We've hit a performance problem where a query such as

Select  Top(10)
  [Title],              
        [WR_Type],          
        [WR_Number],            
        [WR_Period],            
        [State],
        [Target_End_Date],  
        [Target_Start_Date],    
        [Actual_End_Date],  
        [Actual_Start_Date],    
        [Estimated_hours],  
        [Owner_Department_Name], 
        [PrioritySort],
        [Related_Occurrences],   
        [Restricted],           
        [OverDue],          
        [ChkOutTo]                      
FROM  [AQD].[qaoc_WR_ViewSecurity] as wr
where wr.Staff_Member_ID = 437 and wr.WR_Type_Sub_Group_ID=10
AND WR.State in ('Assigned','Opened', 'In Progress', 'Findings Issued')
Order by WR.WR_Period Desc, WR.WR_Type DESC, WR.WR_Number DESC

takes 40 seconds to return 7 rows from a 40,000 row View

After reading http://www.mssqltips.com/sqlservertip/2053/trick-to-optimize-top-clause-in-sql-server

http://stackoverflow.com/questions/9616965/why-select-top-clause-could-lead-to-long-time-cost

I changed the top to use a variable e.g

DECLARE @i int

SET @i = 10

SELECT TOP(@i)... 

and now the 7 rows return in a second.

Is there anything I can do in LLBL to make this query faster?

LLBL version 4.1.13.1220

LLBLGen Framework uses SELECT TOP(@param). Do you see something different?

to get TOP (@param), don't set the compatibility mode to sqlserver 2000, the default is 2005+ which will emit top (@param)

Otis wrote:

to get TOP (@param), don't set the compatibility mode to sqlserver 2000, the default is 2005+ which will emit top (@param)

LLBL is emitting TOP (@param) - still slow. Full LLLBL generated query from ORMProfiler:

SELECT TOP(10 /* @p2 */) [LPA_L1].[InvestigationTitle],
                                                                [LPA_L1].[WRType],
                                                                [LPA_L1].[WRNumber],
                                                                [LPA_L1].[WRPeriod],
                                                                [LPA_L1].[State],
                                                                [LPA_L1].[TargetEndDateTime],
                                                                [LPA_L1].[TargetStartDateTime],
                                                                [LPA_L1].[ActualEndDateTime],
                                                                [LPA_L1].[ActualStartDateTime],
                                                                [LPA_L1].[EstimatedHours],
                                                                [LPA_L1].[Owner],
                                                                [LPA_L1].[PrioritySort],
                                                                [LPA_L1].[RelatedOccurrenceIDs],
                                                                [LPA_L1].[IsRestricted],
                                                                [LPA_L1].[Overdue],
                                                                [LPA_L1].[CheckedoutToWorkbenchUser]
FROM   (SELECT DISTINCT [LPLA_1].[Title]                 AS [InvestigationTitle],
                                                                                                [LPLA_1].[WR_Type]             AS [WRType],
                                                                                                [LPLA_1].[WR_Number]             AS [WRNumber],
                                                                                                [LPLA_1].[WR_Period]             AS [WRPeriod],
                                                                                                [LPLA_1].[State],
                                                                                                [LPLA_1].[Target_End_Date]     AS [TargetEndDateTime],
                                                                                                [LPLA_1].[Target_Start_Date]     AS [TargetStartDateTime],
                                                                                                [LPLA_1].[Actual_End_Date]     AS [ActualEndDateTime],
                                                                                                [LPLA_1].[Actual_Start_Date]     AS [ActualStartDateTime],
                                                                                                [LPLA_1].[Estimated_hours]     AS [EstimatedHours],
                                                                                                [LPLA_1].[Owner_Department_Name] AS [Owner],
                                                                                                [LPLA_1].[PrioritySort],
                                                                                                [LPLA_1].[Related_Occurrences]   AS [RelatedOccurrenceIDs],
                                                                                                [LPLA_1].[Restricted]           AS [IsRestricted],
                                                                                                [LPLA_1].[OverDue]             AS [Overdue],
                                                                                                [LPLA_1].[ChkOutTo]           AS [CheckedoutToWorkbenchUser]
                                FROM   [AQD].[qaoc_WR_ViewSecurity] [LPLA_1]
                                WHERE  (((((([LPLA_1].[Staff_Member_ID] = 437 /* @p3 */))
                                                                   AND ([LPLA_1].[WR_Type_Sub_Group_ID] = 10 /* @p4 */))
                                                  AND ([LPLA_1].[State] IN ('Assigned' /* @p5 */, 'Opened' /* @p6 */, 'In Progress' /* @p7 */, 'Findings Issued' /* @p8 */)))))) [LPA_L1]
