SQL limit query

Question

I'm having an issue with limiting the SQL query. I'm using SQL 2000 so I can't use any of the functions like ROW_NUMBER(),CTE OR OFFSET_ROW FETCH.

I have tried the Select TOP limit * FROM approach and excluded the already shown results but this way the query is so slow because sometimes my result query fetches more than 10000 records.

Also I have tried the following approach:

SELECT * FROM (
 SELECT DISTINCT TOP 100 PERCENT i.name, i.location, i.image ,  
 ( SELECT count(DISTINCT i.id) FROM image WHERE i.id<= im.id ) AS recordnum 
 FROM images AS im 
 order by im.location asc, im.name asc) as tmp 
 WHERE recordnum between 5 AND 15

same problem here plus issue because I couldn't add ORDER option in sub query from record um. I have placed both solution in stored procedure but still the query execution is still so slow.

So my question is: IS there an efficient way to limit the query to pull 20 records per page in SQL 2000 for large amounts of data i.e more than 10000?

Thanks.

10000 records should not be a significant amount (certainly not "large"), check your indexes. If paging is the issue also consider using a cursor. — Matt Whipple, Jan 25 '13 at 13:31
image table has more than 13 000 000 records :) with adding filters sometimes I pull about 40 000 records — sirenka, Jan 25 '13 at 13:39
possible duplicate of [Efficient Paging (Limit) Query in SQLServer 2000?](http://stackoverflow.com/questions/503566/efficient-paging-limit-query-in-sqlserver-2000) — Alex K., Jan 25 '13 at 13:39
I don't understand why you are selecting the top 100 Percent? What is this achieving? One way to get an interval of data is to select the Top 15 in a subquery, then reverse the order in the outer query and select the top 10, this will give you records 5-15 of your original order by. — GarethD, Jan 25 '13 at 13:40
ignore that. I wanted to add ORDER BY in my query for recordnum which requires top, thats why.. — sirenka, Jan 25 '13 at 14:43

score 0 · Answer 1 · answered Jan 25 '13 at 13:34

Query-wise, there is no great performing way. If performance is critical and the data will always be grouped/ordered the same, you could add a int column and set the value by trigger based on the grouping/ordering. Index it and it should be extremely fast for reads; writes will be a bit slower.

Also, make sure you have indexes on the Id columns on image and images.

score 0 · Accepted Answer · answered Jan 25 '13 at 15:27

0

Now the subquery is only run once
where im2.id is null will skip the first 40 rows

SELECT top 25 im1.* 
FROM images im1 
left join ( select top 40 id from images order by id ) im2
on im1.id = im2.id
where im2.id is null
order by im1.id

answered Jan 25 '13 at 15:27

paparazzo

44,497
23
105
176

SQL limit query

2 Answers2