Performance tweak

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello all,

I've been doing some performance analysis on our app, and I've discovered
that the below code is quite a bottle neck:

this.OnFillParameters(selectCommand);

SqlDataAdapter selectAdapter = new SqlDataAdapter(selectCommand);

try
{
selectAdapter.Fill(resultsDataSet);
......


Does anyone have any suggestions/pointers on how this could be improved.

Any comments welcomed.

Thanks,

JY
 
Hi,

My apologies, I shouldn't use local terms on international forums! :)

By bottle next I mean an area of the application that is the slowest.

Thanks,

Jon
 
Hi Jon,

BottleNeck is a common technical term, so don't sweat it!

You've got some pretty simple code there. How many records does it fetch?

--
HTH,

Kevin Spencer
Microsoft MVP
Software Composer
http://unclechutney.blogspot.com

If the Truth hurts, wear it.
 
Unless you're calling that code inside a loop, I can't see that it's
going to cause performance problems. You can always use a SqlDataReader
directly if you want the fastest access to your data but, in all
likelyhood, the performance problem lies with your database query.
 
Hi Kevin and thanks for the reply.

The SP returns varying amounts of data, but even for say 100 records, it's
slow. I do know that the Database and SP's need to be looked at, for example,
if 1 SP returns 10 rows, it then fires off 10 SP's passing in the values from
the first SP (you get the idea!).

But I'd prefer to start with the C# code, as it's less of a beast, and more
my domain. The DB has other issues, I'm really just trying to quel the fire
so to speak.

I was thinking of using the MS DAB, not the June 2005 release. Would that be
better? I'm really just looking for some advice / help.

Thanks again,

Jon
 
The first thing you should do, if you can, is look at the execution of
your procedure in Sql Query Analyzer and see how much of the time spent
in that block is accounted for by SQL server. Without knowing the
numbers, you can't optimise usefully.

There's not much code there to optimise. It isn't teh uber-leet h4x0r
fastest way of doing DB access, but the difference between that and
other methods is going to be minimal.

If that code is running slowly, I would place hard-earned money on the
issue being time spent inside Sql server. I don't honestly think you
will make any measurable dent on your performance problem by tweaking
that code.

Of course, if I'm wrong, I want to know about it, so let people know
what you find out in any case.

-- Bob Gregory
Unvalued Professional.
 
Hi Jon,

You're correct about optimizing the stored procedures, which I can't really
say anything about. As for your code, yes, it could be optimized, depending
upon your requirements. For example, it sounds like you're simply fetching a
single set of data, or a single DataTable. But you're using a DataSet, which
is a rather large and complex class that mimics a database in many ways,
holding numerous DataTables, handling relationships, etc. One optimization
would be to simply use a DataTable with a SqlDataReader. The SqlDataReader
is a very fast little class (which is actually used by a SqlDataAdapter to
do the work that it does in filling DataTables and DataSets). You can fetch
the data with the SqlDataReader, and then pass it as a parameter to the
DataTable.Load method. See
http://msdn2.microsoft.com/en-us/library/system.data.datatable.load.aspx for
more details, and good luck!

--
HTH,

Kevin Spencer
Microsoft MVP
Software Composer
http://unclechutney.blogspot.com

If the Truth hurts, wear it.
 
Back
Top