SQLDataReader is slow - first time

  • Thread starter Thread starter ram_kri
  • Start date Start date
R

ram_kri

Hi Folks,
I have a typical situation here.
My application is a sequence of windows jobs (all are coded in C#).
Now one of my jobs when run is supposed to fetch around 100,000 records
from the SQLDataReader, the SP returns 100,000 records. So, this SP call
from C# and then reading the data using DataReader are happening on one
method say its "myMyMethod()". Every other part of the application is fast
than the part where I am trying to read the data from the DataReader, it
takes 20 min to read the data. I have the time printed before the start of
reading data from the DataReader and immediately after I am done with
reading data from the DataReader (that is how I know that it takes roughly
20 min).
Now I am doing all that stuff that I need to do to have the DataReader
behave fast like, I am using (this is inside myMyMethod()" ) :
while (dataReader.Read())
{
.....
object[] myObjectArray = new object[dataReader.FiledCount];
dataReader.GetValues(myObjectArray);
//Now read each value and cast it to the right type and assign it to the

// valueObject.
myLongVal = (long)myObjectArray[0];
myStringVal = (string)myObjectArray[1];
.......
myObjectArray = null;
}

Now after this job is done (which takes rougly 20 min).
Now I will start running my next job, which calls the same "myMyMethod()"
which I said above. This job also is suppose to get almost the same amount
of data as my Job-1 did. But now the same method runs in just 2 min (as
opposed to 20 min in my previous job).

If I somehow manipulate my data in the DB and run my job-2 first then
again the "myMyMethod()", it takes 20 min now.

I am just confused...can somebody help me. I have checked there is no
memory leak or anything. I dont why the same SP call and the same
DataReader is taking more time first time and second time it runs fast...
Thanks in advance.

Have a nice day.

Regards,
Kris
 
SQL Server is a cache-based server. When you first call the SP it must be
compiled and the query plan cached--this takes a bit of time. When any query
is run, the server looks for the data rows in the cache. The first time the
engine must fetch the rows into the cache. Since you're returning 10,000
rows this can take considerable time. After that, the server can fetch the
pre-compiled query and the data from the RAM cache.

The question I have is why are you fetching so much data?

____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Hi William,
Thanks for the reply. I did much more tracing of this part of application
segment. The problem seems to be with GetValues(..) method that I am
calling on the dataReader. It after every few 100 records the
GetValues(..) method takes 0.050073 seconds. And then for the next 50
records it takes no time (zero seconds to read). Then again it reads for
0.0600876 seconds (the GetValues method) and then again for some records
it takes (GetValues method)zero seconds...as more and more records pass by
it the time it spends in GetValues(..) method goes up like 0.050073 then
zero for some records then again it takes 0.0600876 seconds then again it
is zero for some and then again 0.0901314 seconds .....this continues.
Basically the number of records it reads per minute keeps going down from
20,481 records to 3110 records the last 17th minute as given below :
1st Minute - 20,481 records are read
2nd Minute - 10,639 records are read
3rd Minute - 8,157 records are read
4th Minute - 6,847 records are read
5th Minute - 6,001 records are read
6th Minute - 5402 records are read
7th Minute - 4965 records are read
8th Minute - 4583 records are read
9th Minute - 4337 records are read
10th Minute - 4065 records are read
11th Minute - 3879 records are read
12th Minute -3716 records are read
13th Minute - 3546 records are read
14th Minute - 3410 records are read
15th Minute - 3301 records are read
16th Minute - 3165 records are read
17th Minute - 3110 records are read
last 7 sec - 407 records are read

To answer your question, I need to generate a file with the details that I
pull out and there, its a financial application and we need to process as a
batch towards the end of the day and there could be as many as 100000
records. So I will pull those items out and then try to generate the file
I want.

Now what's wrong with this GetValues(...) method. I guess its more on
dataReading part than SP compilation etc. Because I am tracking the time
the SP is taking to run and it takes only a minute (or less than that) to
execute.

This issue is really giving me tough time...can somebody help ?
Have a nice day.

Regards,
Kris
 
Hi Folks,
I now found that if we I have some Insert queries and select queries
inside a transaction then it takes lot of time to have the SP executed. If
I commit the transaction after the inserts and updates and then If I do a
select it is faster then.
So that fact that SP was getting executed faster was just an illusion...
So the problem was not with DataReader but with the Select query itself
which when is inside a Transaction slows down the whole process. Now the
problem is if I commit the transaction after inserts and updates then what
if sth goes wrong at a later point in time ?
what if my computer shuts down...how would I roll back the whole
transaction because after I fetch the records from the DB, there is lot of
processing that I am doing...
Any ideas on this ?
Thanks in advance.

cheers,
kris
 
Back
Top