473,325 Members | 2,872 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

large SELECT with ORDER BY very slow (only in ado.net)

Hello,

I have a problem that actually doesn't even make sense.
I have 4 million rows in my database. I want to get all records into a
DataReader and then read. So if I do it in DAO (either in VB or MFC C++)
it takes around 1 second to get the recordset and then around 10-15
seconds to iterate through the recordset. This speed remains the same even
if I incorporate an ORDER BY clause:
SELECT * FROM myTable ORDER BY customer_id, order_Date;
Without the ORDER BY statement i obtain the same speed in C# using
ado.net. I tested the four possibilities:
1. using OLE DB data provider with Ms Access.
2. using ODBC data provider with Ms Access.
3. using ODBC data provider with Sql Server.
4. using SqlClient data provider with Sql Server.

BUT, when I incorporated the ORDER BY clause each of the first three took
around 3 minutes!!! (the forth was around 30 seconds which is still very
very slow compared to the DAO (which is an older technology and depricated
in vs.net). NOTE: all the extra time is from the method
DbCommand.ExecuteReader() not from iterating through the dataReader.
It's not a question of reducing the size of data i retrieve or indexing
the relevant fields in the database as I want to achieve with ado.net the
same speed I get with DAO. In my experiment I use the same database, same
tables and corresponding objects (as a RecordSet in DAO is the same as a
DataReader in ado.net).
Another thing i noticed is that i use a dynaset in DAO (which no longer
exists in ado.net). Still need to find a solution for this problem.

Thanks,
Dror

Jul 21 '05 #1
3 4803
On Mon, 27 Dec 2004 06:56:24 -0500, "dror" <dr*****@gmail.com> wrote:

¤ Hello,
¤
¤ I have a problem that actually doesn't even make sense.
¤ I have 4 million rows in my database. I want to get all records into a
¤ DataReader and then read. So if I do it in DAO (either in VB or MFC C++)
¤ it takes around 1 second to get the recordset and then around 10-15
¤ seconds to iterate through the recordset. This speed remains the same even
¤ if I incorporate an ORDER BY clause:
¤ SELECT * FROM myTable ORDER BY customer_id, order_Date;
¤ Without the ORDER BY statement i obtain the same speed in C# using
¤ ado.net. I tested the four possibilities:
¤ 1. using OLE DB data provider with Ms Access.
¤ 2. using ODBC data provider with Ms Access.
¤ 3. using ODBC data provider with Sql Server.
¤ 4. using SqlClient data provider with Sql Server.
¤
¤ BUT, when I incorporated the ORDER BY clause each of the first three took
¤ around 3 minutes!!! (the forth was around 30 seconds which is still very
¤ very slow compared to the DAO (which is an older technology and depricated
¤ in vs.net). NOTE: all the extra time is from the method
¤ DbCommand.ExecuteReader() not from iterating through the dataReader.
¤ It's not a question of reducing the size of data i retrieve or indexing
¤ the relevant fields in the database as I want to achieve with ado.net the
¤ same speed I get with DAO. In my experiment I use the same database, same
¤ tables and corresponding objects (as a RecordSet in DAO is the same as a
¤ DataReader in ado.net).
¤ Another thing i noticed is that i use a dynaset in DAO (which no longer
¤ exists in ado.net). Still need to find a solution for this problem.

The problem is probably due to the fact that ADO.NET will attempt to return the *data* for all rows
(to the client) upon execution of the query, and then disconnect. A DAO Dynaset Recordset type, on
the other hand, will only return a small subset of the data (and all keys), remain connected and
retrieve additional data as it is requested through the Recordset object.

ADO.NET doesn't support dynamic result sets. The result sets returned are essentially disconnected.
The closest equivalent in DAO to an ADO.NET DataReader would be a Snapshot (forward-only).
Paul ~~~ pc******@ameritech.net
Microsoft MVP (Visual Basic)
Jul 21 '05 #2
Hi,
Thanks for the reply,
I've been experimenting with this for the last few days. I would like to
move to ado.net (DAO has been depricated). I've tried odbc (mssql &
access), oledb (mssql & access) and SqlClient providers and none reach the
performance of DAO's dynaset. I understand the reason for that, as you
explained, but I still need to find a solution as on one hand DAO is
depricated and on the other ado.net's performance is poor (even for
smaller sizes of queries DAO performs better, and not to mention the
performance of filling a DataSet which is even slower). What should I do
in this case? What API should I use? Btw, even legacy ADO performs slower
with dynamic recordset. Why is the idea behind dynaset being depricated,
is microsoft giving up on fast technology? What other API can be used that
runs fast?

