markrendle / Simple.Data

A light-weight, dynamic data access component for C# 4.0
MIT License
1.33k stars 303 forks source link

Performance: Missing sql parameters in sql query pager #388

Open vmati opened 8 years ago

vmati commented 8 years ago

SqlQueryPager builds query like this one:

SELECT Count(*) 
FROM   [dbo].[book]; 

WITH __data 
     AS (SELECT [dbo].[book].[id], 
                [dbo].[book].[writerid], 
                [dbo].[book].[name], 
                [dbo].[book].[year], 
                [dbo].[book].[description], 
                Row_number() 
                  OVER( 
                    ORDER BY [dbo].[book].[name] DESC) AS [_#_] 
         FROM   [dbo].[book]) 
SELECT [id], 
       [writerid], 
       [name], 
       [year], 
       [description] 
FROM   __data 
WHERE  [_#_] BETWEEN 6 AND 7

BETWEEN expression does not take sql parameter, just values. This causes to create new execution plan every time query is called.