I have looked and looked and have headrd what Sunny and Simon have to say
but ADO 2.5+ has been able to get me a cursor to whatever dbase I wanted to
and returned a true paged set of data.
I did read somewhere that ADO.NET v2.0 will offer a method called
ExecutePagedReader which will provide true paging but until that is out I
don't think we can really do anything.
Simon : Don't get me wrong c# for .NET - Woohoo , about time a real langauge
came out.
The problem I have with the components is that it all indicates that it will
be returning you a true subset and in one of articles in the MSDN about
halfway down the page hidden amongst other stuff there is a : "And by the
way, the reader will fetch the entire dataset and then fill the subset."
I even tried opening the connection once and then setting up the command and
executing the adapter and then in my loop I would just call the fill method
and it would still run to the DB and fetch all the rows every time.
Sunny : I do not have any problem with tables until they reach the 1000 000
(1 million) row mark. 6000 is not the kind of volumes I deal with so I
cannot afford o have the entire datareader in memory as 1 million rows
already sees my PC sitting with 1.5 GB pagefile and 6 million - HEY I had
my first BSOD in XP.
"Sunny" <su***@newsgroups.nospam> wrote in message
news:eY*************@tk2msftngp13.phx.gbl...
Hi,
just a suggestion. Do not use DataAdapter.Fill. Execute an SQL command
"Select * From xxx", and use the DataReader returned for sequential
access to the returned rows. That way you will not get all the rows, but
only the needed ones and these which are before them. If you need
records from 1000 to 2000 you will end up with reading only 3000
records, not all 6000.
As Simon states, there is always a price.
Sunny
In article <4N********************@is.co.za>, jo****@thepub.co.za
says...
"Simon Smith" <gh*****@community.nospam> wrote in message
news:94******************************@ghytred.com. .. On 07 Jul 2004 10:51, "Jo" wrote:
>Hi,
>
>I am using a OleDB provider that connects to ANY datasource - I do
not know >if these have some nice ID that I can page on.
>
>Using the OleDataAdapter FILL method. I then fill the dataset with
say 1000 >records.
>If the source table is 6 000 000 rows long there is a problem in that
the >datareader fetches all 6 000 000 and then returns the 1000 that I
asked for. >This takes some time if the Provider is on another machine (as it
would be >in any normal 3 tier environment)
>
>Is there any way that this will be fixed in a future release or is
there a >workaround without using "SELECT TOP n" in the select command as I do
not >know the provider I am connecting with at the time and I do not know
if >there is a ID col. on the table (could be oracle which does not
support TOP >n).
>
Any SQL query should only return exactly what you asked for. If more
is being returned than you asked for then the database you're using needs
to be severely admonished....
How do you know that the DataReader has all 6m rows in it? If you're
using DataAdaptor.Fill you don't get to see the DataRead it uses.
--
Simon Smith
simon dot s at ghytred dot com
www.ghytred.com/NewsLook - NNTP Client for Outlook
Run this over a network and use (Pseudocode)
Command = "Select * from Tasble"
OleDataAdapter.comman = Command
OleDataAdapter.Fill(oDS)
Measure total amount of data that is returned - Takes a bout 2 minutes
over a 10mip network,
change code to read
Run this over a network and use (Pseudocode)
Command = "Select * from Tasble"
OleDataAdapter.comman = Command
OleDataAdapter.Fill(oDS, nStart, nEnd)
Where nStart could be 1000 and nEnd could be 2000 (ie 1000 rows)
Check the amount of network traffic that comes back and voila - exactly
the same as the complete select. and also takes 2 minutes
I undestand that the way that I could do it is to say "Select TOP 1000
from table where ID > " + SomeID
But if you read what I wropte above - I do not know what that ID is as
it could be any Provider / Table