How to deal with big query result?

[Lin Ma]

Greetings,

In my search application, user can type a number to search. I use LIKE in my
query.

If a query result generates over 10,000 recordsets, it may several minutes
to run.

Is there a way to only query certain recordset at a time?

I found in SQL you can use "TOP". Like select TOP 100.... it will return
only top 100 records.

Is there a way to query may be from 101 to 200 then from 201 to 300...

My query is like: Select * from Product where PartNumber like '%" PartNumber
"%'

Any idea is welcome.

Thanks a lot.

Lin Ma

[Aaron Bertrand - MVP]

Yes Lin, this is called "paging"

http://www.aspfaq.com/2120
http://www.aspfaq.com/2352

The use of a temp table to divide all of the records tends to scare people.
Typically, much of the processing time is in transferring the data over the
network and rendering the HTML required to display the data, than the
initial filtering. So give that method a shot.

Also, if a query returns 10,000 rows, you might consider requiring that the
user narrow it down a bit better... e.g. picking categories, or only
choosing results that start with one letter at a time, or ...


"Lin Ma" <[e-mail]> wrote in message
news:#S*************@TK2MSFTNGP10.phx.gbl...

Greetings,

In my search application, user can type a number to search. I use LIKE in my query.

If a query result generates over 10,000 recordsets, it may several minutes
to run.

Is there a way to only query certain recordset at a time?

I found in SQL you can use "TOP". Like select TOP 100.... it will return
only top 100 records.

Is there a way to query may be from 101 to 200 then from 201 to 300...

My query is like: Select * from Product where PartNumber like '%" PartNumber "%'

Any idea is welcome.

Thanks a lot.

Lin Ma

[Ray at]

http://www.aspfaq.com/2120
http://www.aspfaq.com/2096

Ray at work

"Lin Ma" <[e-mail]> wrote in message
news:%2***************@TK2MSFTNGP10.phx.gbl...

Greetings,

In my search application, user can type a number to search. I use LIKE in my query.

If a query result generates over 10,000 recordsets, it may several minutes
to run.

Is there a way to only query certain recordset at a time?

I found in SQL you can use "TOP". Like select TOP 100.... it will return
only top 100 records.

Is there a way to query may be from 101 to 200 then from 201 to 300...

My query is like: Select * from Product where PartNumber like '%" PartNumber "%'

Any idea is welcome.

Thanks a lot.

Lin Ma