.NET does not Support Paging???

  • Thread starter Thread starter Jo
  • Start date Start date
J

Jo

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 response would be appreciated - even if it is to commiserate the
ineffectiveness of .NET paging.

thanks
Johan.
 
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).
Click to expand...

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 said:
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.
Click to expand...


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
 
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
Click to expand...

OK - now I see what you're doing. You weant to process some subset of
the full table, but you don't know what the id of the table is so you can't
restrict the rows returned by the SQL.
I don't see what there is to fix here. Everything is working exactly as
I'd expect it to. If your app has to be so flexible that it takes in data
from totally unknown sources then you have to pay the price for that flexibility
(there is always a price).
What changes would you like in the DataAdaptor or DataSet or whatever?
How would you tell it that you want the 1001st row to the 2000th row and
not to return the others? The ,NET components aren't returning the data:
the RDBMS is. This is the job of the RDBMS and if there isn't a way to
tell the RDBMS then blaming .NET for what the RDBMS returns seems a bit
harsh....
 
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
 
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.
 
Hi Jo,

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.
Click to expand...

From the docs:
<cite>
The DataReader provides an unbuffered stream of data that allows
procedural logic to efficiently process results from a data source
sequentially. The DataReader is a good choice when retrieving large
amounts of data because the data is not cached in memory.
</cite>

The problem is that one way or another you have to read the first N rows
before you start fetching the data you need. But thats the world we live
in :)

Sunny
 
As a last note - I discovered something very interesting.

I run the exact same code against a Oracle DB and it pages perfectly!!!!!
Only gets 10 000 rows at a time against a 6 million row db - hardly any
network traffic.

this can only mean that the Ole provider for SQL does not support paging and
the Ole Provider for Oracle does.

thanks for your comments - I will look around for a newer / better provider
for SQL.

Johan
 
As a last note - I discovered something very interesting.

I run the exact same code against a Oracle DB and it pages perfectly!!!!!
Only gets 10 000 rows at a time against a 6 million row db - hardly any
network traffic.

this can only mean that the Ole provider for SQL does not support paging and
the Ole Provider for Oracle does.

thanks for your comments - I will look around for a newer / better provider
for SQL.

Johan
Click to expand...

It would be interesting to see what the SqlClient namespace objects do.
They are probably your best bet....
 
Back
Top