simple Insert - tune

  • Here are the 3 steps i am running and final step is taking too long when trying to create a table and insert records from temp table executed before last step.

    select distinct d.EmpID, e.EmpCode,StDate,EnDate

    into #EmployeeDur

    from dbo.EmployeeSrc a

    --(692535 row(s) affected), RunTime = 1.02 min

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

    select distinct EmpID,a.EmpCode,a.InDt

    into Empgig1

    from #EmpList a

    join EmployeeSrc b on (a.EmpCode = b.EmpCode)

    where (a.InDt between b.StDate and b.EnDate)

    go

    --324665 row(s) affected, RunTime = 2.06 min

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

    select distinct

    a.EmpID, a.EmpCode, InDt, b.StDate StartDate, b.EnDate EndDate,

    into dbo.EmployeeList

    from Empgig1 a

    inner join #EmployeeDur b on (a.EmpID = b.EmpID)

    where a.InDt between b.StDate and b.EnDate

    go

    --324665 row(s) affected, RunTime = 11.2 hrs

  • Does dbo.EmployeeList table have indexes? If so, you should disable or drop the index before the insert and rebuild it after the insert has completed.

  • Hi Tara,

    There's simply not enough information in your post to really help. Take a look at the article at the second link in my signature line below. It tells you how to post what we need to help.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • EmployeeList is not existing, i am creating that with in the query using SELECT INTO...

  • Tara,

    did you add any indexes after populating #EmployeeDur and Empgig1?

    If not, I guess the following indexes might help:

    Empgig1: EmpID,InDt include(EmpCode)

    #EmployeeDur: EmpID,StDate,EnDate

    Side note: Are you sure you need the DISTINCT clause on each query? As far as I can see you definitely don't need it in your last query since you select all values from the tables previously generated holding distinct data.



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • your 3rd query is takig so long time. it means the related tables dont contain any indexes.

    temp table must contains indexes here, to make JOIN optimal/fast.

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • For the Query 3

    if the dbo.EmployeeSrc table having Index on EmpID ,Instead of #EmployeeDur Temp table u can join the the

    dbo.EmployeeSrc to Empgig1

  • deepak.a (5/14/2010)


    For the Query 3

    if the dbo.EmployeeSrc table having Index on EmpID ,Instead of #EmployeeDur Temp table u can join the the

    dbo.EmployeeSrc to Empgig1

    i would say clustered index on empid and ono clus on stdate and Endate. and then watch the query on exec plan

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • lmu92 (5/13/2010)


    I guess the following indexes might help:

    Empgig1: EmpID,InDt include(EmpCode)

    #EmployeeDur: EmpID,StDate,EnDate

    I agree with Lutz. The indexes suggested above will probably fix the problem.

Viewing 9 posts - 1 through 8 (of 8 total)

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