Re: SqlDataReader performance

XML and performance cannot be used in the same sentence. The other question
you need to ask is "Why?". Why move so many rows to the client? Shouldn't
the data be managed in the Database Management System--that's what they're
designed to do. Too many developers treat the DBMS as a file store and fetch
much of the database to the client to process. Most serious DBMS engines can
process data locally and return the compiled/computed/correlated results of
these operations far more efficiently than moving bulk data to the client
and crunching it there.

As far as making the server-side query run faster, investigate the Query
Analyzer that helps tune the query so it can run as efficiently as possible.
All too often I've seen query problems boil down to proper query and index
design (and maintenance) and knowing how the engine processes the queries.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
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.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

"Jan" <jango@xxxxxxxxxxxxxxxx> wrote in message
news:28EB7EC6-2914-4500-9659-BE482466D94B@xxxxxxxxxxxxxxxx
Wen Yuan

Thanks for the input so far.

I already figured that the collumn count was a bit steep, and are in the
process of optimizing the data model as well as the query.

I'll do a test on the datatyping changes you suggested, but I'm pretty
sure
this is not where I'm loosing valuable seconds.

Do you know of any other method that is more effective at moving large
rowsets from the db server to the client. How about the sql xml methods?
Do
they deliver compareable performance (or better)?

Best regards

Jan
--
Do or die..


"WenYuan Wang" wrote:

Hi Jan,
Thanks for your reply.

What "cmd.ExecuteReader()" was to query the DataBase and return a cursor
(not the whole data).
Each time the data row will be returned from DataBase when the statement
"dr.read()" is executed.
For these reasons, as you know, the execute query will not take a long
time(1.6 seconds in your case), but retrieval of data is time consuming
(when you have huge amounts of data returned).

I think the root cause of this issue is the Query String(returns 30+
collums from 4 joined tables). It should effect performance if you try to
returning fewer columns.
(Tip: SELECT statement should only return the columns/rows that are going
to be used, possibly for display purposes or some business logic.)

Some suggestions:
Have you tried testing whether the time is spent in the data retrieval
from the DataReader, or the ToString call?
Is dr[keyname] already a string, or is it some other data type that's
being
converted with ToString?
If it's the latter, I would try profiling what happens if you do this
instead:

object val = dr[keyname];
string keyvalue = val.ToString();
This will not help your performance, but will help isolate what the
problem
is.

If dr[keyname] is a string value, try using dr.GetString(keyordinal)
instead of dr[keyname], which is basically
dr.GetValue(GetOrdinal(keyname)).
In doing so you will avoid boxing the value into an object.

If there is anything unclear, please feel free to reply me.
I'm glad to work with you.
Best Regards,
Wen Yuan




.

Relevant Pages