Handling millions of data in the result set

[vani]

HI All,

We are working on a search application, where the number of records in the
database are in millions. For certain search conditions, the search result
results in more than million records. When the result set is too huge we are
getting the following message :

"Timeout expired. The timeout period elapsed prior to completion of the
operation
or the server is not responding."

Database Used : SQL server 200
Platform used : windows 2000
Languages Used : ASP.NET, C#

When we run the Stored proc which we use to get the search result at the
backend, it takes more than 3 min to return more than million records
otherwise stored proc is fast.

How this can be handled? What is the solution?

Thanks in advance.
Vani

[Cam]

To avoid getting that error message you can set the CommandTimeout property
on the SqlCommand object, I believe that the default is 30 seconds. Try 300
and see where that gets you.

"vani" wrote:

HI All,

We are working on a search application, where the number of records in the
database are in millions. For certain search conditions, the search result
results in more than million records. When the result set is too huge we are
getting the following message :

"Timeout expired. The timeout period elapsed prior to completion of the
operation
or the server is not responding."

Database Used : SQL server 200
Platform used : windows 2000
Languages Used : ASP.NET, C#

When we run the Stored proc which we use to get the search result at the
backend, it takes more than 3 min to return more than million records
otherwise stored proc is fast.

How this can be handled? What is the solution?

Thanks in advance.
Vani

[vani]

Hi Cam,

Thanks for the immd. response.

We tried extending commandtimeout but we had problem saying that server
unavailable. We found that it may be because of ASP.NET page timeout after
3min.

We are thinking some alternative to this solution, Is it possible to display
the search result before finishing the Stored proc execution fully. say may
be a 1000 records at a time, using Response.flush().

Expecting quick reply.

Thanks,
vani

"Cam" wrote:

To avoid getting that error message you can set the CommandTimeout property
on the SqlCommand object, I believe that the default is 30 seconds. Try 300
and see where that gets you.

"vani" wrote:

HI All,

We are working on a search application, where the number of records in the
database are in millions. For certain search conditions, the search result
results in more than million records. When the result set is too huge we are
getting the following message :

"Timeout expired. The timeout period elapsed prior to completion of the
operation
or the server is not responding."

Database Used : SQL server 200
Platform used : windows 2000
Languages Used : ASP.NET, C#

When we run the Stored proc which we use to get the search result at the
backend, it takes more than 3 min to return more than million records
otherwise stored proc is fast.

How this can be handled? What is the solution?

Thanks in advance.
Vani

[Cam]

> We are thinking some alternative to this solution, Is it possible to display

the search result before finishing the Stored proc execution fully. say may
be a 1000 records at a time, using Response.flush().

I'd suggest delivering the results in chunks, requesting, say 1000 records
at a time for display and allowing the user to page through the results.

This article seems to detail a method that I've used before for such a
solution:
http://www.samspublishing.com/articl...31263&seqNum=3

Cheers,
C.

[vani]

thanks Cam for the quick reply.

we will try this out and let u know.

Thanks,
Vani
"Cam" wrote:

We are thinking some alternative to this solution, Is it possible to display
the search result before finishing the Stored proc execution fully. say may
be a 1000 records at a time, using Response.flush().

I'd suggest delivering the results in chunks, requesting, say 1000 records
at a time for display and allowing the user to page through the results.

This article seems to detail a method that I've used before for such a
solution:
http://www.samspublishing.com/articl...31263&seqNum=3

Cheers,
C.