Long running query

  • Hi,

    I wonder if anyone could offer any ideas. I need to set up a job which will run a query to insert ercords from on db to another (this is for a test). The source table has 3 1/2 million records.

    The query is going to insert based on

    SELECT * FROM dba.tablea where crdate between

    @lastdateused and getdate

    this is supposed to run every 5 minutes to get all records added (in the real world) but my test data alread exists, so it is taking forever.

    I have a clustered index on the date.

    Any suggestions?

  • Oops

    That should read 32 1/2 million rows

  • quote:


    Oops

    That should read 32 1/2 million rows


    -------------------------

    Gregory Larsen, DBA

    If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

    Gregory A. Larsen, MVP

  • Ooops. Clicked "Post New Reply" before I typed anything. Here is what I was thinking.

    1) Have you looked at the execution plan to determine where you are getting the performance hit (what part of the query).

    2) How may records are going to be inserted based on your query? Hopefully only a small amount

    3) I'm not really up on performance differences between one format of a query, but have you tried this instead of between:

    SELECT * FROM dba.tablea where crdate > @lastdateused and crdate < getdate

    -------------------------

    Gregory Larsen, DBA

    If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

    Gregory A. Larsen, MVP

  • crdate >= @lastdateused and crdate <= getdate

    is the same as BETWEEN, however BETWEEN generally runs fastest.

    If you cannot see from the execution plan the problem then try

    SET SHOWPLAN_TEXT ON

    GO

    YOURSELECTQUERY

    GO

    and post the results here for us to take a look.

    "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

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

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