Enhancing ADO Performance

Enhancing ADO Performance

Post by Dave » Fri, 25 Jun 2004 03:33:56


We are currently exploring the retrieval of data for our software from
database sources by composing some small test applications (D7 Pro). We
currently have applications constructed to retrieve data from ODBC
tables using the native BDE connection and using the ADO connection. We
worked with both because of the installation issues when using the BDE
approach, such as the bundled BDE dll and potential registry access for
BDE purposes during the application installation. It seems that ADO
would avoid many of these BDE installation issues, but also seems to be
somewhat slower in its data retrieval than the BDE application we've
written to retrieve the same data from the same source.

Does anybody have any suggestions on how to improve the performance of the
ADO connection?

Thanks
Dave Savageau
 
 
 

Enhancing ADO Performance

Post by Del Murra » Fri, 25 Jun 2004 08:03:28

2 big warnings on performance ...

1. dont use tables, unlike the BDE, ADO reads the whole table when you open
it. ..... bummer .
2. Disable controls for datasets until you need to have the data apear in
the control. There is a quirk in movement technique thru the dataset the is
substantially improved if you disablecontrols , especially for datasets that
dont use data aware objects suchs as tdbEdit, tdbGrid .. etc.
3. Use tADODataset , tADOQuery is for backward compatiblity,
4. Be careful of locktypes ... not super critical, just be mindful
5. Dont read data that you dont use
6. Indexes and proper use of the DB are very important.

HTH

 
 
 

Enhancing ADO Performance

Post by Arnie Maue » Fri, 25 Jun 2004 23:23:51


We went through the same thing when porting from the BDE to ADO.

You can do a lot to tune up an ADO dataset or query via the properties. You
have to understand these. Check the help. For example, if looking up a lot
of data and hooking to a DB grid, use a server-side cursor (CursorLocation).
Only enough data will be brought over to the client to satisfy the grid. A
cient side cursor will read the entire result set over to the client.

If you're not hooked to a DB aware control and just have to read and process
the data (and a lot of it), set CursorType to ctOpenForwardOnly. ctKeyset
is required for hooking to a grid and is much slower because the grid has to
move forward AND backward in the result set.

If you're reading quite a bit of data, set the BlockReadSize property to
100-400. This improves performance quite a bit.

Also, as Del said, avoid the TADOTable like the plague.

HTH,

- Arnie
 
 
 

Enhancing ADO Performance

Post by Arnie Maue » Fri, 25 Jun 2004 23:40:34


Oh yeah, a couple of more things. We had a LOT of code using the BDE. I
created our own set of DB objects derived from the ADO objects but that
provided properties and methods that were compatible with the BDE. This was
not a small project but we avoided changing the bulk of our code.

Also, use parameterized SQL statements. This is in general and not ADO
specific. Any real DB engine (Oracle, SQL Server, DB2) will cache SQL
statements after parsing and execution. The execution plan is also saved.
Therefore, if you execute a statement like (below) multiple times,

SELECT * FROM Atable WHERE Foo = :foo and Bar = :bar

The DB server will use the cached statement. Whereas, executing:

SELECT * FROM Atable WHERE Foo = 2 and Bar = 5
and
SELECT * FROM Atable WHERE Foo = 15 and Bar = 55

will be seen as two distinct statements. The second execution will be
reparsed.

In addition, using parameters for date and string means you don't have to
worry about date formats and dequoting strings.

- Arnie
 
 
 

Enhancing ADO Performance

Post by finalfile » Mon, 28 Jun 2004 14:05:18


All of the other replies +

TADOConnection.CursorLocation := clUseServer;

can make a dramatic difference.



--


Pierre AKA finalfiler
Final Filer Software
http://www dot finalfiler dot com
Worrigee, NSW, Australia 2540
------------------------------