.NET Daily

Featured

Fast SQL Server Stored Procedure with Filtering, Sorting and Paging

Posted on .

Fast SQL Server Stored Procedure with Filtering, Sorting and Paging

Introduction

Even though there are dozens of articles out there which shows you how to do a Microsoft SQL Server stored procedure with filtering, sorting and paging, the execution time greatly differs between each approach.

So I started to do some research in order to find the best approach out there. And I believe I found it on Aaron Bertrand article about OFFSET/FETCH Pagination.

With the help of these OFFSET and FETCH keywords inside a CTE I managed to build a SQL Stored procedure that was at least twice as fast in return times as the other average ones found on the internet.

You can check it in action out below exemplified on a simple Languages table:

Try this approach on your side, especially if you have a different implementation approach. I am super curious to see if it made any difference in terms of performance. On my side for a SQL clustered table with 100+ k rows it got me 2.3x improvement in execution times.

You can download it from below:

Attachments

Darius

Darius

Darius Dumitrescu is a creative Senior CMS Consultant with in depth .NET knowledge, focused on Web Development and Architecture Design.

There are no comments.

View Comments (0) ...
Navigation