Hi, I'm using:

SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll version 2.6.9.511 SD.LLBLGen.Pro.LinqSupportClasses.NET35.dll version 2.6.9.512

When I have 10 or 11 left joins every thing is ok, but with more left joins the linq query execution time goes dramatically high. I tested with 22 inner joins and there was no problem, so I guess it accures only for left joins.

The sample below is for reproducing this issue with northwind database, the category table has 15 left joins with itself, yes I know it's not logical , just for testing. I've tested this with some real world code(joining different tables) and the result is the same.

DataAccessAdapter adapter = new DataAccessAdapter(@"data source=.\sql2005 initial catalog=Northwind2000;Trusted_Connection=yes;");
            LinqMetaData metaData = new LinqMetaData();
            metaData.AdapterToUse = adapter;

            var queryEntity = metaData.Categories;

            var q = from _e1 in queryEntity
                    join _e2 in queryEntity on _e1.CategoryId equals _e2.CategoryId
                    into _e1_e2
                    from _e2 in _e1_e2.DefaultIfEmpty()

                    join _e3 in queryEntity on _e2.CategoryId equals _e3.CategoryId
                    into _e2_e3
                    from _e3 in _e2_e3.DefaultIfEmpty()

                    join _e4 in queryEntity on _e3.CategoryId equals _e4.CategoryId
                    into _e3_e4
                    from _e4 in _e3_e4.DefaultIfEmpty()

                    join _e5 in queryEntity on _e4.CategoryId equals _e5.CategoryId
                    into _e4_e5
                    from _e5 in _e4_e5.DefaultIfEmpty()

                    join _e6 in queryEntity on _e5.CategoryId equals _e6.CategoryId
                    into _e5_e6
                    from _e6 in _e5_e6.DefaultIfEmpty()

                    join _e7 in queryEntity on _e6.CategoryId equals _e7.CategoryId
                    into _e6_e7
                    from _e7 in _e6_e7.DefaultIfEmpty()

                    join _e8 in queryEntity on _e7.CategoryId equals _e8.CategoryId
                    into _e7_e8
                    from _e8 in _e7_e8.DefaultIfEmpty()

                    join _e9 in queryEntity on _e8.CategoryId equals _e9.CategoryId
                    into _e8_e9
                    from _e9 in _e8_e9.DefaultIfEmpty()

                    join _e10 in queryEntity on _e9.CategoryId equals _e10.CategoryId
                    into _e9_e10
                    from _e10 in _e9_e10.DefaultIfEmpty()

                    join _e11 in queryEntity on _e10.CategoryId equals _e11.CategoryId
                    into _e10_e11
                    from _e11 in _e10_e11.DefaultIfEmpty()

                    join _e12 in queryEntity on _e11.CategoryId equals _e12.CategoryId
                    into _e11_e12
                    from _e12 in _e11_e12.DefaultIfEmpty()

                    join _e13 in queryEntity on _e12.CategoryId equals _e13.CategoryId
                    into _e12_e13
                    from _e13 in _e12_e13.DefaultIfEmpty()

                    join _e14 in queryEntity on _e13.CategoryId equals _e14.CategoryId
                    into _e13_e14
                    from _e14 in _e13_e14.DefaultIfEmpty()

                    join _e15 in queryEntity on _e14.CategoryId equals _e15.CategoryId
                    into _e14_e15
                    from _e15 in _e14_e15.DefaultIfEmpty()

                    select _e1;

            var list = q.ToList();

What's the reason for this issue? Is it a bug or is there any workaround for it?

Thank you in advance,

Arash.

What gets slow: the SQL produced executed on the DB, or the evaluation of the linq query to a SQL query? that's essential: if it's the SQL query, then that's a problem of your database used and perhaps it runs out of memory, or your tempdb is too small and it has to scale that up. If it's the linq provider, we need to profile a couple of things, but I don't think it can get much faster than it is now.

So check this carefully, with proper tools.

With INNER joins in a database, it can optimize further joins with the results of the previous, and in-memory temp sets could be kept small. With left joins, this isn't the case: all joined data is always in the complete set so your in-memory temp set gets very very big if your tables are also big. (keep the lists of joins as low as possible, as they can make SQL queries slow when sets get bigger. So don't join tables if you dont have to)

DefaultIfEmpty() is also more time consuming as it has to lookup the source it works on, however it does this by fast lookups in dictionaries so with even 100 defaultifempty's it still would be unnoticeable.

Also be sure you have switched off level 4 tracing on the linq provider as it produces a lot of overhead.

Otis wrote:

What gets slow: the SQL produced executed on the DB, or the evaluation of the linq query to a SQL query? that's essential: if it's the SQL query, then that's a problem of your database used and perhaps it runs out of memory, or your tempdb is too small and it has to scale that up. If it's the linq provider, we need to profile a couple of things, but I don't think it can get much faster than it is now.

So check this carefully, with proper tools.

With INNER joins in a database, it can optimize further joins with the results of the previous, and in-memory temp sets could be kept small. With left joins, this isn't the case: all joined data is always in the complete set so your in-memory temp set gets very very big if your tables are also big. (keep the lists of joins as low as possible, as they can make SQL queries slow when sets get bigger. So don't join tables if you dont have to)

DefaultIfEmpty() is also more time consuming as it has to lookup the source it works on, however it does this by fast lookups in dictionaries so with even 100 defaultifempty's it still would be unnoticeable.

Also be sure you have switched off level 4 tracing on the linq provider as it produces a lot of overhead.

The evaluation of the linq query to a SQL query is slow. There is no problem on the DB side. The generated SQL statement executes so quickly. I've tested it on both SQL Server and Oracle.

Would you please test the code I provided in my last post? If I run the query using only the 11 left joins it will execute in just 1 second but if I run it with 15 left joins it will take about 100 seconds on my PC to be executed. Also I should mention that I've not switched on any tracing switch.

Thanks for your support. With best regards, Arash.

arash wrote:

Otis wrote:

What gets slow: the SQL produced executed on the DB, or the evaluation of the linq query to a SQL query? that's essential: if it's the SQL query, then that's a problem of your database used and perhaps it runs out of memory, or your tempdb is too small and it has to scale that up. If it's the linq provider, we need to profile a couple of things, but I don't think it can get much faster than it is now.

So check this carefully, with proper tools.

With INNER joins in a database, it can optimize further joins with the results of the previous, and in-memory temp sets could be kept small. With left joins, this isn't the case: all joined data is always in the complete set so your in-memory temp set gets very very big if your tables are also big. (keep the lists of joins as low as possible, as they can make SQL queries slow when sets get bigger. So don't join tables if you dont have to)

DefaultIfEmpty() is also more time consuming as it has to lookup the source it works on, however it does this by fast lookups in dictionaries so with even 100 defaultifempty's it still would be unnoticeable.

Also be sure you have switched off level 4 tracing on the linq provider as it produces a lot of overhead.

The evaluation of the linq query to a SQL query is slow. There is no problem on the DB side. The generated SQL statement executes so quickly. I've tested it on both SQL Server and Oracle.

Would you please test the code I provided in my last post? If I run the query using only the 11 left joins it will execute in just 1 second but if I run it with 15 left joins it will take about 100 seconds on my PC to be executed. Also I should mention that I've not switched on any tracing switch.

100 seconds with just 4 joins more is a little odd indeed.

When I run your query, I indeed see a slowness appear in the linq provider (as no query is being generated for 10 seconds or more (I broke it off after that)).

The expression tree looks like:

value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[NW26.Adapter.EntityClasses. CategoryEntity]).GroupJoin(value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[ NW26.Adapter.EntityClasses.CategoryEntity]), _e1 => _e1.CategoryId, _e2 => _e2.CategoryId, (_e1, _e1_e2) => new <>f__AnonymousType5a`2(_e1 = _e1, _e1_e2 = _e1_e2)).SelectMany(<>h__TransparentIdentifier93 => <>h__TransparentIdentifier93._e1_e2.DefaultIfEmpty(), (<>h__TransparentIdentifier93, _e2) => new <>f__AnonymousType5b`2(<>h__TransparentIdentifier93 = <>h__TransparentIdentifier93, _e2 = _e2)).GroupJoin(value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[ NW26.Adapter.EntityClasses.CategoryEntity]), <>h__TransparentIdentifier94 => <>h__TransparentIdentifier94._e2.CategoryId, _e3 => _e3.CategoryId, (<>h__TransparentIdentifier94, _e2_e3) => new <>f__AnonymousType5c`2(<>h__TransparentIdentifier94 = <>h__TransparentIdentifier94, _e2_e3 = _e2_e3)).SelectMany(<>h__TransparentIdentifier95 => <>h__TransparentIdentifier95._e2_e3.DefaultIfEmpty(), (<>h__TransparentIdentifier95, _e3) => new <>f__AnonymousType5d`2(<>h__TransparentIdentifier95 = <>h__TransparentIdentifier95, _e3 = _e3)).GroupJoin(value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[ NW26.Adapter.EntityClasses.CategoryEntity]), <>h__TransparentIdentifier96 => <>h__TransparentIdentifier96._e3.CategoryId, _e4 => _e4.CategoryId, (<>h__TransparentIdentifier96, _e3_e4) => new <>f__AnonymousType5e`2(<>h__TransparentIdentifier96 = <>h__TransparentIdentifier96, _e3_e4 = _e3_e4)).SelectMany(<>h__TransparentIdentifier97 => <>h__TransparentIdentifier97._e3_e4.DefaultIfEmpty(), (<>h__TransparentIdentifier97, _e4) => new <>f__AnonymousType5f`2(<>h__TransparentIdentifier97 = <>h__TransparentIdentifier97, _e4 = _e4)).GroupJoin(value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[ NW26.Adapter.EntityClasses.CategoryEntity]), <>h__TransparentIdentifier98 => <>h__TransparentIdentifier98._e4.CategoryId, _e5 => _e5.CategoryId, (<>h__TransparentIdentifier98, _e4_e5) => new <>f__AnonymousType60`2(<>h__TransparentIdentifier98 = <>h__TransparentIdentifier98, _e4_e5 = _e4_e5)).SelectMany(<>h__TransparentIdentifier99 => <>h__TransparentIdentifier99._e4_e5.DefaultIfEmpty(), (<>h__TransparentIdentifier99, _e5) => new <>f__AnonymousType61`2(<>h__TransparentIdentifier99 = <>h__TransparentIdentifier99, _e5 = _e5)).GroupJoin(value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[ NW26.Adapter.EntityClasses.CategoryEntity]), <>h__TransparentIdentifier9a => <>h__TransparentIdentifier9a._e5.CategoryId, _e6 => _e6.CategoryId, (<>h__TransparentIdentifier9a, _e5_e6) => new <>f__AnonymousType62`2(<>h__TransparentIdentifier9a = <>h__TransparentIdentifier9a, _e5_e6 = _e5_e6)).SelectMany(<>h__TransparentIdentifier9b => <>h__TransparentIdentifier9b._e5_e6.DefaultIfEmpty(), (<>h__TransparentIdentifier9b, _e6) => new <>f__AnonymousType63`2(<>h__TransparentIdentifier9b = <>h__TransparentIdentifier9b, _e6 = _e6)).GroupJoin(value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[ NW26.Adapter.EntityClasses.CategoryEntity]), <>h__TransparentIdentifier9c => <>h__TransparentIdentifier9c._e6.CategoryId, _e7 => _e7.CategoryId, (<>h__TransparentIdentifier9c, _e6_e7) => new <>f__AnonymousType64`2(<>h__TransparentIdentifier9c = <>h__TransparentIdentifier9c, _e6_e7 = _e6_e7)).SelectMany(<>h__TransparentIdentifier9d => <>h__TransparentIdentifier9d._e6_e7.DefaultIfEmpty(), (<>h__TransparentIdentifier9d, _e7) => new <>f__AnonymousType65`2(<>h__TransparentIdentifier9d = <>h__TransparentIdentifier9d, _e7 = _e7)).GroupJoin(value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[ NW26.Adapter.EntityClasses.CategoryEntity]), <>h__TransparentIdentifier9e => <>h__TransparentIdentifier9e._e7.CategoryId, _e8 => _e8.CategoryId, (<>h__TransparentIdentifier9e, _e7_e8) => new <>f__AnonymousType66`2(<>h__TransparentIdentifier9e = <>h__TransparentIdentifier9e, _e7_e8 = _e7_e8)).SelectMany(<>h__TransparentIdentifier9f => <>h__TransparentIdentifier9f._e7_e8.DefaultIfEmpty(), (<>h__TransparentIdentifier9f, _e8) => new <>f__AnonymousType67`2(<>h__TransparentIdentifier9f = <>h__TransparentIdentifier9f, _e8 = _e8)).GroupJoin(value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[ NW26.Adapter.EntityClasses.CategoryEntity]), <>h__TransparentIdentifiera0 => <>h__TransparentIdentifiera0._e8.CategoryId, _e9 => _e9.CategoryId, (<>h__TransparentIdentifiera0, _e8_e9) => new <>f__AnonymousType68`2(<>h__TransparentIdentifiera0 = <>h__TransparentIdentifiera0, _e8_e9 = _e8_e9)).SelectMany(<>h__TransparentIdentifiera1 => <>h__TransparentIdentifiera1._e8_e9.DefaultIfEmpty(), (<>h__TransparentIdentifiera1, _e9) => new <>f__AnonymousType69`2(<>h__TransparentIdentifiera1 = <>h__TransparentIdentifiera1, _e9 = _e9)).GroupJoin(value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[ NW26.Adapter.EntityClasses.CategoryEntity]), <>h__TransparentIdentifiera2 => <>h__TransparentIdentifiera2._e9.CategoryId, _e10 => _e10.CategoryId, (<>h__TransparentIdentifiera2, _e9_e10) => new <>f__AnonymousType6a`2(<>h__TransparentIdentifiera2 = <>h__TransparentIdentifiera2, _e9_e10 = _e9_e10)).SelectMany(<>h__TransparentIdentifiera3 => <>h__TransparentIdentifiera3._e9_e10.DefaultIfEmpty(), (<>h__TransparentIdentifiera3, _e10) => new <>f__AnonymousType6b`2(<>h__TransparentIdentifiera3 = <>h__TransparentIdentifiera3, _e10 = _e10)).GroupJoin(value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[ NW26.Adapter.EntityClasses.CategoryEntity]), <>h__TransparentIdentifiera4 => <>h__TransparentIdentifiera4._e10.CategoryId, _e11 => _e11.CategoryId, (<>h__TransparentIdentifiera4, _e10_e11) => new <>f__AnonymousType6c`2(<>h__TransparentIdentifiera4 = <>h__TransparentIdentifiera4, _e10_e11 = _e10_e11)).SelectMany(<>h__TransparentIdentifiera5 => <>h__TransparentIdentifiera5._e10_e11.DefaultIfEmpty(), (<>h__TransparentIdentifiera5, _e11) => new <>f__AnonymousType6d`2(<>h__TransparentIdentifiera5 = <>h__TransparentIdentifiera5, _e11 = _e11)).GroupJoin(value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[ NW26.Adapter.EntityClasses.CategoryEntity]), <>h__TransparentIdentifiera6 => <>h__TransparentIdentifiera6._e11.CategoryId, _e12 => _e12.CategoryId, (<>h__TransparentIdentifiera6, _e11_e12) => new <>f__AnonymousType6e`2(<>h__TransparentIdentifiera6 = <>h__TransparentIdentifiera6, _e11_e12 = _e11_e12)).SelectMany(<>h__TransparentIdentifiera7 => <>h__TransparentIdentifiera7._e11_e12.DefaultIfEmpty(), (<>h__TransparentIdentifiera7, _e12) => new <>f__AnonymousType6f`2(<>h__TransparentIdentifiera7 = <>h__TransparentIdentifiera7, _e12 = _e12)).GroupJoin(value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[ NW26.Adapter.EntityClasses.CategoryEntity]), <>h__TransparentIdentifiera8 => <>h__TransparentIdentifiera8._e12.CategoryId, _e13 => _e13.CategoryId, (<>h__TransparentIdentifiera8, _e12_e13) => new <>f__AnonymousType70`2(<>h__TransparentIdentifiera8 = <>h__TransparentIdentifiera8, _e12_e13 = _e12_e13)).SelectMany(<>h__TransparentIdentifiera9 => <>h__TransparentIdentifiera9._e12_e13.DefaultIfEmpty(), (<>h__TransparentIdentifiera9, _e13) => new <>f__AnonymousType71`2(<>h__TransparentIdentifiera9 = <>h__TransparentIdentifiera9, _e13 = _e13)).GroupJoin(value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[ NW26.Adapter.EntityClasses.CategoryEntity]), <>h__TransparentIdentifieraa => <>h__TransparentIdentifieraa._e13.CategoryId, _e14 => _e14.CategoryId, (<>h__TransparentIdentifieraa, _e13_e14) => new <>f__AnonymousType72`2(<>h__TransparentIdentifieraa = <>h__TransparentIdentifieraa, _e13_e14 = _e13_e14)).SelectMany(<>h__TransparentIdentifierab => <>h__TransparentIdentifierab._e13_e14.DefaultIfEmpty(), (<>h__TransparentIdentifierab, _e14) => new <>f__AnonymousType73`2(<>h__TransparentIdentifierab = <>h__TransparentIdentifierab, _e14 = _e14)).GroupJoin(value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[ NW26.Adapter.EntityClasses.CategoryEntity]), <>h__TransparentIdentifierac => <>h__TransparentIdentifierac._e14.CategoryId, _e15 => _e15.CategoryId, (<>h__TransparentIdentifierac, _e14_e15) => new <>f__AnonymousType74`2(<>h__TransparentIdentifierac = <>h__TransparentIdentifierac, _e14_e15 = _e14_e15)).SelectMany(<>h__TransparentIdentifierad => <>h__TransparentIdentifierad._e14_e15.DefaultIfEmpty(), (<>h__TransparentIdentifierad, _e15) => <>h__TransparentIdentifierad.<>h__TransparentIdentifierac.<>h__TransparentIdentifierab.<>h__TransparentIdentifieraa.<>h__TransparentIdentifiera9.<>h__TransparentIdentifiera8.<>h__TransparentIdentifiera7.<>h__TransparentIdentifiera6.<>h__TransparentIdentifiera5.<>h__TransparentIdentifiera4.<>h__TransparentIdentifiera3.<>h__TransparentIdentifiera2.<>h__TransparentIdentifiera1.<>h__TransparentIdentifiera0.<>h__TransparentIdentifier9f.<>h__TransparentIdentifier9e.<>h__TransparentIdentifier9d.<>h__TransparentIdentifier9c.<>h__TransparentIdentifier9b.<>h__TransparentIdentifier9a.<>h__TransparentIdentifier99.<>h__TransparentIdentifier98.<>h__TransparentIdentifier97.<>h__TransparentIdentifier96.<>h__TransparentIdentifier95.<>h__TransparentIdentifier94.<>h__TransparentIdentifier93._e1)

So it's a lot to parse. When I use 1-11, it's fast (as in instantaneously), when I add 12, it's slower (1 second or so). When I add 13, it takes 5 seconds, so it's exponentially going upwards. This looks like a sequential lookup problem.

That's not said it is fixable though. DefaultIfEmpty() is one of the most complex elements in the linq provider, so I could perhaps locate the hotspot of the problem with a profiler and look into perhaps optimize that a bit, but that's it.

Doing this much left joins is also not recommended, do you really need that much left-joins? Don't worry, I'll look into this, it's just an edge case IMHO so you should look into using less joins in your query anyway, as the resulting query itself will likely not be that optimal anyway (although it might look like it's 'fast' with small test sets of data)

Doing this much left joins is also not recommended, do you really need that much left-joins?

Unfortunately yes I need, maybe I should use Stored Procedures, but it would be nice if I can handle it in LINQ.

Thanks.

(you need 15 left joins? A little odd, but ok, if you say so )

Hmm... profiling with 11 caused dottrace to allocate more than 2GB or ram, so I've to narrow the thing down per step I guess. I couldn't find anything with 6 joins... looking into it.

(edit) The slowness is in the preprocessor, the first phase of the tree handling. HandleMemberExpression is called 131,000 times, which isn't good. Looking into why this is (as it is handling that bizarre long projection lambda, however that shouldn't take that much calls)

Found it. Can you spot the issue? ->

/// <summary>
/// Handles the access to a member of a type or instance. This version only converts member access to objects into constants.
/// </summary>
/// <param name="expressionToHandle">The expression to handle.</param>
/// <returns>the handled expression</returns>
protected override LinqExpression HandleMemberExpression(MemberExpression expressionToHandle)
{
    TraceScopeStart("PreProcessor::MemberExpression");
    LinqExpression toReturn = null;
    if(expressionToHandle.Expression == null)
    {
        // member of static type. This member can be read as a constant and wrapped into a constant expression 
        toReturn = LinqUtils.ConvertLocalExpressionToRealObject(expressionToHandle);
    }
    else
    {
        LinqExpression memberContainer = HandleExpression(expressionToHandle.Expression);
        toReturn = memberContainer;
        switch((int)memberContainer.NodeType)
        {
            case (int)ExpressionType.Constant:
                toReturn = HandleMemberIsPartOfConstant(expressionToHandle, memberContainer);
                break;
            case (int)LLBLGenProExpressionType.Entity:
                toReturn = LinqUtils.HandleMemberMappedOnRelationIsPartOfEntity(expressionToHandle, memberContainer, this.TrackedMappings,
                                this.GeneratedCodeElementCreator);
                if(toReturn == null)
                {
                    toReturn = base.HandleMemberExpression(expressionToHandle);
                }
                break;
            default:
                toReturn = base.HandleMemberExpression(expressionToHandle);
                break;
        }

        if(toReturn.NodeType == ExpressionType.Constant)
        {
            // unwrap DataSource*<T> constants. Do this by leveraging the handle constant routine which contains this code
            toReturn = HandleConstantExpression((ConstantExpression)toReturn);
        }
    }
    TraceScopeEnd("PreProcessor::MemberExpression");
    return toReturn;
}


// base.HandleMemberExpression(..)
protected virtual LinqExpression HandleMemberExpression(MemberExpression expressionToHandle)
{
    TraceScopeStart("GenericExpressionHandler::MemberExpression");
    LinqExpression operand = HandleExpression(expressionToHandle.Expression);
    TraceScopeEnd("GenericExpressionHandler::MemberExpression");

    if(operand != expressionToHandle.Expression)
    {
        return LinqExpression.MakeMemberAccess(operand, expressionToHandle.Member);
    }
    return expressionToHandle;
}

Took me a while but OK, it's very subtle. 15 left joins now take under a second. There's one other bug pending, I'll post a link here to the post which will contain a new build with this and that other bug's fix as well.

(edit) please use the dll attached to this post: http://www.llblgen.com/tinyforum/GotoMessage.aspx?MessageID=89603&ThreadID=16035

Thank you very much for your very quick responses and excellent support, you saved me

No problem Glad it's solved.