ORDER  BY [LPA_L1].[WRPeriod] DESC,
                                  [LPA_L1].[WRType] DESC,
                                  [LPA_L1].[WRNumber] DESC

That it is slow, I don't see how that is our fault, to be honest: the framework generates what it is ordered to do so. That sql server makes it a slow query, that's unfortunate, but what is there that we should change? Is the query slow, the consumption of the data, is the execution plan revealing anything?

btw, how did you test it again? In SSMS, the connection / results are cached, reexecuting a query is often very quick, but not a real measurement.

Keep in mind that the view's sql is in-lined in the query when executed, so it might be the view is actually rather slow as it is executed each time (the results aren't cached unless you're using a materialized/indexed view)

Otis wrote:

That it is slow, I don't see how that is our fault, to be honest

I never said it was. Our database guy's research has found that this is a well known bug, or at least a deficiency of SQL Servers query optimizer.

Otis wrote:

That sql server makes it a slow query, that's unfortunate, but what is there that we should change?

Provide some way implementing one of the workarounds, such as emitting a variable for the TOP, as talked about in the links above.

Otis wrote:

Does the execution plan revealing anything?

I haven't looked at it myself, but I believe adding the variable changes the execution plan to do the right thing. Also replacing the columns with * also makes it go fast.

Tested in SSMS by alternating between using and not using the variable. e.g

DECLARE @i int
SET @i = 10
SELECT TOP(@i)... 

then

DECLARE @i int
SET @i = 10
SELECT TOP(10)... 

Which database version (select @@version) are you using?

Walaa wrote:

Which database version (select @@version) are you using?

Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) Dec 28 2012 20:23:12 Copyright (c) Microsoft Corporation Business Intelligence Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)

We support Microsoft SQL Server 2008 as well

More info on the problem here:[http://sqlblog.com/blogs/paul_white/archive/2010/08/27/sorting-row-goals-and-the-top-100-problem.aspx](http://sqlblog.com/blogs/paul_white/archive/2010/08/27/sorting-row-goals-and-the-top-100-problem.aspx)

If I change the Top to 30 or greater it is fast likewise if I change the DESC to ASC or sort on other columns.

So it seems the performance of TOP is unreliable.

TomDog wrote:

Otis wrote:

That it is slow, I don't see how that is our fault, to be honest

I never said it was. Our database guy's research has found that this is a well known bug, or at least a deficiency of SQL Servers query optimizer.

Otis wrote:

That sql server makes it a slow query, that's unfortunate, but what is there that we should change?

Provide some way implementing one of the workarounds, such as emitting a variable for the TOP, as talked about in the links above.

but it does that, from the looks of it? The query you posted has @p2 as top parameter:

SELECT TOP(10 /* @p2 */) [LPA_L1].[InvestigationTitle],

so I'm not sure what else there's to do: the top argument is a parameter already.

or is this related to parameter sniffing?

All sql server sql is executed by sp_executesql, as SqlCommand.Execute* does that (so we can't prevent that, there's no way around that actually). This means that parameters are passed through to the query. It might be, that the parameters declared locally are treated differently than the parameters defined on the command itself. This is causing the 'parameter sniffing' issue with sqlserver: it doesn't do the right thing based on the parameter value. But again, I don't know whether that's the issue you run into. In all cases: the top argument is already a parameter, so that's not it. Please see this (long) thread for more info and code, to see whether this is what's causing your problems:

http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=13271&HighLight=1

Otis wrote:

TomDog wrote:

Provide some way implementing one of the workarounds, such as emitting a variable for the TOP, as talked about in the links above.

but it does that, from the looks of it? The query you posted has @p2 as top parameter:

SELECT TOP(10 /* @p2 */) [LPA_L1].[InvestigationTitle],

so I'm not sure what else there's to do: the top argument is a parameter already.

or is this related to parameter sniffing?

It needs not be a variable not a parameter

e.g

DECLARE @i int

SET @i = @p2

SELECT TOP(@i)... 

It appears to be a hack to force optimizer can allocate more memory so the sort operation doesn't spill over to tempdb

ah ok, missed that. I already wondered why the parameter wasn't enough! In that case, please see the parameter sniffing thread I posted in my edited post earlier. That will give you insights in how to do this (as that thread offers a workaround to declare parameters locally to work around the parameter sniffing issue and that workaround is usable for you as well. )

Otis wrote:

In that case, please see the parameter sniffing thread I posted in my edited post earlier.

Yeah, that's will sort us if we need it, thanks. For now, as the only place we have hit the problem is this one compound key, we have added a sort column to the table for that key. But if we hit the problem with other columns we'll try the more general option of generating the variable from the parameter.