Data migration mapping joins

Topics: Customizing Orchard, General
Aug 2, 2011 at 11:33 PM

Is it possible to change the way mappings work, so that it does a join instead of multiple selects?

I have a massive about of content items and tags (some items have 20 tags), lists can timeout during the DB call, the main problem is the individual selects for the tags, i would like to map this as a join instead to limit the round trip DB calls.

SQL for tags

-- statement #1
SELECT tags0_.TagsPartRecord_Id as TagsPart3_1_,
       tags0_.Id                as Id1_,
       tags0_.Id                as Id74_0_,
       tags0_.TagRecord_id      as TagRecord2_74_0_,
       tags0_.TagsPartRecord_id as TagsPart3_74_0_
FROM   Orchard_Tags_ContentTagRecord tags0_
WHERE  tags0_.TagsPartRecord_Id = 180 /* @p0 */

-- statement #2
SELECT tagrecord0_.Id      as Id75_0_,
       tagrecord0_.TagName as TagName75_0_
FROM   Orchard_Tags_TagRecord tagrecord0_
WHERE  tagrecord0_.Id = 334 /* @p0 */

-- statement #3
SELECT tagrecord0_.Id      as Id75_0_,
       tagrecord0_.TagName as TagName75_0_
FROM   Orchard_Tags_TagRecord tagrecord0_
WHERE  tagrecord0_.Id = 311 /* @p0 */

-- statement #4
SELECT tagrecord0_.Id      as Id75_0_,
       tagrecord0_.TagName as TagName75_0_
FROM   Orchard_Tags_TagRecord tagrecord0_
WHERE  tagrecord0_.Id = 236 /* @p0 */

-- statement #5
SELECT tagrecord0_.Id      as Id75_0_,
       tagrecord0_.TagName as TagName75_0_
FROM   Orchard_Tags_TagRecord tagrecord0_
WHERE  tagrecord0_.Id = 316 /* @p0 */

-- statement #6
SELECT tagrecord0_.Id      as Id75_0_,
       tagrecord0_.TagName as TagName75_0_
FROM   Orchard_Tags_TagRecord tagrecord0_
WHERE  tagrecord0_.Id = 321 /* @p0 */

-- statement #7
SELECT tagrecord0_.Id      as Id75_0_,
       tagrecord0_.TagName as TagName75_0_
FROM   Orchard_Tags_TagRecord tagrecord0_
WHERE  tagrecord0_.Id = 309 /* @p0 */

-- statement #8
SELECT tagrecord0_.Id      as Id75_0_,
       tagrecord0_.TagName as TagName75_0_
FROM   Orchard_Tags_TagRecord tagrecord0_
WHERE  tagrecord0_.Id = 192 /* @p0 */

-- statement #9
SELECT tagrecord0_.Id      as Id75_0_,
       tagrecord0_.TagName as TagName75_0_
FROM   Orchard_Tags_TagRecord tagrecord0_
WHERE  tagrecord0_.Id = 362 /* @p0 */

-- statement #10
SELECT tagrecord0_.Id      as Id75_0_,
       tagrecord0_.TagName as TagName75_0_
FROM   Orchard_Tags_TagRecord tagrecord0_
WHERE  tagrecord0_.Id = 363 /* @p0 */

-- statement #11
SELECT tagrecord0_.Id      as Id75_0_,
       tagrecord0_.TagName as TagName75_0_
FROM   Orchard_Tags_TagRecord tagrecord0_
WHERE  tagrecord0_.Id = 361 /* @p0 */

-- statement #12
SELECT tagrecord0_.Id      as Id75_0_,
       tagrecord0_.TagName as TagName75_0_
FROM   Orchard_Tags_TagRecord tagrecord0_
WHERE  tagrecord0_.Id = 262 /* @p0 */

-- statement #13
SELECT tagrecord0_.Id      as Id75_0_,
       tagrecord0_.TagName as TagName75_0_
FROM   Orchard_Tags_TagRecord tagrecord0_
WHERE  tagrecord0_.Id = 360 /* @p0 */

Coordinator
Aug 2, 2011 at 11:37 PM

Yes, that is something we're looking at improving. In the meantime, the DB cache module can help a lot with this issue and drastically reduce the number of requests on most pages.

Aug 11, 2011 at 3:53 PM
Edited Aug 11, 2011 at 3:53 PM

Finally got around to looking into a solution for now.

Not ideal as it requires adding files to Orchard.Framework but it works.

Add the following classes:

 

namespace Orchard.Data.Conventions
{
    public class JoinAttribute : Attribute
    {
    }

    public class ReferenceConvention : IReferenceConvention, IReferenceConventionAcceptance
    {
        public void Apply(IManyToOneInstance instance)
        {
            instance.Fetch.Join();
        }

        public void Accept(IAcceptanceCriteria<IManyToOneInspector> criteria)
        {
            criteria.Expect(x => x.Property != null && x.Property.IsDefined(typeof(JoinAttribute), false));
        }
    }
}

Add the join attribute to ContentTagRecord

 

 

namespace Orchard.Tags.Models
{
    public class ContentTagRecord
    {
        public virtual int Id { get; set; }
        [Join]
        public virtual TagRecord TagRecord { get; set; }
        public virtual TagsPartRecord TagsPartRecord { get; set; }
    }
}

 

New sql output with join

 

SELECT tags0_.TagsPartRecord_Id as TagsPart3_2_,
       tags0_.Id                as Id2_,
       tags0_.Id                as Id43_1_,
       tags0_.TagRecord_id      as TagRecord2_43_1_,
       tags0_.TagsPartRecord_id as TagsPart3_43_1_,
       tagrecord1_.Id           as Id44_0_,
       tagrecord1_.TagName      as TagName44_0_
FROM   Orchard_Tags_ContentTagRecord tags0_
       left outer join Orchard_Tags_TagRecord tagrecord1_
         on tags0_.TagRecord_id = tagrecord1_.Id
WHERE  tags0_.TagsPartRecord_Id = 15 /* @p0 */
Coordinator
Aug 11, 2011 at 6:34 PM

I would rather call this attribute [Eager] than join, because it actually eager loads the reference instead of lazy loading it.

Coordinator
Aug 11, 2011 at 6:35 PM
This discussion has been copied to a work item. Click here to go to the work item and continue the discussion.
Aug 11, 2011 at 8:46 PM

@sebastienros thanks for the feedback, I agree and appreciate your work on the project. P.S. your Taxonomies module saved me a lot of work on a recent project.

Coordinator
Aug 11, 2011 at 10:31 PM

Glad you liked iut, I am working hard on it right now ... suggestions are welcome.