Performance differences between QA/OSQL/ADO

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Performance differences between QA/OSQL/ADO

Author

Topic

CombinedEffort
Starting Member

2 Posts

Posted - 2005-11-09 : 15:40:42

I have a performance problem with a query and need some advice to resolve it.

Basically, I'm running a query ("select field_x from dbo.my_function('a param')") which returns a table of results.

In 'Query Analyzer'(QA), the query runs in 3 seconds. In OSQL.EXE it's taking around 10 seconds, ditto with ODBC and sqloledb via ADO and ODBC via RDO.

Watching the query run with the SQL Profiler, I can see that when the query runs in QA, it's doing about 700K reads, but when it runs from OSQL or ADO, it's generating 1.6M reads, leading, I assume, to the longer execution time. CPU cycle time is the same pattern. The data is static, and the results very reproducable.

So, my question is : What options / doodads is QA passing behind the scenes to speed things up so? Do I have to put a sniffer on the wire to see what commands are being passed (I assume ODBC Tracing will be useless since neither QA or OSQL use ODBC?). Any clues appreciated.

TIA

Rich

p.s. SQL2K SP4, W2K3

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-11-09 : 20:26:14

osql does use ODBC (that's more or less what the "o" is for), QA may still use the DB-Library basis of isql but I'm pretty sure MS changed all of the tools to use ODBC with the release of SQL Server 2000.

There is overhead with ODBC, but nothing that should cause twice as many reads. Try running your query with isql and see how it performs against the others.

ADO and RDO also have overhead, but again shouldn't be twice as much. I'm thinking there's a sneak cursor going on there and that could be what's causing the extra reads. ODBC sometimes uses internal cursors, but it's just a WAG on my part as to whether that's the cause of its problems.

Add cursor events to your trace (if you haven't already) and see what you get. ODBC tracing can't hurt, just make sure to turn it off after you test.

CombinedEffort
Starting Member

2 Posts

Posted - 2005-11-10 : 05:54:31

Excellent information - I've now resolved the issue.

I checked what SET commands were being issued by OSQL.EXE / ISQL.EXE prior to sending the actual query:

/*slow*/
set quoted_identifier on
set implicit_transactions off
set cursor_close_on_commit off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set language us_english
set dateformat mdy
set datefirst 7
set quoted_identifier off
select count(*) from dbo.fn_get_items_im_working_on('user_id')

/*fast*/
set quoted_identifier off
set implicit_transactions off
set cursor_close_on_commit off
set ansi_warnings off
set ansi_padding off
set ansi_nulls off
set concat_null_yields_null off
set language us_english
set dateformat mdy
set datefirst 7
select count(*) from dbo.fn_get_items_im_working_on('user_id')

After playing around with the differences, it seems setting any of the following resulted in the same speed increase as running through ISQL.EXE

set ansi_warnings off
set ansi_padding off
set concat_null_yields_null off

I prefixed my SELECT statement in ADO with 'set ansi_warnings off' and this is now running at an acceptable speed.

Thanks for your help!

Rich.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources