Performance Question when using a DataReader

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

Guest

I am using a DataReader to read what can potentially be a pretty large result
set (thousands of records).

Most of the time, I only need the first X records of the result set (I can't
predict in advance how many records I'll need). In my case, where there
could potentially be thousands of records that meet my select criteria, but
where I'm probably only going to use a much smaller number, is there a
performance gain from setting the "ROWCOUNT" returned by SQL Server to a
small value. If necessary I can requery the database to get the next X
records until I am done.

I guess I'm concerned that although the DataReader is getting records for me
one at a time that SQL Server is building an indexed list of thousands of
records.

Thanks.

BBM
 
BBM said:
I am using a DataReader to read what can potentially be a pretty large result
set (thousands of records).

Most of the time, I only need the first X records of the result set (I can't
predict in advance how many records I'll need). In my case, where there
could potentially be thousands of records that meet my select criteria, but
where I'm probably only going to use a much smaller number, is there a
performance gain from setting the "ROWCOUNT" returned by SQL Server to a
small value. If necessary I can requery the database to get the next X
records until I am done.

I guess I'm concerned that although the DataReader is getting records for me
one at a time that SQL Server is building an indexed list of thousands of
records.

Thanks.

BBM
Click to expand...
check : DataAdapter.Fill method parameters
 
<<I am using a DataReader to read what can potentially be a pretty large
result
set (thousands of records).>>
Click to expand...

Chances are that in any one sitting, you won't need all of those at once.
In most instances, if you need this much data, it's probably better to just
get what you need when you are sure you need it and deal with smaller sets.
Why? B/c if I have 100000 records, and I only need records 1-10 and
99995-100000, then I actually only need 15 records, so pulling them over
will cause extra resources on the db and locally (that data will have to sit
somewhere until I actually need it).

predict in advance how many records I'll need). In my case, where there
could potentially be thousands of records that meet my select criteria,
but
where I'm probably only going to use a much smaller number, is there a
performance gain from setting the "ROWCOUNT" returned by SQL Server to a
small value. If necessary I can requery the database to get the next X
records until I am done.>>
Click to expand...
This really is one of those, "IT depends" type situations but in most cases,
you probably don't need to pull all that data over. There' overheard
associated with opening and closing connections and firing additional
queries, but if handled correctly and if pooling is ON, then this impact is
fairly small. As such, it's hard to imagine that if you had a 10k row
query, that they user would actually page through all of it (even if they
did, it's probably not typical). .. so you would probably be a lot better
off just grabbing what you needed. If I would only page through lets say 5
different sets of 10 records, then pulling over all 10k is going to cause a
lot of resources to be used that don't really provide any benefit.

Another thing, you can use an adapter and change it's commandtext, and fill
the same datatable . So you could se the command text to pull the first 10
records. Then, if the user pages to another set, add those records by
changing the commandtext of the adapter's select command, and just fill the
SAME datatable without clearing it. Now, you'll have all of the first
results records, and the second as well. Repeat. Also, chances are that if
you needed the results once, that you're more likely to need it again - just
to double check the values, confirm your work or whatever. So keeping it in
the datatable will allow you to go back without having to hit the db again.

In this instance, using a DataTable may be the better approach, although as
a rule, if you don't need the data more than once (ie once you read it,
you're done with it) than storing it in a datatable isn't typically a good
choice when performance is a big deal.

HTH,

Bill
 
AFAIK, runing

SELECT TOP X * FROM table

is faster than runing

SELECT * FROM table

HTH

Elton Wang
(e-mail address removed)
 
Thanks for your response. I'll look at these but can I use a DataAdapter to
do something other than to fill a DataSet or DataTable? Both of these are a
little too heavyweight for what I'm doing.
 
Thanks everyone for your input.
1) I only need to touch the records once so I really don't need a DataTable
2) Nobody responded to my "ROWCOUNT" idea, but I think I like using 'TOP'
better
3) I'm going to try the app both (Selecting the whole result set, and using
'TOP' and see if there's any difference.

Thanks again.

BBM
 
Thanks for your response.

I'm going to try to use 'TOP' to restrict the number of rows returned and
see if it makes a difference.

BBM
 
Ancient Education Hill proverb: If you need to worry about fetch
performance, you're fetching too many rows.
If you say you're only "touching the rows once" then I suspect you're under
the impression that data changes generated by code should be done on the
client. This is usually not the case if you care about performance--and it
looks like you do.


--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
www.sqlreportingservices.net
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 Bill,

Thanks for the tip. The app I'm working on at this time is a prototype, and
has a local database. When we go to the production version, I'm sure we'll
have to take your comments into consideration. I presume you're talking
about having a server resident data access tier and delegating the updates to
it - or maybe calling stored procedures on the server from the client?

Thanks again.

BBM
 
Yes, for the most part. Client applications (where a user interacts with the
data) should be designed so that just the "right" amount of data is
presented to the user (human) so they can make an informed decision about
what to do with the data. This does not mean transporting the entire
database or even significant parts of it to the desktop (or to the IIS
server) to show to the user. Generally (and there are always exceptions),
the user should be encouraged to help focus the server on the needed data
and fetch just what's needed.
Quite a bit (more than half) of the time I worked with mainframe data and
large server databases, I spent "massaging" data--not in query interaction
driven by the client. These tasks would often take hours (or days) to run as
we move vast mountains of data from place to place, adjusted, tuned,
refined, clarified and filtered it. We made every effort to maximize the
performance of these routines often creating databases to manage the
databases. We built rollup and archival databases and imported data from
other systems--often from remote sites all over the country. Virtually all
of these tasks were done on the server--not by moving data to the client,
making adjustments and shipping it back to the server. ADO has been terrible
at this. It was never designed to handle this task (not until ADO 2.0).
Using a query interface to do this work is problematic at best. Lately I've
been extolling the virtues of DTS and BCP to do the transporting and
server-side SPs to do the grunt work to massage the bits. Yep, these
routines can bring a server to its knees (just like it put the mainframe's
wait light out). This meant that we scheduled these tasks in the "batch"
cycle at night (between 18:00 and 06:00) or offloaded the data to other
systems that were not impacted by the performance hit.

I could go on... but you get the idea.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
www.sqlreportingservices.net
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Why not use Stored procedure to implement it.

Use datareader and commander to call the stored procedure with paramters
 
Back
Top