Post reply

Query slow when run through Execute SQL Task.

  • 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

  • July 19, 2006 at 9:18 am

    #650143

    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

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • July 25, 2006 at 11:09 am

    #651419

    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

  • July 27, 2006 at 4:19 am

    #651821

    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

  • July 27, 2006 at 9:28 am

    #651913

    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

  • July 28, 2006 at 3:29 am

    #652084

    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

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

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