ADO performance

As we all know, there are several ways connecting to a database and retrieve data. For example you can use server side or client side cursors, open the recordsets forwardonly, or static, or dynamic etc. So, my question is simple: How can I achieve the best performance. Are there any tools (benchmarks) in order to see graphically the needed time for each operation and choose the best one? And something more: Which ADO is faster, 2.0, 2.1 or 2.5?
Forgot to say that I use VB6 SP3 and I create client programs for retrieving data from the main database. I cannot have entrance to the server with the database (SQL Server), so what I must do is to create programs as fast as they can be! Thank you.

P.S. Hey, Chris, any good site?? ;-)

Michael Vlastos
Automation Engineer
Company SouthGate Hellas SA
Development Department
Athens, Greece

Well, I'm don't know too much about ADO 2.5 other than it's supposed to have a bunch of XML support and a little better data access, but I do know that ADO 2.1 seems to be more stable than ADO 2.0 was. As for best performance, it depends on two things: 1) the speed of your network and 2) are you worried about network overload?

If you don't have to worry about working the LAN too hard then use client side cursors so that once all the data has come back from your query, moving through the recordset is much faster because ADO doesn't have to ask the server for the next row. I always use forward only, read only cursors unless for some reason i will have to be able to move backwards in the recordset. These are the smallest and quickest cursors for ADO to generate.

If you do have to think about network performance, then you're sort of stuck with server-side cursors. But still using read-only and if applicable forward only cursors will help you out.

Disconnected recordsets are nice, but they kill the network because you basically have three copies of the recordset coming across the network. 1 copy for you to manipulate, 1 copy to retain the old values, should you change your mind about an update and a third copy that keeps a sort of pointer to the actual value in the database, so that when you issue your UpdateBatch command ADO can see if anyone else has modified that row while you had the data locally. But once it's there, you have all the advantages of a writable, scrollable cursor on the local machine.

Hope this makes sense, and sort of helps,
John

John Pirkey
MCSD
www.ShallowWaterSystems.com