Hi,

we noticed that the parametrized TOP in MS SQL Server is slower than the plain TOP.

The following query is a simplified Version of the Query LLBLGen executed following a Adapter.FetchTypedList call, with max number of rows set to 500:

DECLARE @p2 bigint, @p17 varchar(6)
SET @p2=500
SET @p17='%test%'

SELECT DISTINCT TOP(@p2) TArt_Cod
FROM  TArt 
WHERE TArt_Bez LIKE @p17
 OR  TArt_Cod IN 
        (SELECT  TArt_Cod
              FROM  TEAN
          WHERE  TEAN_Cod LIKE @p17) 
ORDER BY TArt_Cod ASC

On tables of small size (< 10000 rows), the query takes about 40 seconds to execute. On large tables (> 100000 rows), the query takes several minutes.

If we just make the following (semantics preserving) change

TOP(@p2)  -->  TOP(500)

the query runs in a matter of milliseconds for the small table and seconds for the larger one.

This seems to be a known issue with SQL Server, which we reproduced in SQL Server 2005 SP4 and 2008 SP2 (didn't check 2008 R2).

So it would be great if LLBLGen could avoid the parametrized TOP.. what are the chances of seeing such a change in the next release?

Thanks!

set compatibility mode to sqlserver 2000, this will inline the value instead of create a parameter. The downside is that paging queries will use temptables, however in general that's not really a problem. (also sqlserver 2005/8/12 specific features are not supported then, i.e. newsequentialid())