help with hash match/inner join performance problem??
-
Hello,
Can anyone help me with this poor performing SQL? The explain plan indicates it is doing a Hash Match/Inner Join.
Our developer is having difficulty rwriting the SQL to improve the performance.
Thanks in advance,
Steve
=============================================================================================
Here is the offending SQL statement:
Update T
set T.BUSI_PHAD_ID=S.BUSI_PHAD_ID ,
T.BUSI_PHAD_ADTY_ADDRESS_CODE=S.BUSI_PHAD_ADTY_ADDRESS_CODE ,
T.BUSI_PHAD_IS_SOURCE=S.BUSI_PHAD_IS_SOURCE ,
T.BUSI_PHAD_ADDRESS=S.BUSI_PHAD_ADDRESS ,
T.BUSI_PHAD_POSTAL_CODE=S.BUSI_PHAD_POSTAL_CODE ,
T.BUSI_PHAD_CITY=S.BUSI_PHAD_CITY ,
T.BUSI_PHAD_STATE_PROVINCE=S.BUSI_PHAD_STATE_PROVINCE ,
T.BUSI_PHAD_ADDITIONAL=S.BUSI_PHAD_ADDITIONAL ,
T.BUSI_PHAD_ATTENTION=S.BUSI_PHAD_ATTENTION ,
T.BUSI_PHAD_COUNTRY=S.BUSI_PHAD_COUNTRY ,
T.BUSI_PHAD_COUNTY=S.BUSI_PHAD_COUNTY ,
T.BUSI_PHAD_CREATE_TS=S.BUSI_PHAD_CREATE_TS ,
T.BUSI_PHAD_UPDATE_TS=S.BUSI_PHAD_UPDATE_TS ,
T.BUSI_PHAD_FPTT_CODE=S.BUSI_PHAD_FPTT_CODE ,
T.BUSI_PHAD_POSTAL_VERIFICATION_IND=S.BUSI_PHAD_POSTAL_VERIFICATION_IND
from testdb2.dbo.I$_DW_INDIVIDUALS S, testdb1.dbo.DW_INDIVIDUALS T
where (T.INDI_ID=S.INDI_ID and T.PROD_ID=S.PROD_ID)
and S.IND_UPDATE = 'U'
================================================================================================
Here is the execution plan:
Update T
set T.BUSI_PHAD_ID=S.BUSI_PHAD_ID ,
T.BUSI_PHAD_ADTY_ADDRESS_CODE=S.BUSI_PHAD_ADTY_ADDRESS_CODE ,
T.BUSI_PHAD_IS_SOURCE=S.BUSI_PHAD_IS_SOURCE ,
T.BUSI_PHAD_ADDRESS=S.BUSI_PHAD_ADDRESS ,
T.BUSI_PHAD_POSTAL_CODE=S.BUSI_PHAD_POSTAL 24 1 0 NULL NULL 1 NULL 2741031.3 NULL NULL NULL 752.43048 NULL NULL UPDATE 0 NULL
|--Table Update(OBJECT
[testdb1].[dbo].[DW_INDIVIDUALS]), SET[DW_INDIVIDUALS].[BUSI_PHAD_UPDATE_TS]=.[BUSI_PHAD_UPDATE_TS], [DW_INDIVIDUALS].[BUSI_PHAD_CREATE_TS]=.[BUSI_PHAD_CREATE_TS], [DW_INDIVIDUALS].[BUSI_PHAD_ID]=.[BUSI_PHAD_ID] 24 2 1 Table Update Update OBJECT[testdb1].[dbo].[DW_INDIVIDUALS]), SET[DW_INDIVIDUALS].[BUSI_PHAD_UPDATE_TS]=.[BUSI_PHAD_UPDATE_TS], [DW_INDIVIDUALS].[BUSI_PHAD_CREATE_TS]=.[BUSI_PHAD_CREATE_TS], [DW_INDIVIDUALS].[BUSI_PHAD_ID]=.[BUSI_PHAD_ID], [DW_INDIVIDUALS] NULL 2741031.3 1.0985808E-2 2.7410312 21 752.43048 NULL NULL PLAN_ROW 0 1.0|--Top(ROWCOUNT est 0) 24 4 2 Top Top NULL NULL 2741031.3 0.0 0.27410311 276 749.40436 [Bmk1000],
.[BUSI_PHAD_POSTAL_VERIFICATION_IND],.[BUSI_PHAD_FPTT_CODE],.[BUSI_PHAD_UPDATE_TS],.[BUSI_PHAD_CREATE_TS],.[BUSI_PHAD_COUNTY],.[BUSI_PHAD_COUNTRY],.[BUSI_PHAD_ATTENTION],.[BUSI_PHAD_ADDITIONAL],.[BUSI_PHAD_STAT NULL PLAN_ROW 0 1.0|--Parallelism(Gather Streams) 24 5 4 Parallelism Gather Streams NULL NULL 2741031.3 0.0 68.144493 276 749.13025 [Bmk1000],
.[BUSI_PHAD_POSTAL_VERIFICATION_IND],.[BUSI_PHAD_FPTT_CODE],.[BUSI_PHAD_UPDATE_TS],.[BUSI_PHAD_CREATE_TS],.[BUSI_PHAD_COUNTY],.[BUSI_PHAD_COUNTRY],.[BUSI_PHAD_ATTENTION],.[BUSI_PHAD_ADDITIONAL],.[BUSI_PHAD_STAT NULL PLAN_ROW -1 1.0|--Hash Match(Inner Join, HASH
.[INDI_ID],.[PROD_ID])=([T].[INDI_ID], [T].[PROD_ID]), RESIDUAL.[INDI_ID]=[T].[INDI_ID] AND.[PROD_ID]=[T].[PROD_ID])) 24 6 5 Hash Match Inner Join HASH.[INDI_ID],.[PROD_ID])=([T].[INDI_ID], [T].[PROD_ID]), RESIDUAL.[INDI_ID]=[T].[INDI_ID] AND.[PROD_ID]=[T].[PROD_ID]) NULL 2741031.3 302.30212 241.14835 276 680.98578 [Bmk1000],.[BUSI_PHAD_POSTAL_VERIFICATION_IND],.[BUSI_PHAD_FPTT_CODE],.[BUSI_PHAD_UPDATE_TS],.[BUSI_PHAD_CREATE_TS],.[BUSI_PHAD_COUNTY],.[BUSI_PHAD_COUNTRY],.[BUSI_PHAD_ATTENTION],.[BUSI_PHAD_ADDITIONAL],.[BUSI_PHAD_STAT NULL PLAN_ROW -1 1.0|--Parallelism(Repartition Streams, PARTITION COLUMNS
.[INDI_ID],.[PROD_ID])) 24 7 6 Parallelism Repartition Streams PARTITION COLUMNS.[INDI_ID],.[PROD_ID]) NULL 2812626.0 0.0 77.581039 259 110.95693.[PROD_ID],.[INDI_ID],.[BUSI_PHAD_POSTAL_VERIFICATION_IND],.[BUSI_PHAD_FPTT_CODE],.[BUSI_PHAD_UPDATE_TS],.[BUSI_PHAD_CREATE_TS],.[BUSI_PHAD_COUNTY],.[BUSI_PHAD_COUNTRY],.[BUSI_PHAD_ATTENTION],.[BUSI_PHAD_ADDITIONAL], NULL PLAN_ROW -1 1.0| |--Table Scan(OBJECT
[testdb2].[dbo].[I$_DW_INDIVIDUALS] AS ), WHERE.[IND_UPDATE]='U')) 24 8 7 Table Scan Table Scan OBJECT[testdb2].[dbo].[I$_DW_INDIVIDUALS] AS ), WHERE.[IND_UPDATE]='U').[IND_UPDATE],.[PROD_ID],.[INDI_ID],.[BUSI_PHAD_POSTAL_VERIFICATION_IND],.[BUSI_PHAD_FPTT_CODE],.[BUSI_PHAD_UPDATE_TS],.[BUSI_PHAD_CREATE_TS],.[BUSI_PHAD_COUNTY],.[BUSI_PHAD_COUNTRY],.[BUSI_PHAD_ATTENTION],.[BUSI_ 2812626.0 31.153875 1.5469891 259 32.700863.[IND_UPDATE],.[PROD_ID],.[INDI_ID],.[BUSI_PHAD_POSTAL_VERIFICATION_IND],.[BUSI_PHAD_FPTT_CODE],.[BUSI_PHAD_UPDATE_TS],.[BUSI_PHAD_CREATE_TS],.[BUSI_PHAD_COUNTY],.[BUSI_PHAD_COUNTRY],.[BUSI_PHAD_ATTENTION],.[BUSI_ NULL PLAN_ROW -1 1.0|--Parallelism(Repartition Streams, PARTITION COLUMNS
[T].[INDI_ID], [T].[PROD_ID])) 24 9 6 Parallelism Repartition Streams PARTITION COLUMNS[T].[INDI_ID], [T].[PROD_ID]) NULL 2977129.0 0.0 16.998135 33 26.57836 [Bmk1000], [T].[PROD_ID], [T].[INDI_ID] NULL PLAN_ROW -1 1.0|--Index Scan(OBJECT
[testdb1].[dbo].[DW_INDIVIDUALS].[DW_INDI_PK] AS [T]), ORDERED FORWARD) 24 10 9 Index Scan Index Scan OBJECT[testdb1].[dbo].[DW_INDIVIDUALS].[DW_INDI_PK] AS [T]), ORDERED FORWARD [Bmk1000], [T].[PROD_ID], [T].[INDI_ID] 2977129.0 7.9427638 1.6374602 33 9.580224 [Bmk1000], [T].[PROD_ID], [T].[INDI_ID] NULL PLAN_ROW -1 1.0(9 row(s) affected)
================================================================================================
Here is the DDL for the tables:
CREATE TABLE [dbo].[I$_DW_INDIVIDUALS] (
[INDI_ID] [numeric](10, 0) NULL ,
[PROD_ID] [numeric](10, 0) NULL ,
[BUSI_PHAD_ID] [numeric](10, 0) NULL ,
[BUSI_PHAD_ADTY_ADDRESS_CODE] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BUSI_PHAD_IS_SOURCE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BUSI_PHAD_ADDRESS] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BUSI_PHAD_POSTAL_CODE] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BUSI_PHAD_CITY] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BUSI_PHAD_STATE_PROVINCE] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BUSI_PHAD_ADDITIONAL] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BUSI_PHAD_ATTENTION] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BUSI_PHAD_COUNTRY] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BUSI_PHAD_COUNTY] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BUSI_PHAD_CREATE_TS] [datetime] NULL ,
[BUSI_PHAD_UPDATE_TS] [datetime] NULL ,
[BUSI_PHAD_FPTT_CODE] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BUSI_PHAD_POSTAL_VERIFICATION_IND] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IND_UPDATE] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE UNIQUE INDEX [idx1_DW_INDIVIDUALS] ON [dbo].[I$_DW_INDIVIDUALS]([INDI_ID], [PROD_ID]) ON [PRIMARY]
GO
CREATE INDEX [indi_id_steve_idx1] ON [dbo].[I$_DW_INDIVIDUALS]([INDI_ID]) ON [PRIMARY]
GO
================================================================================
CREATE TABLE [dbo].[DW_INDIVIDUALS] (
[INDI_ID] [numeric](10, 0) NOT NULL ,
[INDI_TYPE] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ENRO_ID] [numeric](10, 0) NOT NULL ,
[PROD_ID] [numeric](10, 0) NOT NULL ,
[CNTR_ID] [numeric](10, 0) NOT NULL ,
[ORGA_ID] [numeric](10, 0) NOT NULL ,
[INDI_FIRST_NAME] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[INDI_LAST_NAME] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[INDI_FIRST_NAME_UPPER] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[INDI_LAST_NAME_UPPER] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[INDI_PREF_DELIV_METHOD] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[INDI_PD_CODE] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[INDI_EMPLOYEE_NUMBER] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[INDI_FIRST_HIRE_DATE] [datetime] NULL ,
[INDI_RPT_REQ_APPR_FLAG] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[INDI_GENDER] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[INDI_SENIORITY] [numeric](2, 0) NULL ,
[INDI_TITLE] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[INDI_ACTIVE_FLG] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[INDI_MOM_MAIDEN_NAME] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[INDI_MARITAL_STATUS] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[INDI_NAME_PREFIX] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[INDI_DOB] [datetime] NULL ,
[INDI_EMAIL] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[INDI_LANGUAGE] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[INDI_MIDDLE_NAME] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[INDI_NAME_SUFFIX] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[INDI_PERSONAL_CODE] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[INDI_PIN] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[INDI_ALT_DELIV_METHOD] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[INDI_DELIVERY_MEDIUM] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[INDI_TERMINATION_DATE] [datetime] NULL ,
[INDI_SSN] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[INDI_SPOKEN_NAME_FILE_PATH] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[INDI_CREATE_TS] [datetime] NULL ,
[INDI_CREATE_USERNAME] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[INDI_RACE_CODE] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[INDI_EDLV_ID] [numeric](28, 0) NULL ,
[INDI_UPDATE_TS] [datetime] NULL ,
[INDI_UPDATE_USERNAME] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[INDI_RACE_SUPPLEMENT] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[INDI_LAST_NAME_KEYPAD] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[INDI_FIRST_NAME_KEYPAD] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[INDI_USERNAME] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[INDI_PASSWORD] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[INDI_UNKNOWN_FLAG] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[INDI_ME_NUMBER] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[INDI_DEA_NUMBER] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PROV_SPECIALTY] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PROV_SUBSPECIALTY] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PHAD_ID] [numeric](10, 0) NULL ,
[PHAD_ADTY_ADDRESS_CODE] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PHAD_IS_SOURCE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PHAD_ADDRESS] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PHAD_POSTAL_CODE] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PHAD_CITY] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PHAD_STATE_PROVINCE] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PHAD_ADDITIONAL] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PHAD_ATTENTION] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PHAD_COUNTRY] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PHAD_COUNTY] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PHAD_INDI_ID] [numeric](10, 0) NULL ,
[PHAD_ORGA_ID] [numeric](10, 0) NULL ,
[PHAD_CREATE_TS] [datetime] NULL ,
[PHAD_CREATE_USERNAME] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PHAD_UPDATE_TS] [datetime] NULL ,
[PHAD_UPDATE_USERNAME] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PHAD_FPTT_CODE] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PHAD_POSTAL_VERIFICATION_IND] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BUSI_PHNU_ID] [numeric](10, 0) NULL ,
[BUSI_PHNU_PT1_CODE] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BUSI_PHNU_IS_SOURCE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BUSI_PHNU_CALLER_ID_FLG] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BUSI_PHNU_PHONE_NUMBER] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BUSI_PHNU_EXTENSION] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BUSI_PHNU_CREATE_TS] [datetime] NULL ,
[BUSI_PHNU_CREATE_USERNAME] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BUSI_PHNU_UPDATE_TS] [datetime] NULL ,
[BUSI_PHNU_UPDATE_USERNAME] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BUSI_PHNU_TIZO_CODE] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BUSI_PHNU_INDI_ID] [numeric](10, 0) NULL ,
[BUSI_PHNU_ORGA_ID] [numeric](10, 0) NULL ,
[BFAX_PHNU_ID] [numeric](10, 0) NULL ,
[BFAX_PHNU_PT1_CODE] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BFAX_PHNU_IS_SOURCE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BFAX_PHNU_CALLER_ID_FLG] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BFAX_PHNU_PHONE_NUMBER] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BFAX_PHNU_EXTENSION] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BFAX_PHNU_CREATE_TS] [datetime] NULL ,
[BFAX_PHNU_CREATE_USERNAME] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BFAX_PHNU_UPDATE_TS] [datetime] NULL ,
[BFAX_PHNU_UPDATE_USERNAME] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BFAX_PHNU_TIZO_CODE] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BFAX_PHNU_INDI_ID] [numeric](10, 0) NULL ,
[BFAX_PHNU_ORGA_ID] [numeric](10, 0) NULL ,
[HOME_PHNU_ID] [numeric](10, 0) NULL ,
[HOME_PHNU_PT1_CODE] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HOME_PHNU_IS_SOURCE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HOME_PHNU_CALLER_ID_FLG] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HOME_PHNU_PHONE_NUMBER] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HOME_PHNU_EXTENSION] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HOME_PHNU_CREATE_TS] [datetime] NULL ,
[HOME_PHNU_CREATE_USERNAME] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HOME_PHNU_UPDATE_TS] [datetime] NULL ,
[HOME_PHNU_UPDATE_USERNAME] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HOME_PHNU_TIZO_CODE] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HOME_PHNU_INDI_ID] [numeric](10, 0) NULL ,
[HOME_PHNU_ORGA_ID] [numeric](10, 0) NULL ,
[DW_CREATE_TS] [datetime] NOT NULL ,
[DW_UPDATE_TS] [datetime] NULL ,
[DW_LOAD_JOB_ID] [numeric](10, 0) NOT NULL ,
[EXTR_LOAD_JOB_ID] [numeric](10, 0) NOT NULL ,
[PROD_CODE] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BUSI_PHAD_ID] [numeric](10, 0) NULL ,
[BUSI_PHAD_ADTY_ADDRESS_CODE] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BUSI_PHAD_IS_SOURCE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BUSI_PHAD_ADDRESS] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BUSI_PHAD_POSTAL_CODE] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BUSI_PHAD_CITY] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BUSI_PHAD_STATE_PROVINCE] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BUSI_PHAD_ADDITIONAL] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BUSI_PHAD_ATTENTION] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BUSI_PHAD_COUNTRY] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BUSI_PHAD_COUNTY] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BUSI_PHAD_INDI_ID] [numeric](10, 0) NULL ,
[BUSI_PHAD_ORGA_ID] [numeric](10, 0) NULL ,
[BUSI_PHAD_CREATE_TS] [datetime] NULL ,
[BUSI_PHAD_CREATE_USERNAME] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BUSI_PHAD_UPDATE_TS] [datetime] NULL ,
[BUSI_PHAD_UPDATE_USERNAME] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BUSI_PHAD_FPTT_CODE] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BUSI_PHAD_POSTAL_VERIFICATION_IND] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VMAI_PHNU_ID] [numeric](10, 0) NULL ,
[VMAI_PHNU_PT1_CODE] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VMAI_PHNU_IS_SOURCE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VMAI_PHNU_CALLER_ID_FLG] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VMAI_PHNU_PHONE_NUMBER] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VMAI_PHNU_EXTENSION] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VMAI_PHNU_CREATE_TS] [datetime] NULL ,
[VMAI_PHNU_CREATE_USERNAME] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VMAI_PHNU_UPDATE_TS] [datetime] NULL ,
[VMAI_PHNU_UPDATE_USERNAME] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VMAI_PHNU_TIZO_CODE] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VMAI_PHNU_INDI_ID] [numeric](10, 0) NULL ,
[VMAI_PHNU_ORGA_ID] [numeric](10, 0) NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[DW_INDIVIDUALS] ADD
CONSTRAINT [DF__DW_INDIVI__DW_CR__0D1BA681] DEFAULT (getdate()) FOR [DW_CREATE_TS],
CONSTRAINT [DF_DW_INDIVIDUALS_DW_UPDATE_TS] DEFAULT (getdate()) FOR [DW_UPDATE_TS],
CONSTRAINT [DF__DW_INDIVI__DW_LO__0F03EEF3] DEFAULT (0) FOR [DW_LOAD_JOB_ID],
CONSTRAINT [DW_INDI_PK] PRIMARY KEY NONCLUSTERED
(
[INDI_ID],
[PROD_ID]
 
ON [PRIMARY]GO
CREATE INDEX [IX_DW_INDIVIDUALS_INDI_ID] ON [dbo].[DW_INDIVIDUALS]([INDI_ID]) ON [PRIMARY]
GO
-
Are those the only indexes on the table(s) ? It appears as if neither has a clustered index, so both tables may have become highly fragmented as a result.
Also, this may not change the query plan, but you should probably use ANSI style joins and remove the "T" alias from the destination columns:
Update T
set BUSI_PHAD_ID=S.BUSI_PHAD_ID ,
BUSI_PHAD_ADTY_ADDRESS_CODE=S.BUSI_PHAD_ADTY_ADDRESS_CODE ,
BUSI_PHAD_IS_SOURCE=S.BUSI_PHAD_IS_SOURCE ,
BUSI_PHAD_ADDRESS=S.BUSI_PHAD_ADDRESS ,
BUSI_PHAD_POSTAL_CODE=S.BUSI_PHAD_POSTAL_CODE ,
BUSI_PHAD_CITY=S.BUSI_PHAD_CITY ,
BUSI_PHAD_STATE_PROVINCE=S.BUSI_PHAD_STATE_PROVINCE ,
BUSI_PHAD_ADDITIONAL=S.BUSI_PHAD_ADDITIONAL ,
BUSI_PHAD_ATTENTION=S.BUSI_PHAD_ATTENTION ,
BUSI_PHAD_COUNTRY=S.BUSI_PHAD_COUNTRY ,
BUSI_PHAD_COUNTY=S.BUSI_PHAD_COUNTY ,
BUSI_PHAD_CREATE_TS=S.BUSI_PHAD_CREATE_TS ,
BUSI_PHAD_UPDATE_TS=S.BUSI_PHAD_UPDATE_TS ,
BUSI_PHAD_FPTT_CODE=S.BUSI_PHAD_FPTT_CODE ,
BUSI_PHAD_POSTAL_VERIFICATION_IND=S.BUSI_PHAD_POSTAL_VERIFICATION_IND
FROM
testdb1.dbo.DW_INDIVIDUALS As T
INNER JOIN
testdb2.dbo.I$_DW_INDIVIDUALS As S,
ON (T.INDI_ID=S.INDI_ID AND
T.PROD_ID=S.PROD_ID)
WHERE S.IND_UPDATE = 'U'
-
Definitely change the query as described by PW - I would go one step further and specify the fully qualified table name in the UPDATE clause - but that's just me...
Also, I would test adding the column IND_UPDATE to the unique index, idx1_DW_INDIVIDUALS, on I$_DW_INDIVIDUALS thereby reducing the chances of a table scan. If possible, you can try converting the unique index to a clustered PK (including the IND_UPDATE column). Changes in the IND_UPDATE column should not affect the 'physical' position of rows in the table and should eliminate the bookmark lookup that is occuring.
Converting the non-clustered PK on DW_INDIVIDUALS to a clustered PK may remove a bookmark lookup too. The caveat with this one is: Only if there are relatively few inserts or the PK can be dropped/rebuilt after large 'refreshes'.
-
Thanks for the suggestions! I will try them this evening and let you know the outcome.
Steve
-
Your index:
CREATE INDEX [indi_id_steve_idx1] ON [dbo].[I$_DW_INDIVIDUALS]([INDI_ID]) ON [PRIMARY]
GO
gives absolutely no additional benefit over the unique index that is already there. I'd make the unique index a unique clustered index.
Then I'd add an NC index just on the IND_UPDATE column. That will enable the WHERE clause to restrict the data before moving on to the join. Since the INDI_ID and PROD_ID columns are now part of the clustered index, they will also be present automatically in the NC index (no need to specify it), and so this NC index will also act as a covering index for the JOIN.
-
Thanks Phillip, R2ro and PW,
I created the clustered PK that R2ro suggested and the optimizer plan improved greatly. The update statement completed in 9 minutes, down from 4.5 hours. Pretty nice huh? I will also attempt Phillips indexing suggestions adn drop the crappy index while I'm at it. If I get any more performance improvement I will let you know.
For now the development team is extremely happy with the improvement.
Thanks again everyone!!
Steve
-
sorry, I'm struggling to read the plan, but are you getting parallelism involved ? Please please don't alter the server settings but you might want to try your query with the maxdop 1 statement. ( assuming it's a multi-proc box )
Hopefully the index improvements will have removed any issues, but if not try it and see.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
I notice that the INDI_ID and PROD_ID are NULL on the first table and NOT NULL on the second table. I have seen that be a problem for using indexs on joins in the past.
JG
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply