Databases
StackOverflow2013
Postsdbo
POAlternatives for generating sql server query from lambda expression
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POAlternatives for generating sql server query from lambda expression
    primarykey
    data
    text
    <p>I've written a lambda expression that produces the expected results however it generates an absolutely massive sql query and it has bad performance. See very bottom for io/time statistics.</p> <p>Is there another way to achieve the below query?</p> <pre><code>select distinct(searchterms) as SearchTerms, max(totalresults) FROM cmsSearchLog where totalresults != 0 and searchterms like 'de%' group by searchterms order by max(totalresults) desc </code></pre> <p>The c# code snippets:</p> <pre><code>// current lamda expression; has bad performance compared to above query List&lt;SearchTerm&gt; existingSearchTerms1 = context.cmsSearchLogs.Where(oq =&gt; context.cmsSearchLogs.Where(q =&gt; q.SearchTerms.ToLower().Contains(terms.ToLower()) &amp;&amp; q.TotalResults != 0) .Select(s =&gt; s.SearchTerms) .Distinct() .Contains(oq.SearchTerms)) .Select(a =&gt; new { a.SearchTerms, a.TotalResults }) .GroupBy(gb =&gt; gb.SearchTerms) .OrderByDescending(ob =&gt; ob.Max(m =&gt; m.TotalResults)) .Select(s =&gt; new SearchTerm() { SearchTerms = s.FirstOrDefault().SearchTerms, TotalResults = s.FirstOrDefault().TotalResults } ) .ToList(); // get the suggestions back as a list of strings List&lt;string&gt; suggestions = Enumerable.Range(0, existingSearchTerms1.Count()) .Select(x =&gt; existingSearchTerms1.ElementAt(x).SearchTerms).ToList(); </code></pre> <p>This is the private class to hold the results from the query</p> <pre><code>private class SearchTerm { public string SearchTerms { get; set; } public int TotalResults { get; set; } } </code></pre> <p>The sql generated by the lambda expression is huge:</p> <pre><code>SELECT [Project13].[C2] AS [C1], [Project13].[C3] AS [C2], [Project13].[C4] AS [C3] FROM ( SELECT [Project12].[C1] AS [C1], 1 AS [C2], [Project12].[C2] AS [C3], [Project12].[C3] AS [C4] FROM ( SELECT [Project8].[C1] AS [C1], [Project8].[C2] AS [C2], (SELECT TOP (1) [Extent5].[TotalResults] AS [TotalResults] FROM [dbo].[cmsSearchLog] AS [Extent5] WHERE ( EXISTS (SELECT 1 AS [C1] FROM ( SELECT DISTINCT [Extent6].[SearchTerms] AS [SearchTerms] FROM [dbo].[cmsSearchLog] AS [Extent6] WHERE (( CAST(CHARINDEX(LOWER('dew'), LOWER([Extent6].[SearchTerms])) AS int)) &gt; 0) AND (0 &lt;&gt; [Extent6].[TotalResults]) ) AS [Distinct3] WHERE [Distinct3].[SearchTerms] = [Extent5].[SearchTerms] )) AND ([Project8].[SearchTerms] = [Extent5].[SearchTerms])) AS [C3] FROM ( SELECT [Project7].[C1] AS [C1], [Project7].[SearchTerms] AS [SearchTerms], [Project7].[C2] AS [C2] FROM ( SELECT [Project3].[C1] AS [C1], [Project3].[SearchTerms] AS [SearchTerms], (SELECT TOP (1) [Extent3].[SearchTerms] AS [SearchTerms] FROM [dbo].[cmsSearchLog] AS [Extent3] WHERE ( EXISTS (SELECT 1 AS [C1] FROM ( SELECT DISTINCT [Extent4].[SearchTerms] AS [SearchTerms] FROM [dbo].[cmsSearchLog] AS [Extent4] WHERE (( CAST(CHARINDEX(LOWER('dew'), LOWER([Extent4].[SearchTerms])) AS int)) &gt; 0) AND (0 &lt;&gt; [Extent4].[TotalResults])) AS [Distinct2] WHERE [Distinct2].[SearchTerms] = [Extent3].[SearchTerms] )) AND ([Project3].[SearchTerms] = [Extent3].[SearchTerms])) AS [C2] FROM ( SELECT [GroupBy1].[A1] AS [C1], [GroupBy1].[K1] AS [SearchTerms] FROM ( SELECT [Extent1].[SearchTerms] AS [K1], MAX([Extent1].[TotalResults]) AS [A1] FROM [dbo].[cmsSearchLog] AS [Extent1] WHERE EXISTS (SELECT 1 AS [C1] FROM ( SELECT DISTINCT [Extent2].[SearchTerms] AS [SearchTerms] FROM [dbo].[cmsSearchLog] AS [Extent2] WHERE (( CAST(CHARINDEX(LOWER('dew'), LOWER([Extent2].[SearchTerms])) AS int)) &gt; 0) AND (0 &lt;&gt; [Extent2].[TotalResults])) AS [Distinct1] WHERE [Distinct1].[SearchTerms] = [Extent1].[SearchTerms]) GROUP BY [Extent1].[SearchTerms]) AS [GroupBy1] ) AS [Project3] ) AS [Project7] ) AS [Project8] ) AS [Project12] ) AS [Project13] ORDER BY [Project13].[C1] ASC </code></pre> <p>I executed both queries with io and time statistics turned on and the results are below. (note: the lambda generated query is first, my hand written query second) So this confirms my suspicion that the generated query is performing horribly compared to the query I actually want.</p> <pre><code>(8 row(s) affected) Table 'cmsSearchLog'. Scan count 6, logical reads 106, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms. (7 row(s) affected) Table 'cmsSearchLog'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USTheresa
    1. USTugboatCaptain
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POAlternatives for generating sql server query from lambda expression
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POAlternatives for generating sql server query from lambda expression
      1. This table or related slice is empty.
      1. VTUpMod
    1. CONo one has ever claimed that linq to sql would generate perfect sql every time. This looks like a great place to use a hand optimized stored procedure instead of a lambda generated call.
      singulars
      1. POAlternatives for generating sql server query from lambda expression
      1. USasawyer
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload