Execution time is taking longer
-
Hello Friends,
I have a query which is taking too much time for execution. I am trying to fine tune the performance but could not achieve it. I am posting the table structure and sample data in here. Could you please provide me any suggestions? The Left outer join tables contains 150 million records. So I believe this performance issue could be due to huge data and BETWEEN statement is used because of the data and table is stored in such a way.
CREATE TABLE [LKP].[DTC_BI_DM_SOH](
[DateID] [int] NULL,
[ST_ID] [int] NULL,
[IT_ID] [int] NULL
) ON [DATA]GO
INSERT INTO LKP.DTC_BI_DM_SOH
SELECT
5106 ,426, 33
UNION
SELECT 5106, 427, 33
UNION
SELECT 5106, 428, 33
UNION
SELECT 5106, 429, 33
UNION
SELECT 5107, 430, 33
UNION
SELECT 5107, 453, 33
UNION
SELECT 5106, 738, 33
UNION
SELECT 5106, 426, 30
UNION
SELECT 5106, 428, 30
UNION
SELECT 5106, 427 ,29
UNION
SELECT 5106, 450 ,33
UNION
SELECT 5107, 426, 29GO
CREATE TABLE [AM].[IT_ST_Item_Store_PLU](
[IT_ID] [int] NOT NULL,
[ST_ID] [int] NOT NULL,
[IT_ST_PLU] [decimal](29, 2) NULL,
[ValidFrom] [int] NOT NULL,
[ValidUnto] [int] NOT NULL
CONSTRAINT [PK_IT_ST_Item_Store_PLU_999999] PRIMARY KEY CLUSTERED
(
[IT_ID] ASC,
[ST_ID] ASC,
[ValidFrom] ASC,
[ValidUnto] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [DATA]
) ON [DATA]GO
INSERT INTO [AM].[IT_ST_Item_Store_PLU]
SELECT 29, 426, 58.82, 1, 99999
UNION
SELECT 29, 427, 58.82, 1, 3738
UNION
SELECT 29, 427, 248.75, 3739, 99999
UNION
SELECT 30, 453, 58.82, 1, 2500
UNION
SELECT 30, 453, 58.90, 2501, 99999
UNION
SELECT 33, 427, 500.90, 1, 99999
UNION
SELECT 33, 426, 95.20, 1, 99999
UNION
SELECT 33, 428, 83.00, 1, 4150
UNION
SELECT 33, 428, 85.20, 4151, 99999GO
CREATE TABLE [AM].[IT_ST_Item_Store_UnitCostPrice](
[IT_ID] [int] NOT NULL,
[ST_ID] [int] NOT NULL,
[IT_ST_UnitCostPrice] [decimal](29, 2) NULL,
[ValidFrom] [int] NOT NULL,
[ValidUnto] [int] NOT NULL
CONSTRAINT [PK_IT_ST_UnitCostPrice] PRIMARY KEY CLUSTERED
(
[IT_ID] ASC,
[ST_ID] ASC,
[ValidFrom] ASC,
[ValidUnto] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [DATA]
) ON [DATA]GO
INSERT INTO [AM].[IT_ST_Item_Store_UnitCostPrice](
SELECT 29, 426, 58.82, 1, 99999
UNION
SELECT 29, 427, 101.30, 1, 5150
UNION
SELECT 29, 427, 248.75, 5151, 99999
UNION
SELECT 30, 453, 58.82, 1, 2500
UNION
SELECT 30, 453, 58.90, 2501, 99999
UNION
SELECT 33, 427, 500.90, 2788, 99999
UNION
SELECT 33, 426, 95.20, 1, 99999
UNION
SELECT 33, 428, 92.00, 1, 3317
UNION
SELECT 33, 428, 95.78, 3318, 99999GO
SELECT
SOH.[DateID] AS [DateID]
, SOH.[ST_ID] AS [ST_ID]
, SOH.[IT_ID] AS [IT_ID]
, CAST(PLU.[IT_ST_PLU] AS MONEY) AS [PLU]
, CAST(UCP.[IT_ST_UnitCostPrice] AS MONEY) AS [UnitCostPrice]
, CAST(NULL AS MONEY) AS [RRP]
FROM [LKP].[DTC_BI_DM_SOH] SOH-- LKP PLU
LEFT JOIN [AM].[IT_ST_Item_Store_PLU] PLU
ON PLU.IT_ID = SOH.[IT_ID]
AND PLU.ST_ID = SOH.[ST_ID]
AND SOH.[DateID] BETWEEN PLU.[ValidFrom] AND PLU.[ValidUnto]-- LKP UCP
LEFT JOIN AM.IT_ST_Item_Store_UnitCostPrice UCP
ON UCP.IT_ID = SOH.[IT_ID]
AND UCP.ST_ID = SOH.[ST_ID]
AND SOH.[DateID] BETWEEN UCP.[ValidFrom] AND UCP.[ValidUnto]
Thanks,
Charmer -
Posting your Indexes are going to be a great help the the experts here. I can see a Table Scan on DTC_BI_DM_SOH in that picture of a query plan, meaning that one isn't being used. I'd suggest posting the DDL of any indexes on the relevant tables and supplying the Execution plan in a more consumable format, rather than an image: https://www.brentozar.com/pastetheplan/
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
I have attached the execution plan in here. The DTC_BI_DM_SOH table does not have any index. It is a temporary table and all the time truncate and reload happens over this. The amount of records in this table is usually in between 1 to 2 million.
Thanks,
Charmer -
Try forcing a MERGE join, see if that helps significantly. If it does, then go ahead and cluster the SOH table on ( IT_ID, ST_ID, DateID ). Yes, that means the rows will have to be sorted when they loaded, but they'll need to be to do a merge join anyway.
SELECT ...
FROM SOH
LEFT MERGE JOIN ...
LEFT MERGE JOIN ...SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
- ScottPletcher - Wednesday, May 23, 2018 10:04 AM
OK, I will try with Merge. One thing I missed mentioning is that IT_ST_Item_Store_PLU table contains 1 billion records.. Also I'm wondering can colum store index help in here?
Thanks,
Charmer - Charmer - Wednesday, May 23, 2018 10:13 AM
The more rows (not "records" ;)), the more a proper use of merge is likely to help. One of the huge advantages of clustering on "real" keys is the capability of using merge joins.
Columnstore would almost certainly help with that much data. You'd have to determine if you want to partition it (which can be based on only one column, so you might want to create a unique column that corresponds to each unique ( IT_ID, ST_ID, DateID ) combination so that you can partition on it, if IT_ID by itself doesn't reduce the partition size enough. I can give more details on the reasoning behind this if you're interested in following up on columnstore indexes.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
- ScottPletcher - Wednesday, May 23, 2018 10:43 AM
Yes... We are looking for column store index as an option now.. So your suggestion would be really helpful. And also when you say cluster on real keys.. Are you asking to create a cluster index on those keys? Sorry if it was a silly question.
Thanks,
Charmer
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply