September 19, 2014 at 9:47 am
It's been sometime since I last posted, but I'm not sure how to make this work and usually Fridays are slow in here, so we might have some fun. 😉
Today, a stored procedure took over 3 hours to run when it's supposed to take 17 minutes. Basically, it loads information from 2 flat files into a staging table, deletes duplicates on it and uses merge to upsert the final table. One file had 875,824 rows and the other one had 616,132. The final table (NF_T710_2013) has 313,580,290 rows and the staging table (NF_T710) has 1,196,714 after deleting the duplicates. The staging table is truncated and populated each day.
Here's the DDL:
CREATE TABLE [dbo].[NF_T710](
[T071_ENT] [char](4) NOT NULL,
[T071_CEN_REG] [char](4) NOT NULL,
[T071_ACC] [char](10) NOT NULL,
[T071_NUM_RNWSEQ] [decimal](5, 0) NOT NULL,
[T071_NUM_OPERATION] [decimal](9, 0) NOT NULL,
[T071_CODE] [char](3) NOT NULL,
[T071_AMOUNT] [decimal](15, 2) NOT NULL,
[T071_DAT_ACCT] [char](10) NOT NULL,
[T071_DAT_OPERATION] [char](10) NOT NULL,
[T071_TIM_OPERATION] [char](4) NOT NULL,
[T071_DAT_VALUE] [char](10) NOT NULL,
[T071_FCC] [char](3) NOT NULL,
[T071_CHECK] [decimal](7, 0) NOT NULL,
[T071_INTREF] [char](15) NOT NULL,
[T071_TYP_ACCT] [char](3) NOT NULL,
[T071_OBSERVATIONS] [char](31) NOT NULL,
[T071_AUTBAL] [decimal](15, 2) NOT NULL,
[T071_ENT_ACCT] [char](4) NOT NULL,
[T071_CEN_ACCT] [char](4) NOT NULL,
[T071_COD_PRODUCT] [char](2) NOT NULL,
[T071_COD_SPROD] [char](4) NOT NULL,
[T071_FLG_AUTOMAN] [char](1) NOT NULL,
[T071_FLG_UPDATE] [char](1) NOT NULL,
[T071_FLG_MODIFIED] [char](1) NOT NULL,
[T071_FLG_ANN] [char](1) NOT NULL,
[T071_FLG_CHECKED] [char](1) NOT NULL,
[T071_FLG_CREDEB] [char](1) NOT NULL,
[T071_FLG_FREE2A] [char](1) NOT NULL,
[T071_ENT_UPDATE] [char](4) NOT NULL,
[T071_CEN_UPDATE] [char](4) NOT NULL,
[T071_USERUPD] [char](8) NOT NULL,
[T071_UPDATM] [char](1) NOT NULL,
[T071_NTNMUPD] [char](8) NOT NULL,
[T071_TIMESTAMP] [char](26) NOT NULL,
[T071_AMT_ORIGIN] [decimal](15, 2) NOT NULL,
[T071_OCC] [char](3) NOT NULL,
[T071_AMT_FREE1] [decimal](15, 2) NOT NULL,
[T071_AMT_FREE2] [decimal](15, 2) NOT NULL,
[T071_AMT_FREE3] [decimal](15, 2) NOT NULL,
[T071_FLG_FREE1] [char](3) NOT NULL,
[T071_FLG_FREE2B] [char](1) NOT NULL,
[T071_FLG_FREE3] [char](1) NOT NULL
)
WITH
(
DATA_COMPRESSION = PAGE
)
GO
CREATE CLUSTERED INDEX [cx_NF_T710] ON [dbo].[NF_T710]
(
[T071_ACC] ASC,
[T071_CEN_REG] ASC,
[T071_ENT] ASC,
[T071_DAT_ACCT] ASC,
[T071_NUM_OPERATION] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, DATA_COMPRESSION = PAGE)
GO
ALTER TABLE [dbo].[NF_T710] WITH NOCHECK ADD CONSTRAINT [CK_NF_T710] CHECK (([t071_dat_operation]>='2012-01-01'))
GO
ALTER TABLE [dbo].[NF_T710] CHECK CONSTRAINT [CK_NF_T710]
GO
CREATE TABLE [dbo].[NF_T710_2013](
[T071_DAT_CARGA] [date] NOT NULL,
[T071_ENT] [char](4) NOT NULL,
[T071_CEN_REG] [char](4) NOT NULL,
[T071_ACC] [char](10) NOT NULL,
[T071_NUM_RNWSEQ] [decimal](5, 0) NOT NULL,
[T071_NUM_OPERATION] [decimal](9, 0) NOT NULL,
[T071_CODE] [char](3) NOT NULL,
[T071_AMOUNT] [decimal](15, 2) NOT NULL,
[T071_DAT_ACCT] [char](10) NOT NULL,
[T071_DAT_OPERATION] [char](10) NOT NULL,
[T071_TIM_OPERATION] [char](4) NOT NULL,
[T071_DAT_VALUE] [char](10) NOT NULL,
[T071_FCC] [char](3) NOT NULL,
[T071_CHECK] [decimal](7, 0) NOT NULL,
[T071_INTREF] [char](15) NOT NULL,
[T071_TYP_ACCT] [char](3) NOT NULL,
[T071_OBSERVATIONS] [char](31) NOT NULL,
[T071_AUTBAL] [decimal](15, 2) NOT NULL,
[T071_ENT_ACCT] [char](4) NOT NULL,
[T071_CEN_ACCT] [char](4) NOT NULL,
[T071_COD_PRODUCT] [char](2) NOT NULL,
[T071_COD_SPROD] [char](4) NOT NULL,
[T071_FLG_AUTOMAN] [char](1) NOT NULL,
[T071_FLG_UPDATE] [char](1) NOT NULL,
[T071_FLG_MODIFIED] [char](1) NOT NULL,
[T071_FLG_ANN] [char](1) NOT NULL,
[T071_FLG_CHECKED] [char](1) NOT NULL,
[T071_FLG_CREDEB] [char](1) NOT NULL,
[T071_FLG_FREE2A] [char](1) NOT NULL,
[T071_ENT_UPDATE] [char](4) NOT NULL,
[T071_CEN_UPDATE] [char](4) NOT NULL,
[T071_USERUPD] [char](8) NOT NULL,
[T071_UPDATM] [char](1) NOT NULL,
[T071_NTNMUPD] [char](8) NOT NULL,
[T071_TIMESTAMP] [char](26) NOT NULL,
[T071_AMT_ORIGIN] [decimal](15, 2) NOT NULL,
[T071_OCC] [char](3) NOT NULL,
[T071_AMT_FREE1] [decimal](15, 2) NOT NULL,
[T071_AMT_FREE2] [decimal](15, 2) NOT NULL,
[T071_AMT_FREE3] [decimal](15, 2) NOT NULL,
[T071_FLG_FREE1] [char](3) NOT NULL,
[T071_FLG_FREE2B] [char](1) NOT NULL,
[T071_FLG_FREE3] [char](1) NOT NULL,
PRIMARY KEY CLUSTERED
(
[T071_ACC] ASC,
[T071_CEN_REG] ASC,
[T071_ENT] ASC,
[T071_DAT_ACCT] ASC,
[T071_NUM_OPERATION] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, DATA_COMPRESSION = PAGE)
)
GO
ALTER TABLE [dbo].[NF_T710_2013] WITH NOCHECK ADD CONSTRAINT [CK_NF_T710_2013] CHECK (([t071_dat_operation]>='2012-01-01'))
GO
ALTER TABLE [dbo].[NF_T710_2013] CHECK CONSTRAINT [CK_NF_T710_2013]
GO
And here's an extract from the code:
DECLARE @fecha_proc AS SMALLDATETIME
,@df_proceso AS CHAR(8)
,@BulkInsert VARCHAR(250)
,@Rowcount INT
SET @fecha_proc = dateadd(day, - 1, getdate());
WITH cte710
AS (
SELECT *
,ROW_NUMBER() OVER (
PARTITION BY [T071_ENT]
,[T071_CEN_REG]
,[T071_ACC]
,[T071_NUM_OPERATION] ORDER BY [T071_ACC]
) rn
FROM DWREPDB.dbo.NF_T710
)
DELETE
FROM cte710
WHERE rn > 1;
MERGE DWREPDB.dbo.NF_T710_2013 AS t
USING DWREPDB.dbo.NF_T710 AS s
ON s.[T071_ENT] = t.[T071_ENT]
AND s.[T071_CEN_REG] = t.[T071_CEN_REG]
AND s.[T071_ACC] = t.[T071_ACC]
AND s.[T071_NUM_OPERATION] = t.[T071_NUM_OPERATION]
WHEN MATCHED
THEN
UPDATE
SET T071_DAT_CARGA = CONVERT(CHAR(10), GETDATE() - 1, 121)
WHEN NOT MATCHED BY TARGET
THEN
INSERT (
T071_DAT_CARGA
,T071_ENT
,T071_CEN_REG
,T071_ACC
,T071_NUM_RNWSEQ
,T071_NUM_OPERATION
,T071_CODE
,T071_AMOUNT
,T071_DAT_ACCT
,T071_DAT_OPERATION
,T071_TIM_OPERATION
,T071_DAT_VALUE
,T071_FCC
,T071_CHECK
,T071_INTREF
,T071_TYP_ACCT
,T071_OBSERVATIONS
,T071_AUTBAL
,T071_ENT_ACCT
,T071_CEN_ACCT
,T071_COD_PRODUCT
,T071_COD_SPROD
,T071_FLG_AUTOMAN
,T071_FLG_UPDATE
,T071_FLG_MODIFIED
,T071_FLG_ANN
,T071_FLG_CHECKED
,T071_FLG_CREDEB
,T071_FLG_FREE2A
,T071_ENT_UPDATE
,T071_CEN_UPDATE
,T071_USERUPD
,T071_UPDATM
,T071_NTNMUPD
,T071_TIMESTAMP
,T071_AMT_ORIGIN
,T071_OCC
,T071_AMT_FREE1
,T071_AMT_FREE2
,T071_AMT_FREE3
,T071_FLG_FREE1
,T071_FLG_FREE2B
,T071_FLG_FREE3
)
VALUES (
CONVERT(CHAR(10), GETDATE() - 1, 120)
,T071_ENT
,T071_CEN_REG
,T071_ACC
,T071_NUM_RNWSEQ
,T071_NUM_OPERATION
,T071_CODE
,T071_AMOUNT
,T071_DAT_ACCT
,T071_DAT_OPERATION
,T071_TIM_OPERATION
,T071_DAT_VALUE
,T071_FCC
,T071_CHECK
,T071_INTREF
,T071_TYP_ACCT
,T071_OBSERVATIONS
,T071_AUTBAL
,T071_ENT_ACCT
,T071_CEN_ACCT
,T071_COD_PRODUCT
,T071_COD_SPROD
,T071_FLG_AUTOMAN
,T071_FLG_UPDATE
,T071_FLG_MODIFIED
,T071_FLG_ANN
,T071_FLG_CHECKED
,T071_FLG_CREDEB
,T071_FLG_FREE2A
,T071_ENT_UPDATE
,T071_CEN_UPDATE
,T071_USERUPD
,T071_UPDATM
,T071_NTNMUPD
,T071_TIMESTAMP
,T071_AMT_ORIGIN
,T071_OCC
,T071_AMT_FREE1
,T071_AMT_FREE2
,T071_AMT_FREE3
,T071_FLG_FREE1
,T071_FLG_FREE2B
,T071_FLG_FREE3
);
I'm attaching an estimated execution plan and as soon as I can run the procedure again, I'll post the actual execution plan.
I hope someone can give me some ideas on what can be happening here.
September 19, 2014 at 10:14 am
Quick first questions, what are the server specs, has the behaviour been trending, what is the maxdop, the threshold for parallelism, server version/edition?
😎
September 19, 2014 at 10:21 am
Hi Luis,
Are the row counts typical of this process?
Since the estimated plan shows clustered index scans of both tables, I'd first check to be sure you didn't lose a nonclustered index that would help this query.
I notice that both tables have clustered keys with the same five columns, but your logic for removing duplicates and for joining the source and target in the MERGE is based on the 1st, 2nd, 3rd, and 5th column of those clustered keys. The optimizer intends to perform a hash match join on those four columns followed by a huge sort on all five columns of the clustered key. Does the business logic require omitting the 4th column ([T071_DAT_ACCT]) from the duplicate and join logic? If so, can you reorder the clustered keys to move it to the last column so that the optimizer has the option to perform a merge join instead of the hash match (and thus eliminate that sort since the results would be ordered coming out of the join)? If not, can you either drop that column from the clustered keys or add it to the duplicate and join criteria, again with the goal of getting a merge join to eliminate that sort?
Jason Wolfkill
September 19, 2014 at 10:28 am
I'm not sure if this is enough:
Memory 524278 MB
Processors 80
cost threshold for parallelism5
max degree of parallelism4
Version Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) Jun 28 2012 08:36:30 Copyright (c) Microsoft Corporation Data Center Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
It's Clustered
September 19, 2014 at 10:58 am
The row counts are normal, the normal run should be in under 20 minutes but sometimes it takes really long time to complete.
The column is not needed, but it doesn't affect business logic to include it. I'm not sure if changing the cluster index will change the performance and I'm not sure what would be the best option. T071_DAT_ACCT is the date column and the rest define the PK.
Adding that column resulted on a nested loops join for the merge instead of the merge join in the estimated execution plan. Sadly, I still can't run the process again to get the actual exec plan.
September 19, 2014 at 12:03 pm
Luis Cazares (9/19/2014)
The row counts are normal, the normal run should be in under 20 minutes but sometimes it takes really long time to complete.The column is not needed, but it doesn't affect business logic to include it. I'm not sure if changing the cluster index will change the performance and I'm not sure what would be the best option. T071_DAT_ACCT is the date column and the rest define the PK.
Adding that column resulted on a nested loops join for the merge instead of the merge join in the estimated execution plan. Sadly, I still can't run the process again to get the actual exec plan.
I would try including the T071_DAT_ACCT column in the dedupe logic and in the MERGE's join condition before changing the clustered index. The nested loop is probably still an improvement over the hash match. Is it that you can't run the process because it takes too long to complete or that you can't run it without fouling up the DB for business purposes?
Jason Wolfkill
September 19, 2014 at 12:22 pm
I need to wait for some maintenance tasks to complete (including moving the instance to a not-shared node). After everything completes, I'll give it a try. That was exactly my action plan (test including the column before changing the index).
October 4, 2014 at 4:00 pm
Luis Cazares (9/19/2014)
I need to wait for some maintenance tasks to complete (including moving the instance to a not-shared node). After everything completes, I'll give it a try. That was exactly my action plan (test including the column before changing the index).
Did you have any luck with this?
😎
October 6, 2014 at 2:05 pm
Not sure if I can call it luck. The one in charge of this process decided to remove the MERGE and use a combination of update and insert (or maybe delete and insert). It's no longer a problem, but we didn't find a solution using MERGE.
October 6, 2014 at 2:21 pm
Luis Cazares (10/6/2014)
Not sure if I can call it luck. The one in charge of this process decided to remove the MERGE and use a combination of update and insert (or maybe delete and insert). It's no longer a problem, but we didn't find a solution using MERGE.
I use MERGE substantially and find that one cannot expect it to behave to intelligently, somewhat like a cooker does the boiling/frying part but the chief has to prepare/portion/time things etc.
😎
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply