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. 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
"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
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
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
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
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
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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
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"
|
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,
|
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...
|
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...
|
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.
| |
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....
|
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?
|
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...
|
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
|
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,...
|
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...
| |
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...
|
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...
|
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...
|
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();...
|
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...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
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...
| |