Query slow when run through Execute SQL Task.

Question

Query slow when run through Execute SQL Task.

Daniel Forrester-253836

Say Hey Kid

Points: 666
More actions
July 19, 2006 at 8:16 am

#76556

I have a query which runs in about 2 mins in Query Analyser that when executed through a Execute SQL Task never finishes.
Has anyone got any idea why this would happen?
Thanks, Daniel

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

What's the query doing? Any cross-server stuff? Could it be a security issue (is your task running as a local user)?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

John R. Hanson SSC-Addicted Points: 442 More actions · Answer 2

Specifically,

Are you running the SQL service as a domain user, or as system? You may be running as SA when you use Query Analyzer, and something else under the SQL Task. Often times you can reference the owner of objects in the query and it will resolve this timeout. Check that you are running this job on the server, and not on DTS on your workstation...

Hope this helps

Daniel Forrester-253836 Say Hey Kid Points: 666 More actions · Answer 3

Hi,

This problems is getting quite frustrating. I left the SQL to finish and it took 2 hours through DTS yet it only takes 2 mins in query analyser. I broke the SQL script down and found that the following statement to be the offender, it just gets stuck here for ages. As you can see it is pretty simple.

Select Distinct

Date,

Store_No

Into

#Store_Epos_Data

From

Fact_Transaction_Sales_Entry

Where

Year(Date) In (Year(getdate()),Year(getdate())-1)

And IsNumeric(Store_No)=1

I'm executing the DTS package on the server through the designer logged on as myself.

Any ideas?

Thanks, Daniel

Daniel Forrester-253836 Say Hey Kid Points: 666 More actions · Answer 4

I think I know what is happening.

When I looked at the execution plan for the query I noticed that the query optimiser was choosing to utilise an index created on a view based on this table. What I am guessing is that when run through DTS SQL Server decides not to use the index and does a table scan instead on this table which is pretty large and therefore takes ages.

After spending a bit of time with table hints trying to force the query optimiser to use the index on the view I came to the rather obvious conclusion just to use the indexed view instead. Razor sharp me.

I'm still curious as to why the query would execute differently via DTS but happy it now runs!

Cheers, Daniel

Filet SSC Enthusiast Points: 184 More actions · Answer 5

Hi Daniel,

Are you using SQL Server Standard edition ? Per BOL "In SQL Server 2000 Enterprise Edition, the query optimizer will automatically consider the indexed view. To use an indexed view in all other editions, the NOEXPAND hint must be used." If you aren't running DTS in Enterprise edition it probably won't use the view by default

Cheers

Filet