How to fetch only those records you want?!?!

[Jorge Ribeiro]

Hello

I've a web function in a web service that, based on
received parameters, builds a sql query to ask DB for
some records.
The query can fetch more than 20000 records from de DB.
Because of that I figured that is best to break that huge
list in some multiples of, say, 20 records and send only
a group of 20 in each call... one parameter of the
function is the page number and based on that page number
I extract the requested block of the query and send it
back to the client that shows it in a table on a web page.

My problem is in this operation: I built a query that
retrieves record number 20 to record number 40, for
instance... but because SQL Server does not have a line
count my query is functioning based on successive "order
by" that slows down incredible when the table to be fetch
grows beyond a certain level.

Can you help me here to find a quicker way to fetch only
those records (from #20 to #40 for instance) from a huge
table without that complex SQL query that takes too
long?!?!
Should I fetch the whole table and erase from my dataset
the records that I don't want to send back?!?! Is that
practible... is that faster than my ealier aproach?!?!
what you recomend in such cases (thinking that you have
found such cases )


Best regards

Jorge Ribeiro

 

[Miha Markic]

Hi Jorge,

There are some ways:
You might use TOP n directive to return only first n rows. You might combine
it with WHERE ItemNumber > y (if you have a itemnumber column).

The other way would be to create a temporary table within sql server, fill
it with records you are interested in and return the table as result - this
technic involves some TransactSql knowledge...

 

[IbrahimMalluf]

Hello Miha

What advantage does the Temp table provide over the TOP n/ Where ID> n


--
Ibrahim Malluf
http://www.malluf.com
==============================================
MCS Data Services Code Generator
http://64.78.34.175/mcsnet/DSCG/Announcement.aspx
==============================================
Pocket PC Return On Investment Calculator
Free Download http://64.78.34.175/mcsnet/kwickKalk1.aspx

 

[Miha Markic]

Hi Ibrahim,

As I see it, it gives you more flexibility to select the right page without
unnecessary network traffic (loop using cursor on server and select those
records you want), specially if you don't have a linear or starting from 1
id field.
If you have proper ID or you access pages sequentially than TOP/WHERE is the
best, of course.