Re: Performance Question when using a DataReader

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.
__________________________________


"BBM" <bbm@xxxxxxxxxxxxxx> wrote in message
news:0FD567B5-713F-4A95-A26A-38C25CE5CFCC@xxxxxxxxxxxxxxxx
> 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
>
> "William (Bill) Vaughn" wrote:
>
>> 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.
>> __________________________________
>>
>>
>> "BBM" <bbm@xxxxxxxxxxxxxx> wrote in message
>> news:ED1EB317-0A8D-49C2-96FA-ACAE3DE808E3@xxxxxxxxxxxxxxxx
>> > 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.
>> >
>> > "Kourosh" wrote:
>> >
>> >> BBM wrote:
>> >> > 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
>> >> >
>> >> check : DataAdapter.Fill method parameters
>> >>
>>
>>
>>


.

Relevant Pages

  • Re: Portable stored procedures
    ... > I haven't used Java stored procedures with databases but your statement is ... a string, that was needed in the client and in the server - in many cases, ... language you are using) and in the server in some sql-script language - or C ... memory size limits for JVM's on certain platforms etc.). ...
    (comp.lang.java.databases)
  • Re: migrating from MSAccess...
    ... > using MSAccess for some simple databases. ... > We currently have separate databases ... and add a key field to show which client the records belong to. ... > of that front-end linked to the master tables on the server. ...
    (microsoft.public.sqlserver.server)
  • Re: Windows 2003 Certificate Services - problem downloading Active X control
    ... Both client and server need to be updated. ... This posting is provided "AS IS" with no warranties, ... "Bill" wrote in message ...
    (microsoft.public.security)
  • Re: How-to Uninstall WSUS
    ... Still here Bill. ... log, server debug log, windows update log, server config, isa2004, and the ... Never met a client that realistically expected me to have all the answers. ... Yes, for 95+% WSUS just works, but those exceptions can be bears to ...
    (microsoft.public.windows.server.sbs)
  • Re: delayed write failures
    ... In Device Manager, goto Properties of the Drive, select the "Disk ... > Our databases are residing on the Windows2003 File share, They are a mixture> of foxpro and access databases, main issue we are seeing is when we do> compacting of access databases and reindexing of the foxpro databases. ... >>>delayed write errors on the client machines, ... >>>turn of op locking on client and server - DONE ...
    (microsoft.public.windows.server.networking)
Loading