November 9, 2005 at 2:13 pm
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
November 10, 2005 at 2:54 am
can't answer your question directly but if you want to see the real i/o cost of a query always use profiler.
check your query plans, you can do this in profiler, note that set options, as used in connections, alter query plans and may cause recompilation and different plans etc. etc. I have a proc which table scans when called as a proc from ado but index seeks from QA - analysis showed that a different plan was created for each based upon set options - you can see this by looking at the contecnts of the procedure cache.
Hope this provides a few pointers.
You can publish query plans in profiler - it's a bit mind numbing but may help
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
November 10, 2005 at 4:10 am
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.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply