simple Insert - tune

Question

simple Insert - tune

Tara-1044200

SSCoach

Points: 15951
More actions
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

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

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

Bob Griffin SSCertifiable Points: 5982 More actions · Answer 1

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.

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 2

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)

Tara-1044200 SSCoach Points: 15951 More actions · Answer 3

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

LutzM SSC Guru Points: 107049 More actions · Answer 4

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]

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 5

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

deepak.a SSCertifiable Points: 5330 More actions · Answer 6

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

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 7

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

Stefan_G SSCertifiable Points: 6609 More actions · Answer 8

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.