473,699 Members | 2,433 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

.NET does not Support Paging???

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.

Nov 16 '05 #1
8 1960
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.Fil l 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
Nov 16 '05 #2
Jo

"Simon Smith" <gh*****@commun ity.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 knowif these have some nice ID that I can page on.

Using the OleDataAdapter FILL method. I then fill the dataset with say 1000records.
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 bein any normal 3 tier environment)

Is there any way that this will be fixed in a future release or is there aworkaround 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 TOPn).


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.Fil l 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

Nov 16 '05 #3
On 07 Jul 2004 14:29, "Jo" wrote:

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.Fil l 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


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

--
Simon Smith
simon dot s at ghytred dot com
www.ghytred.com/NewsLook - NNTP Client for Outlook
Nov 16 '05 #4
Hi,

just a suggestion. Do not use DataAdapter.Fil l. 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.c o.za
says...

"Simon Smith" <gh*****@commun ity.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 knowif these have some nice ID that I can page on.

Using the OleDataAdapter FILL method. I then fill the dataset with say 1000records.
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 bein any normal 3 tier environment)

Is there any way that this will be fixed in a future release or is there aworkaround 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 TOPn).


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.Fil l 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


Nov 16 '05 #5
Jo
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
ExecutePagedRea der 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***@newsgrou ps.nospam> wrote in message
news:eY******** *****@tk2msftng p13.phx.gbl...
Hi,

just a suggestion. Do not use DataAdapter.Fil l. 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.c o.za
says...

"Simon Smith" <gh*****@commun ity.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.Fil l 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


Nov 16 '05 #6
Hi Jo,

In article <19************ ********@is.co. za>, jo****@thepub.c o.za
says...
<snip>

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.


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
Nov 16 '05 #7
Jo
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
"Sunny" <su***@newsgrou ps.nospam> wrote in message
news:%2******** ********@TK2MSF TNGP10.phx.gbl. ..
Hi Jo,

In article <19************ ********@is.co. za>, jo****@thepub.c o.za
says...
<snip>

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.


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

Nov 16 '05 #8
On 09 Jul 2004 10:32, "Jo" wrote:
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


It would be interesting to see what the SqlClient namespace objects do.
They are probably your best bet....
--
Simon Smith
simon dot s at ghytred dot com
www.ghytred.com/NewsLook - NNTP Client for Outlook
Nov 16 '05 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
3569
by: RelaxoRy | last post by:
sqlConnection1.Open(); myReader = sqlCommand1.ExecuteReader(); DataGrid1.DataSource = myReader; DataGrid1.DataBind(); myReader.Close(); sqlConnection1.Close(); The Datagrid populates fine. I'm styling it on the HTML page. I configure the DataGrid for paging through the property builder fine. I AllowCustomePaging=true;. The page loads fine but it only shows "1"
1
1639
by: Guoqi Zheng | last post by:
Sir, The default paging of datagrid is somehow use too much resource, so I am using Stored procedure for the paging. You can find my Stored procedure at the end of this message. It works fine to do paging like this, however, I have found it difficult to do paging and sorting at the same time. For example, in my case, my boss asked me to do sorting on EmailAddress,
6
1813
by: Shawn | last post by:
Hi. I already have a datagrid where I'm using paging. I have a stored procedure that fills a temp table with 200-500 rows and then sends back 10 records at the time. When I go to page 2 the SP fills the temp table again and returns rows 10-19. The temp table is dropped after each call to the SP, so it has to be created and filled every time the user changes page in the datagrid. My question is this: Would it be more efficient to...
3
1446
by: Urs Eichmann | last post by:
Hello I have an ASP.NET web site where you can page through lists by using INPUT TYPE=image buttons. I noticed that, in IE6, the whole page gets cleared and built again from the ground up, whereas in Firefox, the page stays on screen until the server's answer is here. The page is built mainly with CSS layouts and DIV's/SPAN's, altough, for the list view of the actual result, a TABLE is used. The clearing of the page every time the...
9
24313
by: Johnfli | last post by:
ADODB.Recordset error '800a0cb3' Current Recordset does not support bookmarks. This may be a limitation of the provider or of the selected cursortype. I am moving my webserver from NT4 using SQL to win2003 using SQL 2003 I get the above error when my asp page does teh line: Rs.absolutepage = intCurrentPage I tested teh value of intCurrent page and teh value is 1.
2
6446
by: farhad13841384 | last post by:
Hi , I Hope You fine. I have some problem with this code for paging in asp.net this bottom code work correctly without any error but when I try to place separate code in .VB file then error is begin and occured .I want to separate this code and compiling .vb code using VBC.exe later .(bin/paging.dll) when do it like me so you retrive only < Previous Page Next Page > in your web browser and you don't retrive list Of data in your web browser....
0
3388
by: anonieko | last post by:
This approach I found very efficient and FAST when compared to the rowcount, or Subquery Approaches. This is before the advent of a ranking function from DB such as ROW_NUMBER() in SQL Server 2005 and the likes of it. So This one works with SQL2000 What do you think?
2
1933
by: rn5a | last post by:
In a shopping cart app, a ASPX page retrieves the order details & personal details of a user from a MS-Access database table depending upon the username of the user. The order details of a particular order (like ProductID, Name, Description, Quantity etc.) are displayed in one DataGrid where as the personal details of the buyer corresponding to this order (like Name, E-Mail, Shipping & Billing Address etc.) are displayed in another...
3
3739
by: Ronald S. Cook | last post by:
I was told that if calling lots of records from the database (let's say 100,000), that the GridView's paging feature would automatically "handle" everything. But the 100,000 records are still coming to the client, right? I mean, the paging feature isn't somehow making calls to the database for 25 records at a time or anything like that is it? I remember in the past having to write nasty stored procedures that took in
0
8615
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9034
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
8883
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7750
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6534
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5874
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4376
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
2
2347
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2009
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.