Avatar of DanRollins
DanRollinsFlag for United States of America asked on

ODBC performance over a WAN

I have an application that uses MFC CDatabase and CRecordset objects (and thus ODBC) to access a SQL Server database.  The performance is fine when the database is local or nearby on the LAN.

When running over a WAN, using the ODBC Administrator to set an IP address as the SQL Server, the performance takes a nasty hit.  For instance, a process that's normally done in about 1 second blows up to an unacceptable 30 seconds.

Most of the work is forward-only, read-only access.  It's reading sets of records and generating formatted text into a local memory buffer.

My questions:  
1) Is this type of ODBC access known to be vastly slower than say, ADO access when used on a WAN?  

2) Are there any settings or options that might improve the performance?  It would need to be a considerable improvement... e.g., a 10% boost won't help at all.

Thanks!
Microsoft SQL ServerC++

Avatar of undefined
Last Comment
DanRollins

8/22/2022 - Mon
Anthony Perkins

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%.
ASKER
DanRollins

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?
ASKER CERTIFIED SOLUTION
Anthony Perkins

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
DanRollins

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.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
DanRollins

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!
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Anthony Perkins

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.
Jim P.

Thanks for clarifying my confusion AC.

Regarding the difference between ODBC or OLEDB over a distance -- I doubt there would be a difference.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
DanRollins

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