Dror

Jul 21 '05 #3
On Mon, 27 Dec 2004 15:55:34 -0500, "dror" <dr*****@gmail.com> wrote:

¤ Hi,
¤ Thanks for the reply,
¤ I've been experimenting with this for the last few days. I would like to
¤ move to ado.net (DAO has been depricated). I've tried odbc (mssql &
¤ access), oledb (mssql & access) and SqlClient providers and none reach the
¤ performance of DAO's dynaset. I understand the reason for that, as you
¤ explained, but I still need to find a solution as on one hand DAO is
¤ depricated and on the other ado.net's performance is poor (even for
¤ smaller sizes of queries DAO performs better, and not to mention the
¤ performance of filling a DataSet which is even slower). What should I do
¤ in this case? What API should I use? Btw, even legacy ADO performs slower
¤ with dynamic recordset. Why is the idea behind dynaset being depricated,
¤ is microsoft giving up on fast technology? What other API can be used that
¤ runs fast?

My suggestion would be to implement a paging mechanism similar to what you see
in web pages that display long lists of data. You can restrict your queries to
return only a certain number of rows at a time. If you're using SQL Server or
Access, the TOP keyword would do this. Whenever you need to display data beyond
the end of your current dataset, query the database and select all data where
the field you are ordering by is greater than the value of that field for the
last row of the current dataset. This is somewhat similar to how DAO works with
Dynasets, although all the work is performed under the covers.

With respect to your other questions, ADO is slower than DAO, primarily because
DAO talks directly to the Jet database engine while ADO operates through an
additional data access layer called OLEDB.

In addition, Microsoft has slowly moved away from the concept of persistent
connections and server-side cursors. With the movement toward web development
and thin clients the emphasis has been more toward the concept of data
manipulation in a disconnected environment, either at the server application
level or desktop client application level, significantly reducing the usage of
database resources. Connection resources only exist for the time in which an
update or retrieval of data is required. The middle-man, database cursors, have
been more or less rendered obsolete.

I don't think this is necessarily the correct approach as there are applications
that would significantly benefit from the features present in data access
mechanisms such as DAO.
Paul ~~~ pc******@ameritech.net
Microsoft MVP (Visual Basic)
Jul 21 '05 #4

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

Similar topics

13
by: StealthBananaT | last post by:
My database has two tables - films has 10,000 records and reviews has 20,000 records. Whenever I try to list all the films and the count of its reviews, MySQL locks and I have to restart the...
1
by: Philipp K. Janert, Ph.D. | last post by:
Dear All! I am trying to load a relatively large table (about 1 Million rows) into an sqlite table, which is kept in memory. The load process is very slow - on the order of 15 minutes or so. ...
3
by: Jeremy Howard | last post by:
I am finding delete queries on large InnoDB tables very slow - are there ways to speed this up? I have a table with about 100 million rows: I am trying to delete just a few of these rows (the...
5
by: Thomas Andersson | last post by:
Hi, I am trying to find a working solution for download of large files (400-800 MB)... But this seems almost impossible to find a working example. I have tried Response.Transmitfile, this...
4
by: sherkozmo | last post by:
SQL2000 - AccessXP I built an adp file with a stored procedure from SQL as follows: SELECT * FROM Z_mis_sjk_job_code_access WHERE job_code=@JobCode UNION ALL SELECT * FROM...
3
by: Buddy Ackerman | last post by:
I'm trying to write files directly to the client so that it forces the client to open the Save As dialog box rather than display the file. On some occasions the files are very large (100MB+). On...
3
by: dror | last post by:
Hello, I have a problem that actually doesn't even make sense. I have 4 million rows in my database. I want to get all records into a DataReader and then read. So if I do it in DAO (either in VB...
0
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...
2
by: ARC | last post by:
I'm testing a user's db that contains a very large number of records. I have an invoice screen, with an invoice select dropdown box that shows all invoices, and the customer's name, etc. With...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 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 former...

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.