January 28, 2008 at 1:01 pm
The following query has been working for months and the other day it just stopped. I get no error, it just never finishes. It used to take 20 minutes. Nothing has changed that I know of.
The query is designed to insert the new records from the t_DTM_DATA_STAGING into t_DTM_DATA_STAGING2 using the t_DTM_DATA_1 as the outer join.
Average record count for t_DTM_DATA_STAGING is 2 Million
Current record count in t_DTM_DATA_1 - 267 Million
Both tables have clustered indexes made up of the 10 fields in the join below.
Any Ideas??
SET QUOTED_IDENTIFIER ON
INSERT INTO
[DTM].[dbo].[t_DTM_DATA_STAGING2]
([CP]
,[CO]
,[MAJ]
,[MINR]
,[LOCN]
,[DPT]
,[YEAR]
,[PD]
,[WK]
,[TRDT]
,[SYSTEM]
,[AMOUNT]
,[DESCRIPTION]
,[GROUP]
,[VENDOR]
,[INVOICE]
,[IDAT]
,[PO_NUMBER]
,[DDAT]
,[RCV#]
,[RDAT]
,[RSP]
,[EXPLANATION]
,[UPLOAD_DATE]
,[UPLOAD_USER]
,[UPLOAD_NAME]
,[RELEASE_DATE]
,[RELEASE_USER]
,[RELEASE_NAME]
,[TRTM])
SELECT
t_DTM_DATA_STAGING.CP,
t_DTM_DATA_STAGING.CO,
t_DTM_DATA_STAGING.MAJ,
t_DTM_DATA_STAGING.MINR,
t_DTM_DATA_STAGING.LOCN,
t_DTM_DATA_STAGING.DPT,
t_DTM_DATA_STAGING.YEAR,
t_DTM_DATA_STAGING.PD,
t_DTM_DATA_STAGING.WK,
t_DTM_DATA_STAGING.TRDT,
t_DTM_DATA_STAGING.SYSTEM,
t_DTM_DATA_STAGING.AMOUNT,
t_DTM_DATA_STAGING.DESCRIPTION,
t_DTM_DATA_STAGING.[GROUP],
t_DTM_DATA_STAGING.VENDOR,
t_DTM_DATA_STAGING.INVOICE,
t_DTM_DATA_STAGING.IDAT,
t_DTM_DATA_STAGING.PO_NUMBER,
t_DTM_DATA_STAGING.DDAT,
t_DTM_DATA_STAGING.RCV#,
t_DTM_DATA_STAGING.RDAT,
t_DTM_DATA_STAGING.RSP,
t_DTM_DATA_STAGING.EXPLANATION, t_DTM_DATA_STAGING.UPLOAD_DATE, t_DTM_DATA_STAGING.UPLOAD_USER, t_DTM_DATA_STAGING.UPLOAD_NAME,
t_DTM_DATA_STAGING.RELEASE_DATE, t_DTM_DATA_STAGING.RELEASE_USER, t_DTM_DATA_STAGING.RELEASE_NAME,
t_DTM_DATA_STAGING.TRTM
FROM
t_DTM_DATA_STAGING
LEFT OUTER JOIN
t_DTM_DATA AS t_DTM_DATA_1
ON
t_DTM_DATA_STAGING.TRTM = t_DTM_DATA_1.TRTM
AND
t_DTM_DATA_STAGING.TRDT = t_DTM_DATA_1.TRDT
AND
t_DTM_DATA_STAGING.PD = t_DTM_DATA_1.PD
AND
t_DTM_DATA_STAGING.YEAR = t_DTM_DATA_1.YEAR
AND
t_DTM_DATA_STAGING.DPT = t_DTM_DATA_1.DPT
AND
t_DTM_DATA_STAGING.LOCN = t_DTM_DATA_1.LOCN
AND
t_DTM_DATA_STAGING.MINR = t_DTM_DATA_1.MINR
AND
t_DTM_DATA_STAGING.MAJ = t_DTM_DATA_1.MAJ
AND
t_DTM_DATA_STAGING.CO = t_DTM_DATA_1.CO
AND
t_DTM_DATA_STAGING.CP = t_DTM_DATA_1.CP
WHERE
(t_DTM_DATA_1.CP IS NULL)
January 28, 2008 at 1:06 pm
Have run UPDATE STATISTICS on this table recently?
JM
January 28, 2008 at 1:12 pm
I'm going to give that a try tonight.
Thanks
January 28, 2008 at 1:27 pm
Good idea to do it overnight. It could take awhile!
JM
January 29, 2008 at 6:10 am
You're right, I started it at 7:00 PM last night and it's still running at 8:00 AM. (ran the update on all User Databases) It looks like it worked. The Insert Into job ran at 4:30 AM and it completed.
Thanks for your help.
Jerid
January 29, 2008 at 6:42 am
You are welcome.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply