SQL Server Pagination using CTE and RowNumber

This is a succinct example of a pagination implementation. The ROW_NUMBER() function is very fast and very useful. The CTE’s (Common Table Expressions) are novel, however where the rubber meets the road temp tables are usually far more efficient. [cc lang=”sql”] SET @rowsPerPage = 10 SET @pageNum = 3 With SQLPaging AS ( Select Top(@rowsPerPage * @pageNum) ResultNum = ROW_NUMBER() OVER (ORDER BY id) ,id FROM dbo.Table1 ) select * FROM SQLPaging where ResultNum > ((@pageNum – 1) * @rowsPerPage) [/cc] Continue reading ...

SQL Server Slow Performance

This post deals with a random hanging that sometimes happens with SQL Server 2005+. In order to troubleshoot SQL Server Slowness, go here. The introduction of the new SQL Server 2005 Query Optimization engine has brought great things (including statement-level caching and smarter execution plan generation). There is however a little more overhead with the advent of this new technology. Aside from taking longer to generate an execution plan, I have noticed two separate instances where a query would appear to intermittently hang. From a database engine perspective however, the query is not hanging but generating an execution plan. The two instances I’ve witnessed this in were in both SQL Server 2005 (sp2), and also now in SQL Server 2008. Both procedures were relatively small, however somewhat complex in their where clauses. The physical indicators in both instances where very high CPU usage and very low IO usage for one particular SPID which we gathered by executing sp_who2. Here is an example of the query that hung on SQL Server 2008: [cc lang=”sql”] SET @IsTrue = ( SELECT COUNT(1) FROM dbo.table1 t1 JOIN dbo.table2 t2 ON t1.ColumnID = t2.ColumnID WHERE ( t2.ID = @ID AND t2.SomeDate < GETDATE() ) OR EXISTS ( SELECT 1 FROM dbo.Table3 WHERE ID = @ID AND AlternateID IN (1,2,3,4,6) ) ) [/cc] Once the query would hang in generating the execution plan, the effect seemed to snowball and cause other executing instances of the same procedure to hang. Also, no blocking was occuring in the […]

Featured Articles

 Site Author

  • Thanks for visiting!