Select From 200 To 250 minimising network traffic 1

[MartinSmithhh]

I have a web search facility that Brings back and displays records matching a keyword.

Depending on the keyword entered This can bring back over a 1000 results.

These are divided into pages of 50 with links to other pages - pretty much like these forums do or any search engine does.

My question is What is the most efficient way to get the results for a particular page?

At the moment If someone clicks on Page 5 say I am doing a SELECT TOP 250 and then doing a RS.Move 200 and looping through the relevant results.

This does of course mean that 200 superfluous results are being sent back to the server.

I know there must be a better way of doing this but unfortunately I don't know what!

I have tried the following (Using the Northwind Customers table as an example if I want records 15 to 20 returned I can use something like this)

SELECT TOP 20 *
INTO #Top20
FROM Customers ORDER BY CustomerID;
SELECT TOP 5 * INTO #RangeTable FROM #Top20 ORDER BY CustomerID DESC;
SELECT * FROM #RangeTable ORDER BY CustomerID
DROP table #Top20
DROP table #RangeTable

I would really appreciate the advice of someone who knows about SQL as to whether this is actually a better way of doing it or not though. ie. will this actually cut down network traffic or will the results of the first query be sent back to the client anyway?

 

[AWithers]

you should be able to do this:

SELECT TOP 50 companyname
FROM Customers
where customerid not in (select top 200 customerid from customers order by customerid)
ORDER BY CustomerID

(unfortunately on Northwind this produces no results but hopefully you get the idea)

Andy

 

[MartinSmithhh]

Excellent idea, thanks very much!