ADO slower than ODBC?

Posted on 2003-02-19
Medium Priority
Last Modified: 2013-11-23

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
Question by:Landlyst
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 22

Accepted Solution

Mohammed Nasman earned 300 total points
ID: 7986053
  In much cases ADO is faster than ODBC, but the way you are using it will make the different in performace

  for example, you are using Cursorlocation = clUseClient, this mean that all the data will cached to the client, so this will take very long time with big tables, you should use where clause to return the records needed but not all the table

  also try to make the cursorlocation to be on server

  but always try to return specific records, not all the record

Best regards
Mohammed Nasman

Expert Comment

ID: 7986329

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.


Expert Comment

ID: 9317170
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 
Post your closing recommendations!  No comment means you don't care.
LVL 10

Expert Comment

ID: 9928525
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.


Some days and here is the Christmas Time. I wish good luck and good health for you all and for your loved ones

EE Cleanup Volunteer

Featured Post

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month14 days, 22 hours left to enroll

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question