ODBC slowdown

[Dan Palmer]

For every day for many years, I've pulled data into Stata by running SQL queries using an ODBC connection to a local database. I don't have any hard data to support this claim, but based on my own experience I have the strong impression that, when using the standard odbc load command, data are pulling into Stata significantly slower in Stata 18 than in my previous version (Stata 16). I'd speculate that it takes Stata 18 as much as 50 percent longer to execute queries than Stata 16. I upgraded computers at the same time as my upgrade to Stata 18, but the specs on the new machine are equal to or better than the old, so Stata seems like the likeliest culprit.

Has anyone else had this experience?

[Daniel Schaefer]

It's not just the machine, it could be the database. Especially if the database has undergone a recent change, like someone has added new tables or a lot of data. It could be a SQL issue.

Can you try to run your odbc command with a -timer- set, then time it again with the version 16: command prefix? I don't have a convenient database to test with myself.

version 16: odbc [...]

[Dan Palmer]

I'm pretty sure it's not a database issue nor an SQL issue since my testing involves running the same query at the same time on two different machines. The old computer has Stata 16.1 and the new computer has Stata 18.0. In terms of component specifications, the new computer is equal to or superior to the old computer in all important respects.

Another reason this is perplexing is that the same side-by-side testing approach - when conducted in PLSQL developer - has the opposite (and expected) results: the newer computer is much faster.

I'm also looking into some possible ODBC driver issues as another possible factor.

Thanks for suggesting the timer command. I'll add that to my testing do-file. I didn't know about that little tool.

[Dan Palmer]

Just to close the loop on this thread:

I involved our IT staff, but no one could find an explanation. We did, however, determine that by raising the value of my "Fetch Buffer Size" ODBC driver setting, I was able to significantly improve query execution speed.