Landlyst
asked on
ADO slower than ODBC?
Hi
In my Delphi 6 app, I am accessing a table on a MS SQL server with a little over 1 million records (Stock transactions). When I access it with ADO (tAdoQuery) with Cursorlocation = clUseClient it takes forever to open the dataset, and I need to use clUseClient in order to browse the dataset.
If I access the table through an ODBC datasource using TQuery it is a least 10 times faster to open the table and I can still browse the dataset.
I want to use ADO because it is easier to distribute, but how do I speed it up?
Thanks in advance
In my Delphi 6 app, I am accessing a table on a MS SQL server with a little over 1 million records (Stock transactions). When I access it with ADO (tAdoQuery) with Cursorlocation = clUseClient it takes forever to open the dataset, and I need to use clUseClient in order to browse the dataset.
If I access the table through an ODBC datasource using TQuery it is a least 10 times faster to open the table and I can still browse the dataset.
I want to use ADO because it is easier to distribute, but how do I speed it up?
Thanks in advance
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Landlyst:
This old question needs to be finalized -- accept an answer, split points, or get a refund. For information on your options, please click here-> http:/help/closing.jsp#1
EXPERTS:
Post your closing recommendations! No comment means you don't care.
This old question needs to be finalized -- accept an answer, split points, or get a refund. For information on your options, please click here-> http:/help/closing.jsp#1
EXPERTS:
Post your closing recommendations! No comment means you don't care.
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area for this question:
to accept mnasman's answer
Please leave any comments here within the next seven days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
Some days and here is the Christmas Time. I wish good luck and good health for you all and for your loved ones
kacor
EE Cleanup Volunteer
I will leave a recommendation in the Cleanup topic area for this question:
to accept mnasman's answer
Please leave any comments here within the next seven days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
Some days and here is the Christmas Time. I wish good luck and good health for you all and for your loved ones
kacor
EE Cleanup Volunteer
With ADO be carefully with connections. They are very slow on opening… If you make often close/open connection this will hit your performance. Open connection on start of your application and close it on exit.
And of course if your SELECT return millions of records and you use client cursor and your database is over network you must wait from minutes to hours for transferring all records over network to your client machine.
TIA,
Miro.