Post reply

simple Insert - tune

  • May 13, 2010 at 6:55 am

    #218893

    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

  • May 13, 2010 at 7:04 am

    #1166832

    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.

  • May 13, 2010 at 7:51 am

    #1166875

    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)
    Intro to Tally Tables and Functions

  • May 13, 2010 at 1:31 pm

    #1167168

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

  • May 13, 2010 at 1:49 pm

    #1167171

    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]

  • May 14, 2010 at 6:14 am

    #1167460

    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;-)

  • May 14, 2010 at 6:52 am

    #1167494

    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

  • May 14, 2010 at 7:30 am

    #1167524

    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;-)

  • May 14, 2010 at 1:08 pm

    #1167739

    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