Data Retrieval Terribly Slow

Hi,

I'm using ASP.NET 1.1, SQL Server 2000 Server:

I followed the ASP.NET 1.1 Starter Kit's Commerce application and applied the same principles it had written the code to retrieve data to my web application I created. For example I've written this Function in a class to return a sqldatareader:

Public

Function GetAdvanceSearch(ByVal sAsString,ByVal ExtAsInteger,ByVal fdateAs DateTime,ByVal tdateAs DateTime)As SqlDataReaderDim oDrAdSearchAs SqlDataReader
Dim oCmdGetSearchAsNew SqlCommand("spAdvanceSearch", oComConn)With oCmdGetSearch
.CommandType = CommandType.StoredProcedure
.Parameters.Add(New SqlParameter("@.DialNo", SqlDbType.VarChar)).Value = s
.Parameters.Add(New SqlParameter("@.FDate", SqlDbType.DateTime)).Value = fdate
.Parameters.Add(New SqlParameter("@.TDate", SqlDbType.DateTime)).Value = tdate
.Parameters.Add(New SqlParameter("@.Ext", SqlDbType.Int)).Value = Ext
EndWith

oComConn.Open()
oDrAdSearch = oCmdGetSearch.ExecuteReader(CommandBehavior.CloseConnection)

If oDrAdSearch.HasRowsThen
Return oDrAdSearch
Else
ReturnNothing
EndIfEndFunction

And When I'm calling this function I do write in this way (assuming that this function is in a class called "Calls"):

Dim objCalls as New Calls

DataGrid1.DataSource = objCalls.GetAdvanceSearch(<PARAMS......>)
DataGrid1.Databind

My application is a Telephone Call Recording System and could expect vast amount of data. Averagely, a month may produce approximately 50,000 records or more. So while querying through my web application for a month, the application itself either gets stuck or the retrieval speed gets drastically slow. However I'm using Datareaders for every querying scenario. My Web application is hosted in a Windows 2000 Server and accessed via Local Network or IntraNet.

What are the ways I could make this retrieval more speedier and efficient? I would like to hear from anyone who have come across this problem and anyone who could help me on this.

Thanks in Advance. Looking forward for a reply from some one.

Have you narrowed down the bottleneck? Is it the actual stored procedure that is slowing things down, or your data access code?|||

Hi Morton,

Firstly, thanks for the reply. Infact, along with this stored procedure and several others written the same way as I had written earlier in the post is causing the slowness or getting stuck. I couldn't figure out why when querying about 50,000 or more records it gets slow or gets stuck, or this Error page appears:

Server Application Unavailable

The web application you are attempting to access on this web server is currently unavailable. Please hit the "Refresh" button in your web browser to retry your request.

Administrator Note:An error message detailing the cause of this specific request failure can be found in the application event log of the web server. Please review this log entry to discover what caused this error to occur.

Atleast I couldn't query 10,000 records, however lesser than that...it takes little time to upload. Pls. help me on this.

Thanks & Regards,

|||

Hi Hifni,

Did you check the sql statements with in the stored procedurespAdvanceSearch?

Because generally the select statements for searches may use different execution plans for different where conditions so they use different indexes. So it is better to be sure that the necessary indexes exist over the related columns. You can also update the statistics of the database if you have recently a bulk load on related tables.

One important point is if you use a complex sp, when it is compiled by the sql engine it will work with the compiled execution plan afterwards.

So if you should change the sp to use sp_executeSQL to create and use execution plans each time the sp is called. It may help to reduce period of the execution.

Eralper

http://www.kodyaz.com