Parametrized Linq to SQL query causes performance issues

smarty

I have a linq query that takes 11 minutes to execute against MSSQL server 2008. I used MSSQL Profiler to find the query taking so long to execute, and I ran it alone towards my database.

I also removed all parameters and added the values directly and ran the query. It took less then 1 second to execute!

I have googled and found that using parameters can really impact the performance because the plan is compiled before the value of the where clause is known.

Since Linq to SQL always run parametrized SQL, what can I do to improve performance in this case?

I haven't found anything I can improve on columns regarding indexes. The first table in the Inner Join statement has 192 014 rows, and the SQL without parameters takes less than a second to execute. Screenshots of execution plans attached.

Edits are below the screenshots.

This is the Linq query:

var criteria = CreateBaseCriteria();

var wordsGroup = from word in QueryExecutor.GetSearchWords()
                 join searchEntry in QueryExecutor.GetReportData(criteria) on (word.SearchID + 100000000) equals searchEntry.EventId
                  group searchEntry by word.SearchWord into wg
                  select new SearchAggregate
                  {
                      Value = wg.Key,
                      FirstTime = wg.Min(l => l.EventTime),
                      LastTime = wg.Max(l => l.EventTime),
                      AverageHits = wg.Average(l => l.NumberOfHits.HasValue ? l.NumberOfHits.Value : 0),
                      Count = wg.Count()
                  };

return wordsGroup.OrderByDescending(w => w.Count).Take(maxRows);

Execution plan of query with parameters (linq to sql) Execution plan of query with known values

Edit: The screenshots did go a little small in here. There are only 5 parameters in the parametrized SQL.

Edit 2: It is the Inner Join statement with parameter @p0 which causes the execution plan to change. When I only removed @p0 variable with the value itself, it runs in less then a second. If this value is constant in all cases (I have to investigate that) can I do anything so that this value doesn't get used like a parameter?

smarty

I found a way to go around this statement, which is causing the execution time to just grow bigtime:

on (word.SearchID + 100000000) equals searchEntry.EventId

What I did was to add a computed column [SearchIdUnique] AS ([SearchID]+(100000000)). Then I can change my Linq query to this:

on word.SearchIdUnique equals searchEntry.EventId

The query execution is down to less than a second, and issue solved.

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related