Why does it take many minutes for this insert ? - Any siggestions

Question

Why does it take many minutes for this insert ? - Any siggestions

mw_sql_developer

SSCoach

Points: 19445
More actions
October 16, 2015 at 10:18 am

#399200

--WHY DOES THIS TAKES 10 minutes
INSERT INTO tmpHCDAXPx
select AStg.*, d.IDX
from dbo.HCDAXP AStg
inner join tmpMHPCLMDETx d
on
AStg.DXFTYP = d.FILET
and AStg.DXFORM = d.FORMN
and AStg.DXPCOD = d.PCDCD
and AStg.DXPSDT = d.ADMDT
and AStg.DXSSDT = d.SSVDT;
Below is some helpful info on the indexes I have on both tables and the record counts.
Table: tmpHCDAXPx
This table has a primary key
PK_tmoHCDAXPxon the columns-->DXFORM, DXFTYP, DXPCOD, DXPSDT, DXSSDT, IDX
--HCDAXP table has --> PK_HCDAXPclustered, unique, primary key located on PRIMARY DXFORM, DXFTYP, DXPCOD, DXPSDT, DXSSDT
--tmpMHPCLMDETx table has ---> INDX_tmpMHPCLMDETx_FILET_FORMN_PCDCD_ADMDT_SSVDTnonclustered located on PRIMARYFILET, FORMN, PCDCD, ADMDT, SSVDT
--Select count(*) FROM HCDAXP --= 6818569 ( Took 1 minute and 35 seconds for to get the count(*) the first time today, after that it came in 1 sec )
--Select count(*) FROM tmpMHPCLMDETx --= 7283474 ( 3 seconds for this to execute first time, after that just 1 sec )

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

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

I'm guessing that it could be an expensive sort if the index columns are not in the same order. Possible blocking on any of the 3 tables.

Could you post the execution plan?

Is the PK on tmpHCDAXPx clustered?

How long does the following code takes?

DECLARE @AStg_DXFTYP varchar(100),

@AStg_DXFORM varchar(100),

@AStg_DXPCOD varchar(100),

@AStg_DXPSDT varchar(100),

@AStg_DXSSDT varchar(100),

@d_IDX varchar(100)

select @AStg_DXFTYP = AStg.DXFTYP,

@AStg_DXFORM = AStg.DXFORM,

@AStg_DXPCOD = AStg.DXPCOD,

@AStg_DXPSDT = AStg.DXPSDT,

@AStg_DXSSDT = AStg.DXSSDT,

@d_IDX = d.IDX

from dbo.HCDAXP AStg

inner join tmpMHPCLMDETx d on AStg.DXFTYP = d.FILET

and AStg.DXFORM = d.FORMN

and AStg.DXPCOD = d.PCDCD

and AStg.DXPSDT = d.ADMDT

and AStg.DXSSDT = d.SSVDT;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Eric M Russell SSC Guru Points: 125561 More actions · Answer 2

Run while "SET STATISTICS IO ON", so we know how many actual page reads.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

mw_sql_developer SSCoach Points: 19445 More actions · Answer 3

It took 1 MIN and 40 seconds

Table 'HCDAXP'. Scan count 9, logical reads 896088, physical reads 157, read-ahead reads 899182, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'tmpMHPCLMDETx'. Scan count 9, logical reads 245897, physical reads 0, read-ahead reads 165303, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.