July 8, 2006 at 11:05 am
I have an instance of SQL Server 2000 Standard Edition
I have a table that has several indexes on it. When I want to extract some data from the table (INSERT ... SELECT...) inside a job, the query optimizer does not pick the most efficient index on the souce table. It picks only the primary key (index no 1).
The same query executed in the Query Analyser picks the correct index.
Therefore the Job finishes the statement in 2 minutes to extract 100k rows from an 8M row table while in QA, the same query exeutes in 1.2 seconds because it uses another index than the primary key.
I don't want to use a table hint in the job because that statement is generated dynamicaly, therefore I don't know which index is the best. Anyway the job doed not execute if I specify a hint with an index created with the Database Engin Tuning Advisor.
So, how can I make the query optimizer work from a job as well?
Thanks!
July 10, 2006 at 1:09 am
Hi Gabriel
I'm no expert, but I have had similar problems with differences between queries executed in Query Analyser and in EM, and I eventually resolved them by comparing the connection properties. Things like ansi nulls, concat_null_yields_null and arithabort can affect the optimization and the results of queries.
David
If it ain't broke, don't fix it...
July 11, 2006 at 5:35 am
Gabriel,
David may well be right (I'm not too good on the various connection property differences between clients that might affecte performance either), but if you could post some sample code together with the table and index structures then that would help enormously.
In my experience, the query plan from a job should be the same as from QA.
Phil
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply