none
LINQ Query Optimization RRS feed

  • Question

  • I have a query:

                        var selectForecastList = orderContext.OrderForecasts
                                           .Where(c => c.IdentifierType == 0)
                                           .AsEnumerable()
                                           .Where(c => _skuList.Contains(c.Identifier))
                                           .Select(c => new { c.Identifier, c.Category, c.SubCategory, c.Model, c.Created, c.ModelDetail });
    

    That just takes too long to execute.

    The table looks like:

    USE [SalesHistory]
    GO
    /****** Object:  Table [dbo].[OrderForecast]    Script Date: 07/02/2009 09:12:59 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[OrderForecast](
    	[Identifier] [varchar](128) NOT NULL,
    	[Category] [varchar](128) NOT NULL,
    	[SubCategory] [varchar](128) NOT NULL,
    	[IdentifierType] [tinyint] NOT NULL,
    	[Model] [varchar](32) NOT NULL,
    	[Created] [datetime] NOT NULL,
    	[ModelDetail] [xml] NULL,
     CONSTRAINT [PK_OrderForecast] PRIMARY KEY CLUSTERED 
    (
    	[Identifier] ASC,
    	[Category] ASC,
    	[SubCategory] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    SET ANSI_PADDING OFF

    The table has three PK because of the nature of the heirarchy of the data. Technically identifier type = 0 means that all of the PK columns will have non-null values, identifier type = 1 means just the category column will have a non-null value, and identifier type = 2 means that category and sub category columns will have non-null values. Looking at the data in the table I see about 16,772 rows that have an identifier type of 0

    Back to the LINQ query. I am passed in a list of part numbers (SKUs) that is a List<string> and is the variable _skuList above. Any ideas on how I could speed the query up? Like I said the query as it stands takes way too long.

    Thank you.

    Kevin


     

    Thursday, July 2, 2009 2:22 PM

Answers

  • Remove the AsEnumerable(). This causes most of the filtering to take place on the client rather than the server, which will be what's making the query run slowly.

    Joe

    Write LINQ queries interactively - www.linqpad.net
    • Marked as answer by KevinBurton Thursday, July 2, 2009 2:54 PM
    Thursday, July 2, 2009 2:35 PM
    Answerer

All replies

  • Remove the AsEnumerable(). This causes most of the filtering to take place on the client rather than the server, which will be what's making the query run slowly.

    Joe

    Write LINQ queries interactively - www.linqpad.net
    • Marked as answer by KevinBurton Thursday, July 2, 2009 2:54 PM
    Thursday, July 2, 2009 2:35 PM
    Answerer
  • Most likely you're running in to the issue described in this thread:

    http://social.msdn.microsoft.com/Forums/en-US/linqtosql/thread/4276ecd2-31ff-4cd0-82ea-7a22ce25308b

    You can try the workaround(s) described in the thread, and/or use my L2S profiler to confirm that this is the case. ( http://www.huagati.com/l2sprofiler )


    Kristofer - Huagati Systems Co., Ltd. - Cool tools for Linq-to-SQL and Entity Framework: www.huagati.com/dbmltools (VS designer add-in), www.huagati.com/L2SProfiler (query profiler for L2S)
    Thursday, July 2, 2009 2:36 PM
    Answerer
  • But if I simply remove the 'AsEnumerable' the where clause cannot reflect the 'Contains' as there is not way in LINQ to get the list to the server (that I know of).

    Kevin
    Thursday, July 2, 2009 2:38 PM
  • Ah. I didn't spot the AsEnumerable. That, as Joe says, makes the query return every record with IdentifierType=0 to the client adn then apply the Contains. Linq-to-SQL can translate .Contains to SQL without any problems. Are you by any chance using Linq-to-Entities if it doesn't work..?
    Kristofer - Huagati Systems Co., Ltd. - Cool tools for Linq-to-SQL and Entity Framework: www.huagati.com/dbmltools (VS designer add-in), www.huagati.com/L2SProfiler (query profiler for L2S)
    Thursday, July 2, 2009 2:40 PM
    Answerer
  • What error do you get when you remove .AsEnumerable()? I would expect it to work - it translates to the following SQL:

       WHERE cust.Identifier IN (.., .., .., ..)



    Write LINQ queries interactively - www.linqpad.net
    Thursday, July 2, 2009 2:44 PM
    Answerer
  • Are you saying I can do:

                    var selectForecastList = orderContext.OrderForecasts
                                           .Where(c => c.IdentifierType == 0 &&
                                                               _skuList.Contains(c.Identifier))
                                            .Select(c => new { c.Identifier, c.Category, c.SubCategory, c.Model, c.Created, c.ModelDetail });
    
    

    This will do the same thing but faster?

    Thank you.

    Kevin


    Thursday, July 2, 2009 2:46 PM