12/17/2015 - update
it's been while since asked question , haven't gotten answer thought post solution team ended using.
we started using dynamic sql option smart sorting. while functional felt dirty. came following implementation:
--params declare @pagesize int , @pageindex int , @sortby varchar(30) , @sortdirection varchar(4); set @pageindex = 1; set @pagesize = 10; set @sortby = 'policycode'; set @sortdirection = 'asc'; --vars declare @start int, @end int; --page 1-n set @pageindex = 1; set @pagesize = 10; set @start = (@pagesize * @pageindex) - (@pagesize - 1); set @end = (@pagesize * @pageindex); ;with policycte ( select p.policyid, p.policytypeid, p.policycode, p.policydesc, p.effectivedate, p.expirationdate policy p inner join policyorg po on p.policyid = po.policyid ) , pagedresultscte ( select [policyid] , [policytypeid] , [policycode] , [policydesc] , [effectivedate] , [expirationdate] , case when @sortby = 'policytypeid' , @sortdirection = 'asc' row_number() on (order [policytypeid] asc) when @sortby = 'policytypeid' , @sortdirection = 'desc' row_number() on (order [policytypeid] desc) when @sortby = 'policycode' , @sortdirection = 'asc' row_number() on (order [policycode]) when @sortby = 'policycode' , @sortdirection = 'desc' row_number() on (order [policycode] desc) when @sortby = 'policydesc' , @sortdirection = 'asc' row_number() on (order [policydesc]) when @sortby = 'policydesc' , @sortdirection = 'desc' row_number() on (order [policydesc] desc) when @sortby = 'effectivedate' , @sortdirection = 'asc' row_number() on (order [effectivedate]) when @sortby = 'effectivedate' , @sortdirection = 'desc' row_number() on (order [effectivedate] desc) when @sortby = 'expirationdate' , @sortdirection = 'asc' row_number() on (order [expirationdate]) when @sortby = 'expirationdate' , @sortdirection = 'desc' row_number() on (order [expirationdate] desc) end rownumber policycte ) select policyid, policytypeid, policycode, policydesc, effectivedate, expirationdate pagedresultscte rownumber between @start , @end order rownumber go by combining case statements , cte, able sort passed in sorting criteria , correct rownumber() value. then, in our final select statement, can order rownumber column.
i'll avoid marking question answered in case wants chime in on solution. feedback welcome.
original post
maybe little more dba knowledge can shed light on me. basic premise: paginated data improve page load performance (get records 1-10, 11-20, etc.) using optimized query within stored procedure. tables have thousands of records.
i'm using sql server 2008 r2 , came following t-sql use in stored procedure:
--params declare @pagesize int, @pageindex int; set @pageindex = 1; set @pagesize = 10; --vars declare @start int, @end int --page 1-n set @pageindex = 1; set @pagesize = 10; set @start = (@pagesize * @pageindex) - (@pagesize - 1); set @end = (@pagesize * @pageindex) ;with policycte ( select row_number() on (order p.policycode) rownumber, p.policyid, p.policytypeid, p.policycode, p.policydesc, p.effectivedate, p.expirationdate policy p inner join policyorg po on p.policyid = po.policyid ) select policyid, policytypeid, policycode, policydesc, effectivedate, expirationdate policycte rownumber between @start , @end order policycode go in search find best practices on how this, ran old msdn patterns , practices article: how to: page records in .net applications (applies sql 2000 , states article out of date). referencing user-specific records section, equivalent t-sql this:
declare @pageindex int, @pagesize int, @sortby nvarchar(30) set @pageindex = 0; set @pagesize = 10; set @sortby = 'policycode'; declare @rowstoretrieve int, @sortdirflipped nvarchar(4); if @pageindex < 1 set @pageindex = 1; if @pagesize < 1 set @pagesize = 10; set @rowstoretrieve = @pageindex * @pagesize declare @sqlstring nvarchar(1000); set @sqlstring = n' select policyid, policytypeid, policycode, policydesc, effectivedate, expirationdate ( select top ' + cast(@pagesize varchar(10)) + ' policyid, policytypeid, policycode, policydesc, effectivedate, expirationdate ( select top ' + cast(@rowstoretrieve varchar(10)) + ' policyid, policytypeid, policycode, policydesc, effectivedate, expirationdate ( select top ' + cast(@rowstoretrieve varchar(10)) + ' p.policyid, p.policytypeid, p.policycode, p.policydesc, p.effectivedate, p.expirationdate policy p inner join policyorg po on p.policyid = po.policyid order p.' + @sortby + ' ) t2 order ' + @sortby + ' desc ) t3 ) t4 order ' + @sortby + ' asc'; exec(@sqlstring) go i've run both of these execution plan , client statistics on , seems me (definitely not expert in analyzing results) msdn version performs better. part of reason, can gather, msdn version starts out smaller record set. however, dislike fact msdn version uses dynamic query. understanding more vulnerable sql injection attacks.
most of examples found prefer form of first sample. can explain me , site reasons why, besides ones listed, first form better second?
Comments
Post a Comment