Submit My Question - Recent Questions - Recent Answer
Msdn Tech >> .NET Development >> .NET Framework Data Access and Storage

SqlDataReader is slow

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

[1962 byte] By [RamaKrishna] at [2008-2-27]
«« Fix for .NET Framework API reference documentation in Visual Studio Express Editions
»» MSDN Forums Top Users - 5/27/05
# 1
Hi,
This is just a hunch. But could you try moving the code

object[] myObjectArray = new object[dataReader.FiledCount];

outside the loop? The code seem to create and destroy your object during the loop. The idea here is to try reusing your object (myObjectArray). Maybe it could take a small burden out of your loop...
cheers,
Paul June A. Domag

PaulDomag at 2007-9-8 > top of Msdn Tech,.NET Development,.NET Framework Data Access and Storage...
# 2
RamaKrishna wrote:

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.

That's more than likely SQL's built in caching system in full affect. Works great eh? :-)

mxmissile at 2007-9-8 > top of Msdn Tech,.NET Development,.NET Framework Data Access and Storage...
# 3

Hi,
Thanks for the response. There is no cache stuff here becasue I am tracking the time for each method call here. The SP gets executed in less than a minute. Then the DataReader's GetValues(..) method takes time for every few records. Like, a few records it reads in no time and then it reads for about 0.0100146 seconds. Then again it reads some vaues (GetValues method) in almost zero seconds then again after a few records the GetValues method takes 0.1301898 (this value keeps increasing as more and more records are read). Then again it (GetValues method) reads some records in zero seconds then again it takes 0.1702482 seconds then reads some with zero time and this continues .....and in the end it execute the GetValues for 0.5407884 seconds and then reads the rest of the records with zero time. I even have found the rate at which records are being read
1st minute - 20,481 records are read
2nd Minute - 10,639 records are read
3rd Minute - 8157 records are read
4th Minute - 6,847 records are read
5th Minute - 6001 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 seconds - 407 records are read
Now where is cache stuff here. when I execute my next job the SP is the same but the data is different right ? So ideally the DataReader should take the same amount of time. The time is being wasted in executing the SP but in reading the data that as a result of SP.
I dont understand why DataReader periodically takes long time to read..
Any help on this is greatly appreciated. This is giving me a real tough time.
Have a nice day.

cheers,
kris

RamaKrishna at 2007-9-8 > top of Msdn Tech,.NET Development,.NET Framework Data Access and Storage...
# 4
Hi Paul,
Thanks for that. I did it now, but obviously no major difference.
Thanks for the tip anyways.

Cheers,
Kris

RamaKrishna at 2007-9-8 > top of Msdn Tech,.NET Development,.NET Framework Data Access and Storage...
# 5
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 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

RamaKrishna at 2007-9-8 > top of Msdn Tech,.NET Development,.NET Framework Data Access and Storage...
.NET Development
.NET Framework Data Access and Storage

.NET Development Hot Topic

.NET Development New Topic

.NET Development

Java / MSDN / Linux Tech / PHP Tech / MSDN DotNet / MSDN TECH / Health Library / Programmers Tech / visual basic Tech / Developer Tech / Ipod faqs / Msdn China Tech

Site Classified