ODBC performance over a WAN

How much data are you returning (number of rows and width of row)?
All things being equal, I cannot see ADO or ADO.NET for that matter giving you any major improvement, not even 10%.

A typical scenario is to read a total of about 150 records from about 5 tables (perhaps 30-50 columns per table access).  While processing the result sets, I'll do perhaps 50-100 ad-hoc lookups in a few other tables.

It's not really doing any heavy lifting, and it all goes smooth as snot when the db is local.

My client's position is that ADO recordsets are much more efficient, that ODBC is "a chatty protocol" and that's what's increasing the latency.   The Profiler shows that the statements execute quickly, but the start-to-end is taking forever.  My position is that it's all about the network access latency, but I'd love to have more than an opinion -- i.e., some hard facts.

I'm thinking that there may be some option (such as using client-side cursors) might decrease the number of round trips.  Thoughts?

I agree.  The only way to prove the point is to build a test harness.  I can even use the SQL statements from the profiler so that it is a valid test, then just dump them over an ADO connection (and ignore the responses).

DaveBaldwin,
In this case, the SQL Server is quite busy.  The actual scenario is that the client wants a concurrently-running second instance of my software to be hosted on a remote site.  Its primary (and company policy-mandated) purpose is to act as a fail-over server, but in these tests, it is also running as a peer to the first.  

The performance is dismal on this second (remote) app instance, but the primary app is running at full throttle.  However, the SQL Server box is nowhere close to maxing out the CPU meter or memory available (these guys have hardware that I can only dream about).   It really must be about bandwidth and data transport.

My client mentioned the idea of using stored procs to speed things up, too.  I open a recordset, then use next-record to cycle through it.   I'm not at all clear how a stored proc could make that go faster.  Can you describe a scenario that could make the process faster?

I'll suggest using tracert and and have him re-examine his firewalls (and anything else that stands between the two locations.

Thanks!

Just a word of caution (so that we do not get too confused with terminology) ADO is the old interface typically used by VB. The driver is what counts: ODBC or OLEDB.

The author is not using ADO, but rather C++ and the question is whether they can benefit from changing driver (if that is even an option with MFC) or changing totally to using an interface such as ADO or ADO.NET so that they can use some other driver.  In other words, if as in my view the driver will have little impact, then the interface MFC or ADO or ADO.NET will have zero effect.  All things being equal.

The question of whether Stored Procedures are any faster than say dynamic SQL, really depends on the use.  If for example the dynamic SQL is doing something as trivial as SELECT Col1, Col2, Col3 FROM MyTable WHERE SomeCondition ORDER BY MySort and (as I suspect) returning thousands of rows , then if coded correctly, I cannot see any significant improvement in performance when using Stored Procedures.

So the answer as always is it depends and we cannot assume that using a different interface (for example ADO or ADO.NET) or driver will make any difference.

I cannot over emphasize that the key lies in if it is coded correctly.  So if you return an updateable resultset and proceed to make individual edits to the records only to then return them all the way back to the database over a WAN, then sure you might as well take up knitting.  But Dan has been in this business long enough to know that and I am sure knows better than to make that mistake.

Thanks for clarifying my confusion AC.

Regarding the difference between ODBC or OLEDB over a distance -- I doubt there would be a difference.

I appreciate the help.  It was most useful to at least partially confirm my belief that the ODBC transport (in and of itself) is not the bottleneck.   My client has agreed to do some simulations to (try to) prove their point as I was able to sound convincing in the conference.  

Also, we are working on writing some SPs that may speed up one part of the process.  I estimate we may see maybe a 10% overall speed up from that, so it really doesn't solve the problem, but it shows we are working toward a solution.

BTW... I tried numerous variations of settings on the database and recordset object options, including useCursorLib, snapshot, read-only... but there was no astonishing speedup.

-- Dan