Optimizing SQL query

Question

I have been running a sql query for large number of rows in a table. The structure of query is similar to the following query.

SELECT id FROM
(
SELECT
     id,(ROW_NUMBER() OVER (ORDER BY id)) AS Row 
FROM 
     <table>
)
temp_table WHERE Row BETWEEN 1 AND 50

I am using paging at the UI so that I need fifty records in a single fetch. My concern is that, I am fetching all the records and then select 50 from them isnt there a way that allows me to select only those records that I truly need. Please note that this a simplified query the original query is very complex and have lot of joins. Is there any standard way of implementing queries that support paging?

AnandPhadke · Accepted Answer · 2012-09-07 06:11:00Z

1

What I suggest here is:

1.Create a indexed view(view_sample) with the query

SELECT
     id,(ROW_NUMBER() OVER (ORDER BY id)) AS Row 
FROM 
     <table>

2.Now create an index (clustered) on row

3.Select id from view_sample where Row BETWEEN 1 AND 50

answered Sep 7, 2012 at 6:11

AnandPhadke

13.5k5 gold badges28 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Rajat Saini Over a year ago

I am generating query dynamically at run time so the order by column can be changed any time. The whole query is dynamic in nature so creating indexes is not easy at all.

AnandPhadke Over a year ago

which query are you generating dynamically?

AnandPhadke Over a year ago

Here I am asking you to create the index on column row which you are populating by ROW_NUMBER and I guess this column will always be there

Rajat Saini Over a year ago

No this column wont always be there. Actually there are 6-7 columns that I need to fetch and this index column could be any one of them. Further these columns could be resultant of joins applied on different tables. This query is just the format to make query understandable. I was looking for a solution that could help me save time wasted on fetching those columns which I dont need. I really appreciate your help but indexes are not the option for me this time.

AnandPhadke Over a year ago

you mean you cant use indexes? and can you pls post your actual query bcos your sample query is not good enough to get your actual problem

Collectives™ on Stack Overflow

Optimizing SQL query

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related