March 1, 2012 at 12:38 pm
The query below is taking too long to execute. It runs for 1 hour and 14 mins. I checked for the warnings on profiler and got hash warning. I also checked for wait_types and it is CXPACKET to avoit it I used MAXDOP 1 which did not make any difference. I have attached the execution plan.
DTA is not even suggesting any columns to index for this query.
Begin Tran
UPDATE
STAGE_RETURNS_CLS
SET
ITEM_NUMBER = X.SKU,
UPC = X.UPC,
UPC_DESCRIPTION = ISNULL(X.NAME, 'N/A') + '|',
DML_OPCODE = 2,
DML_UPDATE_DATE = GETDATE()
FROM
STAGE_RETURNS_CLS C INNER JOIN
(
SELECT
XX.UPC, XX.SKU, ISNULL(XX.NAME_WEBSITE, XX.NAME) NAME, ISNULL(XX.UPC_CHECK_DIGIT, '') UPC_CHECK_DIGIT
FROM
(SELECT SKU, UPC, MAX(ITEM_ROW_INACTIVE_DATETIME) MAXDT FROM GEN_PRODUCT_XREF GROUP BY SKU, UPC) A INNER JOIN
GEN_PRODUCT_XREF XX ON
A.SKU= XX.SKU AND
A.UPC = XX.UPC AND
A.MAXDT = XX.ITEM_ROW_INACTIVE_DATETIME
) X
ON
(
C.UPC = X.UPC OR
C.UPC = X.UPC + ISNULL(X.UPC_CHECK_DIGIT, '') OR
LEFT(C.UPC, LEN(X.UPC)) = X.UPC OR
RIGHT(C.UPC, LEN(X.UPC)) = X.UPC OR
LEFT(RIGHT(C.UPC, LEN(X.UPC)+1), LEN(X.UPC)) = X.UPC
)
WHERE
RIGHT(C.UPC_DESCRIPTION, 1) <> '|' AND
LEN(X.UPC) > 3
rollback
any suggestions to improve performance of this query.
March 1, 2012 at 12:46 pm
You have two major segments that are not sargable. The subquery join and your final where clause. Both of those will cause a index scan. If you have a lot of data this will be slow like you are finding.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 1, 2012 at 12:46 pm
Table and index definitions please?
No other predicates that you could specify (useful, row-limiting ones)? The two predicates in the where clause are not SARGable because of the functions, so there's little chance this could use any indexes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 1, 2012 at 1:02 pm
Tables:
CREATE TABLE [dbo].[GEN_PRODUCT_XREF](
[ROW_KEY] [int] NOT NULL,
[ITEM_KEY] [int] NOT NULL,
[ITEM_STATUS] [tinyint] NOT NULL,
[ITEM_STATUS_ALLPOINTS] [tinyint] NULL,
[ITEM_CURRENT_FLAG] [bit] NOT NULL,
[UPC] [varchar](25) NULL,
[UPC_CHECK_DIGIT] [varchar](1) NULL,
[SKU] [int] NOT NULL,
[SKU_PARENT] [int] NULL,
[DIVISION_ID] [tinyint] NULL,
[QTY_PHYSICAL] [int] NULL,
[NAME] [varchar](255) NULL,
[NAME_WMS] [varchar](80) NULL,
[NAME_WEBSITE] [varchar](80) NULL,
[ITEM_PACKAGING_CHANGE_DATETIME] [datetime] NULL,
[ITEM_ROW_CHANGE_DATETIME] [datetime] NULL,
[ITEM_ROW_INACTIVE_DATETIME] [datetime] NULL,
[ITEM_DWL_DATETIME] [datetime] NULL,
[WMS_PRODUCT_CREATE_DATETIME] [datetime] NULL,
[WMS_PRODUCT_CHANGE_DATETIME] [datetime] NULL,
[WMS_INVENTORY_CREATE_DATETIME] [datetime] NULL,
[WMS_INVENTORY_CHANGE_DATETIME] [datetime] NULL,
[FIXED_FWD_LOC_1] [varchar](15) NULL,
[FIXED_FWD_LOC_2] [varchar](15) NULL,
[VENDOR_ID] [int] NULL,
[BASE_RETAIL] [float] NULL,
[VENDOR_PRODUCT_ID] [varchar](255) NULL,
[DEPARTMENT_ID] [int] NULL,
[DEPARTMENT_DESC] [varchar](255) NULL,
[CATEGORY_ID] [int] NULL,
[CATEGORY_DESC] [varchar](255) NULL,
[SUBCATEGORY_ID] [int] NULL,
[SUBCATEGORY_DESC] [varchar](255) NULL,
[MFGR_ID] [int] NULL,
[MFGR_NAME] [varchar](255) NULL,
[UPC_A] [char](11) NULL,
[EAN_13] [char](12) NULL,
[CHECK_DIGIT] [tinyint] NULL,
[UPC_TYPE] [varchar](10) NULL,
[PRODUCT_FLAGS] [int] NULL,
[DML_OPCODE] [tinyint] NOT NULL,
[DML_INSERT_DATE] [datetime] NOT NULL,
[DML_UPDATE_DATE] [datetime] NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[STAGE_RETURNS_CLS](
[ROW_KEY] [int] NOT NULL,
[CLS_SCAN_DATE] [nvarchar](50) NULL,
[DS_ORDER_ID] [nvarchar](255) NULL,
[FULL_TRACKING_ID] [nvarchar](255) NULL,
[ITEM_NUMBER] [nvarchar](50) NULL,
[UPC] [nvarchar](50) NULL,
[UPC_DESCRIPTION] [nvarchar](255) NULL,
[QTY_RETURNED] [nvarchar](50) NULL,
[CUSTOMER_RETURN_REASON] [nvarchar](255) NULL,
[PRODUCT_CONDITION_DESCRIPTION] [nvarchar](255) NULL,
[DISPOSITION_CODE] [nvarchar](255) NULL,
[DML_FILENAME] [varchar](50) NULL,
[DML_OPCODE] [tinyint] NULL,
[DML_UPDATE_DATE] [datetime] NULL,
[DML_INSERT_DATE] [datetime] NULL,
[DS_ORDER_ID_CLEAN] [char](14) NULL,
[SITE_ID_CLEAN] [int] NULL,
[ORDER_SHIPPED_DATE] [datetime] NULL,
[DC_BULK_RETURN] [bit] NOT NULL,
[UNKNOWN_SOURCE] [bit] NOT NULL,
[MATCH_FLAGS] [int] NOT NULL,
[FULL_TRACKING_ID_CLEAN] [varchar](255) NULL,
[RETURN_CENTER_KEY] [smallint] NULL,
[ORDER_CREATED_DATE] [datetime] NULL,
[PACKAGE_FULFILLMENT_COMPLETED_DATE] [datetime] NULL
) ON [PRIMARY]
Indexes:
CREATE CLUSTERED INDEX [IX_CLU_STAGE_RETURNS_CLS_DS_ORDER_ID] ON [dbo].[STAGE_RETURNS_CLS]
(
[DS_ORDER_ID] 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) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [IX_CLU_GEN_PRODUCT_XREF_UPC] ON [dbo].[GEN_PRODUCT_XREF]
(
[UPC] 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) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_GEN_PRODUCT_XREF_SKU_UPC_ITEM_ROW_INACTIVE_DATETIME] ON [dbo].[GEN_PRODUCT_XREF]
(
[SKU] ASC,
[UPC] ASC,
[ITEM_ROW_INACTIVE_DATETIME] 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) ON [PRIMARY]
GO[/code]
March 1, 2012 at 1:05 pm
No, I can not use other predicate. I can only see one option of adding those columns to the table
March 1, 2012 at 2:23 pm
If you are that concerned about performance... why not split it into a couple of separate update statements and get rid of your OR conditions?
Jared
CE - Microsoft
March 2, 2012 at 11:57 am
Thanks.....Jared
for pointing out small yet effective point to improve the performance, it improved performance up to 40%
Thanks again
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply