StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHow to optimize query
text
Body
copied!<p>I am using Linq2Sql as an ORM and experiencing following problem: I have 2 tables, lets call them client (id, name) and client_action (id, clientid, date). 'client' table contains clients, and client_action contains log of user's actions and i need just first of them.</p> <p>There was a linq2sql query (with paging and sorting), that was resulting in the following sql:</p> <pre><code>SELECT TOP (10) /* columns here */ FROM ( SELECT /* columns here */ FROM [dbo].[client] AS [t0] CROSS APPLY (( SELECT NULL AS [EMPTY] ) AS [t8] OUTER APPLY ( SELECT [t9].[source] FROM [action] AS [t9] INNER JOIN ( SELECT MIN([t10].[actionID]) AS [value], [t10].[ClientID] FROM [client_action] AS [t10] GROUP BY [t10].[clientID] ) AS [t11] ON [t9].[ID] = [t11].[value] WHERE [t0].[ID] = [t11].[ClientID] ) AS [t12]) ) AS [t17] WHERE /* filter for fields from the client table */ ORDER BY [t17].[date_created] DESC </code></pre> <p>There was a logical error in that query and i changed it. Now the sql generated looks like this:</p> <pre><code>SELECT TOP (10) /* columns here */ FROM ( SELECT /* columns here */ FROM ( SELECT /* columns here */ FROM [dbo].[client] AS [t0] CROSS APPLY (( SELECT NULL AS [EMPTY] ) AS [t3] OUTER APPLY ( SELECT [t7].[value] FROM ( SELECT [t5].[ClientID], ( SELECT TOP (1) [t6].[ID] FROM [client_action] AS [t6] WHERE [t5].[clientID] = [t6].[clientID] ORDER BY [t6].[date_created] ) AS [value] FROM ( SELECT [t4].[clientID] FROM [client_action] AS [t4] GROUP BY [t4].[clientID] ) AS [t5] ) AS [t7] WHERE [t0].[ID] = [t7].[clientID] ) AS [t8]) LEFT OUTER JOIN [client_action] AS [t9] ON [t8].[value] = [t9].[ID] ) AS [t26] ) AS [t27] WHERE /* filter for fields from client table */ ORDER BY [t27].[date_created] DESC </code></pre> <p>And the performance problem appears. I executed both queries with 'SET STATISTICS IO ON' and got interesting results:</p> <ul> <li>for the first query: <ul> <li>client table is filtered</li> <li>'top 10' applied</li> <li>'cross apply' applied for the resulting 10 records</li> </ul></li> <li>for the second query: <ul> <li>client table is filtered (about 10K records found)</li> <li>'cross apply' applied for that 10K records</li> <li>'top 10' applied</li> </ul></li> </ul> <p>So it makes second query works much slower. Is there a way to 'tweak' the query so that 'cross apply' will be applied after filtering whenever possible?</p> <p>The problem is that there are 2 'cross apply's and couple more joins there and it all makes it work really slow. I created indices that could improve the speed of the query but still its slow enough to be annoying.</p> <p>help <strong>help</strong> <em>help</em>.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

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