This dedicated post is for SQL 2012 Paging feature. This is a very important feature in terms of performance impact. I have seen pagination playing a crucial load while data loading. This inbuilt paging support would help to fetch only required data.
It would be intersting to see how paging is implemented by the Database Engine. How execution Plan looks like when you run a paging query.
Scenario
- Create a Table
- Populate a million records
- Add Indexes on columns
- Run Paging queries
- Check Execution Plan
Step 1 - Creating Table and Populating Data
Step 2 - SELECT query with Filter Condition using Primary Key Column. As you can see it is a Clustered Index Seek Operation
Step 3 - Implement the Same using Paging Option. Check the Execution Plan
Step 4 - Results of Paging Query
Since We are requesting pagination on a clustered index column, I expected this must be a seek but it seems to be a SCAN.This might be a costly operation. I hope to post answer for it in next set of posts.
More Reads
Happy Learning!!!!
No comments:
Post a Comment