why is sqldatareader so slow ?

[marc hugon]

Hello

I'm working on a batch program (command line utility), and I'm having
speed problems to read a lot of data from a database.
At first I thought it was what i was doing with the data that was slow
(and I can work on that), but I've encoutered another problem.

let's have a look at my current source code (removed lot of things) :

SqlConnection myConnection = new SqlConnection("Sql connect string");
myConnection.Open();
string mySelectQuery = "something a little fancy";
Console.WriteLine("Launch request "+DateTime.Now.ToLongTimeString());
myCommand = new SqlCommand(mySelectQuery,myConnection);
myCommand.CommandTimeout=600; //it may be long, so...
SqlDataReader myReader;
myReader = myCommand.ExecuteReader();
Console.WriteLine("Data work "+DateTime.Now.ToLongTimeString());
int counter = 0;
while (myReader.Read())
{
counter++;
}
Console.WriteLine(counter.ToString());
myReader.Close();
myConnection.Close();
Console.WriteLine("End "+DateTime.Now.ToLongTimeString());

and now : what I have on my console when executing this beauty :
Launch request 09:07:47
Data work 09:07:57
17742
End 09:09:37

1:40 minutes to read 17 742 rows of data ??????
how is this possible ??
how to make it work faster ?
any help appreciated

by the way, the sql request is returning 4 rows of the following types
: sqlsingle (real) (x2), string (varchar(7)), int (int(2))
I've tried using a dataset, it was a little slower.
one last information : the sql server is running on my development
computer, so no connexion problem between the command line and the
server....

Any idea, comment, critic, more than welcome

(by the way, I have other functions dealing with more than 500000
lines of data, the complete process takes more than 3 hours, that's
why I MUST find something faster)

Marc

 

[Octavio Hernandez]

Marc,

a) How long does it take to execute the query with the SQL Server tools? If
there it takes a long time, maybe tou should use the SL Profiler or the
Index Tuning Wizard, maybe you lack some indices that could make the query
go faster...
b) The C# code looks fine to me, and I have a lot of similar code in several
apps (well, I don't know how complex you query is, but mine are not simple
for sure), and obtaining a result set of this size is very fast.
c) I suppose in real life you want to do something different than count the
resulting rows; if that is the case, you'd better change the query to SELECT
COUNT(*) ... and put the burden on the server.

Regards - Octavio

 

[marc hugon]

Hello Octavio

Thank you for opening my eyes...
The way I was trying to trace my program was wrong.
The sql command is still running when the datareader starts to
retrieve data, and all the time is spent on the query...

This is where I have to work now, not on the c# program...

Don't know why I was focused on the sqldatareader, thank you very much
for putting me on the right track

Marc

 

[Nicholas Paldino [.NET/C# MVP]]

Marc,

In addition to what Octavio said, you need to also keep an eye out on
the number of columns you are returning in your result set. If there are
columns that you don't need or are not using, then don't have them returned
in the result set. Every time you read to a new row, the server has to send
the full row back, so the more columns in the row, the more information that
needs to be sent back.

Hope this helps.