November 29, 2018 at 2:35 pm
frederico_fonseca - Thursday, November 29, 2018 11:32 AMbefore running more test I would drop index [idx_BTI_include] which you created thinking it would help. even though it is being used, it is the one giving poor estimates on rows - and for 9 rows being returned doing 9 lookups for the data isn't an issue.
After you drop it the query should instead use index idx9_BulkID.I would also drop these 2 indexes [<Name of Missing Index, sysname,>] and [idx_ImportFileDate_include]
I dropped idx_BTI_include and idx_ImportFileDate_include ( there's no index called 'Name of Missing Index' even though it was scripted out during index script generation)
Result: 7 seconds. Exec plan included.
In Query1 the indexes used:
non clustered: (1) BAN (2) BulkID (3) Seriel Number
clustered: cidx_QR_Device_Trade_In_Master on OfferID
I think we agree that Query2 query cost is not the issue.
I don't see the non clustered index on OfferGenerateDate used, and do not understand why the clustered on OfferID is used.
@jonathan-2 AC Roberts @chrism-2 I need to pull from
FROM [#Assurant Manufacturer Received] QR
and not
FROM DistributionCenterFeed.[dbo].[QR_Device_Trade_In_Master_2016onward] QR
else the purpose of creating a temp table is defeated.
you missed the point here
What Jonathan was referring is to the "from " highlighted below below which should indeed be
FROM DistributionCenterFeed.[dbo].[QR_Device_Trade_In_Master_2016onward] QR
as it is the one used to populate the temp table in the first place.
....
INSERT INTO [#Assurant Manufacturer Received]
...
SELECT QR.[Offer_ID],
...
FROM DistributionCenterFeed.[dbo].[QR_Device_Trade_In_Master_2016onward] QR
WHERE QR.BAN = @BAN
UNION
SELECT QR.[Offer_ID],
...
FROM DistributionCenterFeed.[dbo].[QR_Device_Trade_In_Master_2016onward] QR
WHERE QR.BulkID = @BULKID
UNION
SELECT QR.[Offer_ID],
...
FROM DistributionCenterFeed.[dbo].[QR_Device_Trade_In_Master_2016onward] QR
WHERE QR.Serial_Number = @IMEI
UNION
SELECT QR.[Offer_ID],
...
FROM [#Assurant Manufacturer Received] QR
WHERE QR.[Offer_Generate_Date] BETWEEN @FROM_DATE AND @TO_DATE;
-- from this point its the second select
SELECT QR.[Offer_ID]
...
FROM [#Assurant Manufacturer Received] QR
LEFT JOIN ODS.[dbo].[QR_DR_TradeIn_Promo] QP ON QR.Offer_ID = QP.Offer_ID
....
you state the index does not exist - yet it is being used on the plan you sent us. Could it be you sent us the wrong plan?
please issue this command
drop index [<Name of Missing Index, sysname,>] ON [dbo].[QR_Device_Trade_In_Master_2016onward]
also rebuild stats on the index that is misbehaving
update statistics [dbo].[QR_Device_Trade_In_Master_2016onward] [idx9_BulkID] with fullscan
then fix the insert into the temp table as per above
and then run the code to see if idx9_bulkID is used correctly.
November 29, 2018 at 2:56 pm
one more thing. when I ran the query this last time SSMS advised this index, but then wouldn't I need to create a different INCLUDE for each of the other possible parameters (BAN, IMEI, SerielNumber)?
/*
Missing Index Details from BULK_rpt_QR_tune1.sql - prddsscrp001.DistributionCenterFeed (GSM1900\HKresl1 (402))
The Query Processor estimates that implementing the following index could improve the query cost by 51.1203%.
*/
/*
USE [DistributionCenterFeed]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[QR_Device_Trade_In_Master_2016onward] ([BulkID])
INCLUDE ([Offer_ID],[Offer_Generate_Date],[Offer_Update_Date],[Manufacturer],[Model],[Serial_Number],[Application_User_ID],[Application_ID],[device_Evaluation_Date],[Store_ID],[BAN],[Subscriber_No],[Offer_Amount],[RMA_Number],[Offer_Status],[Rejection_Reason],[RMA_Status],[Settlement_Amount],[Eligibility_Status],[Eligibility_Reason],[Adjustment_Reason],[Bill_Status],[Channel_ID],[Credit_Issue_Date],[Assurant_Model],[Assurant_Manufacturer],[Program_ID])
GO
*/
--Quote me
November 29, 2018 at 3:09 pm
polkadot - Thursday, November 29, 2018 2:56 PMone more thing. when I ran the query this last time SSMS advised this index, but then wouldn't I need to create a different INCLUDE for each of the other possible parameters (BAN, IMEI, SerielNumber)?
/*
Missing Index Details from BULK_rpt_QR_tune1.sql - prddsscrp001.DistributionCenterFeed (GSM1900\HKresl1 (402))
The Query Processor estimates that implementing the following index could improve the query cost by 51.1203%.
*//*
USE [DistributionCenterFeed]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[QR_Device_Trade_In_Master_2016onward] ([BulkID])
INCLUDE ([Offer_ID],[Offer_Generate_Date],[Offer_Update_Date],[Manufacturer],[Model],[Serial_Number],[Application_User_ID],[Application_ID],[device_Evaluation_Date],[Store_ID],[BAN],[Subscriber_No],[Offer_Amount],[RMA_Number],[Offer_Status],[Rejection_Reason],[RMA_Status],[Settlement_Amount],[Eligibility_Status],[Eligibility_Reason],[Adjustment_Reason],[Bill_Status],[Channel_ID],[Credit_Issue_Date],[Assurant_Model],[Assurant_Manufacturer],[Program_ID])
GO
*/
index advise is just that - in the majority of cases should not be followed and the correct index should be created - the one I asked you to delete was created from such a index advise.
November 29, 2018 at 3:16 pm
I don't see the non clustered index on OfferGenerateDate used, and do not understand why the clustered on OfferID is used.
SQL has a very strong built-in preference for the clus index.
Also, keep in mind that while you think in terms of matching "rows", SQL "thinks" (analyzes) in terms of matching pages. So typically 9 rows equates to (at least) 9 pages to SQL. Then SQL internally decides about the overhead of 9 random page reads vs whatever number of sequential page reads (which have inherent performance advantages vs random reads). SQL also has to consider whether a sort needs done which a given index might avoid, and so on. There's a lot that goes into choosing an index to use, it's not just a simple row-count thing.
The big advantage of searching on a clus index is that it could immediately reduce the total number of pages to a (relatively) very low number. And if that's accomplished, you don't even really care whether the other args are even sargable or not.
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".
November 29, 2018 at 3:39 pm
@frederico_fonseca , @jonathan-2 AC Roberts and ChrisM@Work
Corrected
(1)
FROM DistributionCenterFeed.[dbo].[QR_Device_Trade_In_Master_2016onward] QR
WHERE QR.[Offer_Generate_Date] >= @FROM_DATE AND QR.[Offer_Generate_Date] <= @TO_DATE;
(2)
I dropped [<Name of Missing Index, sysname,>] ON [dbo].[QR_Device_Trade_In_Master_2016onward]
(3)
rebuilt stats on the index that is misbehaving
update statistics [dbo].[QR_Device_Trade_In_Master_2016onward] [idx9_BulkID] with fullscan
(4)
attached execution plan given after all changes made
maybe should put aside any further optimization on BAN, BulkID, and Seriel Number...great strides EXCEPT when I pass parameters for Start and End Dates, the query runs > 13.22 minutes. Index seeks on non clustered BAN and IMEI but index scans on clustered OfferID. Plan does not use existing non clustered on Date or BulkID.
clean summary of indexes:
USE [DistributionCenterFeed]
GO
CREATE CLUSTERED INDEX [cidx_OfferID] ON [dbo].[QR_Device_Trade_In_Master_2016onward]
(
[Offer_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx10_Serial_Number] ON [dbo].[QR_Device_Trade_In_Master_2016onward]
(
[Serial_Number] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx11_OfferGenerateDate] ON [dbo].[QR_Device_Trade_In_Master_2016onward]
(
[Offer_Generate_Date] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx8_BAN] ON [dbo].[QR_Device_Trade_In_Master_2016onward]
(
[BAN] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
CREATE NONCLUSTERED INDEX [idx9_BulkID] ON [dbo].[QR_Device_Trade_In_Master_2016onward]
(
[BulkID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
query, passing start & end dateUSE [DistributionCenterFeed]
USE [DistributionCenterFeed]
GO
DECLARE
@BULKID varchar(50) = null,--5006,
@BAN varchar(50) = null,--'955932237',
@FROM_DATE date = '2018-09-30 14:27:34.700',
@TO_DATE date = '2018-11-29 14:27:50.260',
@IMEI varchar(100) = null--'353313074247735';
IF OBJECT_ID('tempdb..[#Assurant Manufacturer Received]','U') IS NOT NULL
DROP TABLE [#Assurant Manufacturer Received];
CREATE TABLE [#Assurant Manufacturer Received]
(
[Offer_ID] [varchar](500) NULL,
[Offer_Generate_Date] [datetime] NULL,
[Offer_Update_Date] [datetime] NULL,
[Manufacturer] [varchar](300) NOT NULL,
[Model] [varchar](300) NOT NULL,
[Serial_Number] [varchar](100) NOT NULL,
[Application_User_ID] [varchar](500) NULL,
[Application_ID] [varchar](500) NULL,
[device_Evaluation_Date] [datetime] NULL,
[Store_ID] [varchar](100) NULL,
[BAN] [numeric](9, 0) NULL,
[Subscriber_No] [varchar](20) NULL,
[Offer_Amount] [numeric](8, 4) NULL,
[RMA_Number] [varchar](100) NULL,
[Offer_Status] [varchar](100) NULL,
[Rejection_Reason] [varchar](300) NULL,
[RMA_Status] [varchar](100) NULL,
[Settlement_Amount] [numeric](8, 4) NULL,
[Eligibility_Status] [varchar](100) NULL,
[Eligibility_Reason] [varchar](1000) NULL,
[Adjustment_Reason] [varchar](100),
[Bill_Status] [varchar](1) NULL,
[Channel_ID] [varchar](500) NULL,
[Credit_Issue_Date] datetime,
[Program_ID] varchar(10),
[BulkID] varchar(50),
[Assurant_Manufacturer] [varchar](300) NULL,
[Assurant_Model] [varchar](300) NULL
);
INSERT INTO [#Assurant Manufacturer Received]
(
[Offer_ID],
[Offer_Generate_Date],
[Offer_Update_Date],
[Manufacturer],
[Model],
[Serial_Number],
[Application_User_ID],
[Application_ID],
[device_Evaluation_Date],
[Store_ID],
[BAN],
[Subscriber_No],
[Offer_Amount],
[RMA_Number],
[Offer_Status],
[Rejection_Reason],
[RMA_Status],
[Settlement_Amount],
[Eligibility_Status],
[Eligibility_Reason],
[Adjustment_Reason],
[Bill_Status],
[Channel_ID],
[Credit_Issue_Date],
[Program_ID],
[BulkID],
[Assurant_Manufacturer],
[Assurant_Model]
)
SELECT QR.[Offer_ID],
QR.[Offer_Generate_Date],
QR.[Offer_Update_Date],
QR.[Manufacturer],
QR.[Model],
QR.[Serial_Number],
QR.[Application_User_ID],
QR.[Application_ID],
QR.[device_Evaluation_Date],
QR.[Store_ID],
QR.[BAN],
QR.[Subscriber_No],
QR.[Offer_Amount],
QR.[RMA_Number],
QR.[Offer_Status],
QR.[Rejection_Reason],
QR.[RMA_Status],
QR.[Settlement_Amount],
QR.[Eligibility_Status],
QR.[Eligibility_Reason],
QR.[Adjustment_Reason],
QR.[Bill_Status],
QR.[Channel_ID],
QR.[Credit_Issue_Date],
QR.[Program_ID],
QR.[BulkID],
QR.Assurant_Manufacturer AS [Assurant Manufacturer Received],
QR.Assurant_Model AS [Assurant Model Received]
FROM DistributionCenterFeed.[dbo].[QR_Device_Trade_In_Master_2016onward] QR
WHERE QR.BAN = @BAN
UNION
SELECT QR.[Offer_ID],
QR.[Offer_Generate_Date],
QR.[Offer_Update_Date],
QR.[Manufacturer],
QR.[Model],
QR.[Serial_Number],
QR.[Application_User_ID],
QR.[Application_ID],
QR.[device_Evaluation_Date],
QR.[Store_ID],
QR.[BAN],
QR.[Subscriber_No],
QR.[Offer_Amount],
QR.[RMA_Number],
QR.[Offer_Status],
QR.[Rejection_Reason],
QR.[RMA_Status],
QR.[Settlement_Amount],
QR.[Eligibility_Status],
QR.[Eligibility_Reason],
QR.[Adjustment_Reason],
QR.[Bill_Status],
QR.[Channel_ID],
QR.[Credit_Issue_Date],
QR.[Program_ID],
QR.[BulkID],
QR.Assurant_Manufacturer AS [Assurant Manufacturer Received],
QR.Assurant_Model AS [Assurant Model Received]
FROM DistributionCenterFeed.[dbo].[QR_Device_Trade_In_Master_2016onward] QR
WHERE QR.BulkID = @BULKID
UNION
SELECT QR.[Offer_ID],
QR.[Offer_Generate_Date],
QR.[Offer_Update_Date],
QR.[Manufacturer],
QR.[Model],
QR.[Serial_Number],
QR.[Application_User_ID],
QR.[Application_ID],
QR.[device_Evaluation_Date],
QR.[Store_ID],
QR.[BAN],
QR.[Subscriber_No],
QR.[Offer_Amount],
QR.[RMA_Number],
QR.[Offer_Status],
QR.[Rejection_Reason],
QR.[RMA_Status],
QR.[Settlement_Amount],
QR.[Eligibility_Status],
QR.[Eligibility_Reason],
QR.[Adjustment_Reason],
QR.[Bill_Status],
QR.[Channel_ID],
QR.[Credit_Issue_Date],
QR.[Program_ID],
QR.[BulkID],
QR.Assurant_Manufacturer AS [Assurant Manufacturer Received],
QR.Assurant_Model AS [Assurant Model Received]
FROM DistributionCenterFeed.[dbo].[QR_Device_Trade_In_Master_2016onward] QR
WHERE QR.Serial_Number = @IMEI
UNION
SELECT QR.[Offer_ID],
QR.[Offer_Generate_Date],
QR.[Offer_Update_Date],
QR.[Manufacturer],
QR.[Model],
QR.[Serial_Number],
QR.[Application_User_ID],
QR.[Application_ID],
QR.[device_Evaluation_Date],
QR.[Store_ID],
QR.[BAN],
QR.[Subscriber_No],
QR.[Offer_Amount],
QR.[RMA_Number],
QR.[Offer_Status],
QR.[Rejection_Reason],
QR.[RMA_Status],
QR.[Settlement_Amount],
QR.[Eligibility_Status],
QR.[Eligibility_Reason],
QR.[Adjustment_Reason],
QR.[Bill_Status],
QR.[Channel_ID],
QR.[Credit_Issue_Date],
QR.[Program_ID],
QR.[BulkID],
QR.Assurant_Manufacturer AS [Assurant Manufacturer Received],
QR.Assurant_Model AS [Assurant Model Received]
FROM DistributionCenterFeed.[dbo].[QR_Device_Trade_In_Master_2016onward] QR
WHERE QR.[Offer_Generate_Date] >= @FROM_DATE AND QR.[Offer_Generate_Date] <= @TO_DATE;
SELECT QR.[Offer_ID]
,QR.[Offer_Generate_Date]
,QR.[Offer_Update_Date]
,QR.[Manufacturer]
,QR.[Model]
,QR.[Serial_Number]
,QR.[Application_User_ID]
,QR.[Application_ID]
,QR.[device_Evaluation_Date]
,QR.[Store_ID]
,QR.[BAN]
,QR.[Subscriber_No]
,QR.[Offer_Amount]
,QR.[RMA_Number]
,QR.[Offer_Status]
,QR.[Rejection_Reason]
,QR.[RMA_Status]
,QR.[Settlement_Amount]
,QR.[Eligibility_Status]
,QR.[Eligibility_Reason]
,QR.[Adjustment_Reason]
,QR.[Bill_Status]
,QR.[Channel_ID]
,QR.[Credit_Issue_Date]
,QR.[Program_ID]
,QR.[BulkID]
,QP.MARKET_ASSESSED_AMOUNT as [Fair Market Value]
,QP.PRM_NAME AS Promo
,QR.Assurant_Manufacturer as [Assurant Manufacturer Received]
,QR.Assurant_Model as [Assurant Model Received]
,COALESCE(Q1.Answer_Name,'N/A') AS [FMiP OFF]
,COALESCE(Q6.Answer_Name,'N/A') AS [AntiTheft OFF]
,COALESCE(Q2.Answer_Name,'N/A') AS [Liquid Damage]
,COALESCE(Q3.Answer_Name,'N/A') AS [Promo Accepted]
,COALESCE(Q4.Answer_Name,'N/A') AS [LCD Acceptable]
,COALESCE(Q5.Answer_Name,'N/A') AS [Powers On]
FROM [#Assurant Manufacturer Received] QR
LEFT JOIN ODS.[dbo].[QR_DR_TradeIn_Promo] QP ON QR.Offer_ID = QP.Offer_ID
LEFT JOIN ODS.[dbo].[QR_DR_Transaction_Ques_Answers] Q1 ON QR.Offer_ID = Q1.Offer_ID AND Q1.Question_Name = 'Find My Iphone'--
LEFT JOIN ODS.[dbo].[QR_DR_Transaction_Ques_Answers] Q2 ON QR.Offer_ID = Q2.Offer_ID AND Q2.Question_Name = 'Liquid Damage'
LEFT JOIN ODS.[dbo].[QR_DR_Transaction_Ques_Answers] Q3 ON QR.Offer_ID = Q3.Offer_ID AND Q3.Question_Name = 'ATLANTIS_PROMO'
LEFT JOIN ODS.[dbo].[QR_DR_Transaction_Ques_Answers] Q4 ON QR.Offer_ID = Q4.Offer_ID AND Q4.Question_Name = 'LCD Issue'
LEFT JOIN ODS.[dbo].[QR_DR_Transaction_Ques_Answers] Q5 ON QR.Offer_ID = Q5.Offer_ID AND Q5.Question_Name = 'Powers On'
LEFT JOIN ODS.[dbo].[QR_DR_Transaction_Ques_Answers] Q6 ON QR.Offer_ID = Q6.Offer_ID AND Q6.Question_Name = 'Anti Theft Disabled'
--Quote me
November 29, 2018 at 4:48 pm
polkadot - Thursday, November 29, 2018 3:39 PM@frederico_fonseca , @jonathan-2 AC Roberts and ChrisM@Work
(1)
Corrected
FROM DistributionCenterFeed.[dbo].[QR_Device_Trade_In_Master_2016onward] QR
WHERE QR.[Offer_Generate_Date] >= @FROM_DATE AND QR.[Offer_Generate_Date] <= @TO_DATE;
(2)
I dropped [<Name of Missing Index, sysname,>] ON [dbo].[QR_Device_Trade_In_Master_2016onward]
(3)
rebuilt stats on the index that is misbehaving
update statistics [dbo].[QR_Device_Trade_In_Master_2016onward] [idx9_BulkID] with fullscanmaybe should put aside any further optimization on BAN, BulkID, and Seriel Number...great strides already but
when I pass parameters for Start and End Dates, the query runs > 13.22 minutes.
index seeks on non clustered BAN and IMEI but index scans on clustered OfferID
plan does not use existing non clustered on Date or BulkID.clean summary of indexes:
USE [DistributionCenterFeed]
GOCREATE CLUSTERED INDEX [cidx_OfferID] ON [dbo].[QR_Device_Trade_In_Master_2016onward]
(
[Offer_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx10_Serial_Number] ON [dbo].[QR_Device_Trade_In_Master_2016onward]
(
[Serial_Number] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GOCREATE NONCLUSTERED INDEX [idx11_OfferGenerateDate] ON [dbo].[QR_Device_Trade_In_Master_2016onward]
(
[Offer_Generate_Date] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GOCREATE NONCLUSTERED INDEX [idx8_BAN] ON [dbo].[QR_Device_Trade_In_Master_2016onward]
(
[BAN] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ONGO
CREATE NONCLUSTERED INDEX [idx9_BulkID] ON [dbo].[QR_Device_Trade_In_Master_2016onward]
(
[BulkID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GOquery, passing start & end date
USE [DistributionCenterFeed]
USE [DistributionCenterFeed]
GO
DECLARE
@BULKID varchar(50) = null,--5006,
@BAN varchar(50) = null,--'955932237',
@FROM_DATE date = '2018-09-30 14:27:34.700',
@TO_DATE date = '2018-11-29 14:27:50.260',
@IMEI varchar(100) = null--'353313074247735';
IF OBJECT_ID('tempdb..[#Assurant Manufacturer Received]','U') IS NOT NULL
DROP TABLE [#Assurant Manufacturer Received];CREATE TABLE [#Assurant Manufacturer Received]
(
[Offer_ID] [varchar](500) NULL,
[Offer_Generate_Date] [datetime] NULL,
[Offer_Update_Date] [datetime] NULL,
[Manufacturer] [varchar](300) NOT NULL,
[Model] [varchar](300) NOT NULL,
[Serial_Number] [varchar](100) NOT NULL,
[Application_User_ID] [varchar](500) NULL,
[Application_ID] [varchar](500) NULL,
[device_Evaluation_Date] [datetime] NULL,
[Store_ID] [varchar](100) NULL,
[BAN] [numeric](9, 0) NULL,
[Subscriber_No] [varchar](20) NULL,
[Offer_Amount] [numeric](8, 4) NULL,
[RMA_Number] [varchar](100) NULL,
[Offer_Status] [varchar](100) NULL,
[Rejection_Reason] [varchar](300) NULL,
[RMA_Status] [varchar](100) NULL,
[Settlement_Amount] [numeric](8, 4) NULL,
[Eligibility_Status] [varchar](100) NULL,
[Eligibility_Reason] [varchar](1000) NULL,
[Adjustment_Reason] [varchar](100),
[Bill_Status] [varchar](1) NULL,
[Channel_ID] [varchar](500) NULL,
[Credit_Issue_Date] datetime,
[Program_ID] varchar(10),
[BulkID] varchar(50),
[Assurant_Manufacturer] [varchar](300) NULL,
[Assurant_Model] [varchar](300) NULL
);INSERT INTO [#Assurant Manufacturer Received]
(
[Offer_ID],
[Offer_Generate_Date],
[Offer_Update_Date],
[Manufacturer],
[Model],
[Serial_Number],
[Application_User_ID],
[Application_ID],
[device_Evaluation_Date],
[Store_ID],
[BAN],
[Subscriber_No],
[Offer_Amount],
[RMA_Number],
[Offer_Status],
[Rejection_Reason],
[RMA_Status],
[Settlement_Amount],
[Eligibility_Status],
[Eligibility_Reason],
[Adjustment_Reason],
[Bill_Status],
[Channel_ID],
[Credit_Issue_Date],
[Program_ID],
[BulkID],
[Assurant_Manufacturer],
[Assurant_Model]
)
SELECT QR.[Offer_ID],
QR.[Offer_Generate_Date],
QR.[Offer_Update_Date],
QR.[Manufacturer],
QR.[Model],
QR.[Serial_Number],
QR.[Application_User_ID],
QR.[Application_ID],
QR.[device_Evaluation_Date],
QR.[Store_ID],
QR.[BAN],
QR.[Subscriber_No],
QR.[Offer_Amount],
QR.[RMA_Number],
QR.[Offer_Status],
QR.[Rejection_Reason],
QR.[RMA_Status],
QR.[Settlement_Amount],
QR.[Eligibility_Status],
QR.[Eligibility_Reason],
QR.[Adjustment_Reason],
QR.[Bill_Status],
QR.[Channel_ID],
QR.[Credit_Issue_Date],
QR.[Program_ID],
QR.[BulkID],
QR.Assurant_Manufacturer AS [Assurant Manufacturer Received],
QR.Assurant_Model AS [Assurant Model Received]
FROM DistributionCenterFeed.[dbo].[QR_Device_Trade_In_Master_2016onward] QR
WHERE QR.BAN = @BAN
UNION
SELECT QR.[Offer_ID],
QR.[Offer_Generate_Date],
QR.[Offer_Update_Date],
QR.[Manufacturer],
QR.[Model],
QR.[Serial_Number],
QR.[Application_User_ID],
QR.[Application_ID],
QR.[device_Evaluation_Date],
QR.[Store_ID],
QR.[BAN],
QR.[Subscriber_No],
QR.[Offer_Amount],
QR.[RMA_Number],
QR.[Offer_Status],
QR.[Rejection_Reason],
QR.[RMA_Status],
QR.[Settlement_Amount],
QR.[Eligibility_Status],
QR.[Eligibility_Reason],
QR.[Adjustment_Reason],
QR.[Bill_Status],
QR.[Channel_ID],
QR.[Credit_Issue_Date],
QR.[Program_ID],
QR.[BulkID],
QR.Assurant_Manufacturer AS [Assurant Manufacturer Received],
QR.Assurant_Model AS [Assurant Model Received]
FROM DistributionCenterFeed.[dbo].[QR_Device_Trade_In_Master_2016onward] QR
WHERE QR.BulkID = @BULKID
UNION
SELECT QR.[Offer_ID],
QR.[Offer_Generate_Date],
QR.[Offer_Update_Date],
QR.[Manufacturer],
QR.[Model],
QR.[Serial_Number],
QR.[Application_User_ID],
QR.[Application_ID],
QR.[device_Evaluation_Date],
QR.[Store_ID],
QR.[BAN],
QR.[Subscriber_No],
QR.[Offer_Amount],
QR.[RMA_Number],
QR.[Offer_Status],
QR.[Rejection_Reason],
QR.[RMA_Status],
QR.[Settlement_Amount],
QR.[Eligibility_Status],
QR.[Eligibility_Reason],
QR.[Adjustment_Reason],
QR.[Bill_Status],
QR.[Channel_ID],
QR.[Credit_Issue_Date],
QR.[Program_ID],
QR.[BulkID],
QR.Assurant_Manufacturer AS [Assurant Manufacturer Received],
QR.Assurant_Model AS [Assurant Model Received]
FROM DistributionCenterFeed.[dbo].[QR_Device_Trade_In_Master_2016onward] QR
WHERE QR.Serial_Number = @IMEI
UNION
SELECT QR.[Offer_ID],
QR.[Offer_Generate_Date],
QR.[Offer_Update_Date],
QR.[Manufacturer],
QR.[Model],
QR.[Serial_Number],
QR.[Application_User_ID],
QR.[Application_ID],
QR.[device_Evaluation_Date],
QR.[Store_ID],
QR.[BAN],
QR.[Subscriber_No],
QR.[Offer_Amount],
QR.[RMA_Number],
QR.[Offer_Status],
QR.[Rejection_Reason],
QR.[RMA_Status],
QR.[Settlement_Amount],
QR.[Eligibility_Status],
QR.[Eligibility_Reason],
QR.[Adjustment_Reason],
QR.[Bill_Status],
QR.[Channel_ID],
QR.[Credit_Issue_Date],
QR.[Program_ID],
QR.[BulkID],
QR.Assurant_Manufacturer AS [Assurant Manufacturer Received],
QR.Assurant_Model AS [Assurant Model Received]
FROM DistributionCenterFeed.[dbo].[QR_Device_Trade_In_Master_2016onward] QR
WHERE QR.[Offer_Generate_Date] >= @FROM_DATE AND QR.[Offer_Generate_Date] <= @TO_DATE;SELECT QR.[Offer_ID]
,QR.[Offer_Generate_Date]
,QR.[Offer_Update_Date]
,QR.[Manufacturer]
,QR.[Model]
,QR.[Serial_Number]
,QR.[Application_User_ID]
,QR.[Application_ID]
,QR.[device_Evaluation_Date]
,QR.[Store_ID]
,QR.[BAN]
,QR.[Subscriber_No]
,QR.[Offer_Amount]
,QR.[RMA_Number]
,QR.[Offer_Status]
,QR.[Rejection_Reason]
,QR.[RMA_Status]
,QR.[Settlement_Amount]
,QR.[Eligibility_Status]
,QR.[Eligibility_Reason]
,QR.[Adjustment_Reason]
,QR.[Bill_Status]
,QR.[Channel_ID]
,QR.[Credit_Issue_Date]
,QR.[Program_ID]
,QR.[BulkID]
,QP.MARKET_ASSESSED_AMOUNT as [Fair Market Value]
,QP.PRM_NAME AS Promo
,QR.Assurant_Manufacturer as [Assurant Manufacturer Received]
,QR.Assurant_Model as [Assurant Model Received]
,COALESCE(Q1.Answer_Name,'N/A') AS [FMiP OFF]
,COALESCE(Q6.Answer_Name,'N/A') AS [AntiTheft OFF]
,COALESCE(Q2.Answer_Name,'N/A') AS [Liquid Damage]
,COALESCE(Q3.Answer_Name,'N/A') AS [Promo Accepted]
,COALESCE(Q4.Answer_Name,'N/A') AS [LCD Acceptable]
,COALESCE(Q5.Answer_Name,'N/A') AS [Powers On]
FROM [#Assurant Manufacturer Received] QR
LEFT JOIN ODS.[dbo].[QR_DR_TradeIn_Promo] QP ON QR.Offer_ID = QP.Offer_ID
LEFT JOIN ODS.[dbo].[QR_DR_Transaction_Ques_Answers] Q1 ON QR.Offer_ID = Q1.Offer_ID AND Q1.Question_Name = 'Find My Iphone'--
LEFT JOIN ODS.[dbo].[QR_DR_Transaction_Ques_Answers] Q2 ON QR.Offer_ID = Q2.Offer_ID AND Q2.Question_Name = 'Liquid Damage'
LEFT JOIN ODS.[dbo].[QR_DR_Transaction_Ques_Answers] Q3 ON QR.Offer_ID = Q3.Offer_ID AND Q3.Question_Name = 'ATLANTIS_PROMO'
LEFT JOIN ODS.[dbo].[QR_DR_Transaction_Ques_Answers] Q4 ON QR.Offer_ID = Q4.Offer_ID AND Q4.Question_Name = 'LCD Issue'
LEFT JOIN ODS.[dbo].[QR_DR_Transaction_Ques_Answers] Q5 ON QR.Offer_ID = Q5.Offer_ID AND Q5.Question_Name = 'Powers On'
LEFT JOIN ODS.[dbo].[QR_DR_Transaction_Ques_Answers] Q6 ON QR.Offer_ID = Q6.Offer_ID AND Q6.Question_Name = 'Anti Theft Disabled'
The execution plan is recommending an index:CREATE NONCLUSTERED INDEX [IX_QR_Device_Trade_In_Master_2016onward_Offer_Generate_Date_INC] ON [dbo].[QR_Device_Trade_In_Master_2016onward] ([Offer_Generate_Date])
INCLUDE ([Offer_ID],[Offer_Update_Date],[Manufacturer],[Model],[Serial_Number],
[Application_User_ID],[Application_ID],[device_Evaluation_Date],[Store_ID],
[BAN],[Subscriber_No],[Offer_Amount],[RMA_Number],[Offer_Status],[Rejection_Reason],
[RMA_Status],[Settlement_Amount],[Eligibility_Status],[Eligibility_Reason],
[Adjustment_Reason],[Bill_Status],[Channel_ID],[Credit_Issue_Date],
[Assurant_Model],[Assurant_Manufacturer],[Program_ID],[BulkID])
It will not make much difference to performance, if you are only getting 9 rows back, if you force it use the existing index on [Offer_Generate_Date] and don't create the recommended index
So at the end of the last FROM put: QR.Assurant_Model AS [Assurant Model Received]
FROM DistributionCenterFeed.[dbo].[QR_Device_Trade_In_Master_2016onward] QR WITH (FORCESEEK)
WHERE QR.[Offer_Generate_Date] >= @FROM_DATE AND QR.[Offer_Generate_Date] <= @TO_DA
You got an exception earlier because you were selecting from the wrong table.
November 29, 2018 at 7:45 pm
Yes! @jonathan-2 AC Roberts I was just heading over to add the Force Seek to the UNIONs as one more tweak to my last post, when I see that you commented on that.
I even went ahead and added Force Seek to all the UNIONs as you originally advised a couple posts back, and now an Index Seek is always performed and the queries return in 1 to a few seconds whether I pass BAN, BulkID, IMEI, Date, or combination of these. This has been a huge help and we are done here. This has been the first time I have gotten end to end with an experience tuning both with indexes and query modification, and has been a great learning experience. The ideas and suggestions flying in allowed me to see how people approach this problem and start getting a feel for this.
Thanks everyone.
IF someone could tell me why every Index Seek Non Clustered is paired with a Key Look up (Clustered) as it is the next costliest operation in all scenarios, I would really appreciate. What is Key Look Up (Clustered) even doing?
query and plans attached.
USE [DistributionCenterFeed]
GO
DECLARE
@BULKID varchar(50) = null,--5006,
@BAN varchar(50) = null,--'955932237',
@FROM_DATE date = '2017-08-04 00:00:00.000',
@TO_DATE date = '2017-08-04 00:00:00.000',
@IMEI varchar(100) = '353313074247735';
IF OBJECT_ID('tempdb..[#Assurant Manufacturer Received]','U') IS NOT NULL
DROP TABLE [#Assurant Manufacturer Received];
CREATE TABLE [#Assurant Manufacturer Received]
(
[Offer_ID] [varchar](500) NULL,
[Offer_Generate_Date] [datetime] NULL,
[Offer_Update_Date] [datetime] NULL,
[Manufacturer] [varchar](300) NOT NULL,
[Model] [varchar](300) NOT NULL,
[Serial_Number] [varchar](100) NOT NULL,
[Application_User_ID] [varchar](500) NULL,
[Application_ID] [varchar](500) NULL,
[device_Evaluation_Date] [datetime] NULL,
[Store_ID] [varchar](100) NULL,
[BAN] [numeric](9, 0) NULL,
[Subscriber_No] [varchar](20) NULL,
[Offer_Amount] [numeric](8, 4) NULL,
[RMA_Number] [varchar](100) NULL,
[Offer_Status] [varchar](100) NULL,
[Rejection_Reason] [varchar](300) NULL,
[RMA_Status] [varchar](100) NULL,
[Settlement_Amount] [numeric](8, 4) NULL,
[Eligibility_Status] [varchar](100) NULL,
[Eligibility_Reason] [varchar](1000) NULL,
[Adjustment_Reason] [varchar](100),
[Bill_Status] [varchar](1) NULL,
[Channel_ID] [varchar](500) NULL,
[Credit_Issue_Date] datetime,
[Program_ID] varchar(10),
[BulkID] varchar(50),
[Assurant_Manufacturer] [varchar](300) NULL,
[Assurant_Model] [varchar](300) NULL
);
INSERT INTO [#Assurant Manufacturer Received]
(
[Offer_ID],
[Offer_Generate_Date],
[Offer_Update_Date],
[Manufacturer],
[Model],
[Serial_Number],
[Application_User_ID],
[Application_ID],
[device_Evaluation_Date],
[Store_ID],
[BAN],
[Subscriber_No],
[Offer_Amount],
[RMA_Number],
[Offer_Status],
[Rejection_Reason],
[RMA_Status],
[Settlement_Amount],
[Eligibility_Status],
[Eligibility_Reason],
[Adjustment_Reason],
[Bill_Status],
[Channel_ID],
[Credit_Issue_Date],
[Program_ID],
[BulkID],
[Assurant_Manufacturer],
[Assurant_Model]
)
SELECT QR.[Offer_ID],
QR.[Offer_Generate_Date],
QR.[Offer_Update_Date],
QR.[Manufacturer],
QR.[Model],
QR.[Serial_Number],
QR.[Application_User_ID],
QR.[Application_ID],
QR.[device_Evaluation_Date],
QR.[Store_ID],
QR.[BAN],
QR.[Subscriber_No],
QR.[Offer_Amount],
QR.[RMA_Number],
QR.[Offer_Status],
QR.[Rejection_Reason],
QR.[RMA_Status],
QR.[Settlement_Amount],
QR.[Eligibility_Status],
QR.[Eligibility_Reason],
QR.[Adjustment_Reason],
QR.[Bill_Status],
QR.[Channel_ID],
QR.[Credit_Issue_Date],
QR.[Program_ID],
QR.[BulkID],
QR.Assurant_Manufacturer AS [Assurant Manufacturer Received],
QR.Assurant_Model AS [Assurant Model Received]
FROM DistributionCenterFeed.[dbo].[QR_Device_Trade_In_Master_2016onward] QR WITH (FORCESEEK)
WHERE QR.BAN = @BAN
UNION
SELECT QR.[Offer_ID],
QR.[Offer_Generate_Date],
QR.[Offer_Update_Date],
QR.[Manufacturer],
QR.[Model],
QR.[Serial_Number],
QR.[Application_User_ID],
QR.[Application_ID],
QR.[device_Evaluation_Date],
QR.[Store_ID],
QR.[BAN],
QR.[Subscriber_No],
QR.[Offer_Amount],
QR.[RMA_Number],
QR.[Offer_Status],
QR.[Rejection_Reason],
QR.[RMA_Status],
QR.[Settlement_Amount],
QR.[Eligibility_Status],
QR.[Eligibility_Reason],
QR.[Adjustment_Reason],
QR.[Bill_Status],
QR.[Channel_ID],
QR.[Credit_Issue_Date],
QR.[Program_ID],
QR.[BulkID],
QR.Assurant_Manufacturer AS [Assurant Manufacturer Received],
QR.Assurant_Model AS [Assurant Model Received]
FROM DistributionCenterFeed.[dbo].[QR_Device_Trade_In_Master_2016onward] QR WITH (FORCESEEK)
WHERE QR.BulkID = @BULKID
UNION
SELECT QR.[Offer_ID],
QR.[Offer_Generate_Date],
QR.[Offer_Update_Date],
QR.[Manufacturer],
QR.[Model],
QR.[Serial_Number],
QR.[Application_User_ID],
QR.[Application_ID],
QR.[device_Evaluation_Date],
QR.[Store_ID],
QR.[BAN],
QR.[Subscriber_No],
QR.[Offer_Amount],
QR.[RMA_Number],
QR.[Offer_Status],
QR.[Rejection_Reason],
QR.[RMA_Status],
QR.[Settlement_Amount],
QR.[Eligibility_Status],
QR.[Eligibility_Reason],
QR.[Adjustment_Reason],
QR.[Bill_Status],
QR.[Channel_ID],
QR.[Credit_Issue_Date],
QR.[Program_ID],
QR.[BulkID],
QR.Assurant_Manufacturer AS [Assurant Manufacturer Received],
QR.Assurant_Model AS [Assurant Model Received]
FROM DistributionCenterFeed.[dbo].[QR_Device_Trade_In_Master_2016onward] QR WITH (FORCESEEK)
WHERE QR.Serial_Number = @IMEI
UNION
SELECT QR.[Offer_ID],
QR.[Offer_Generate_Date],
QR.[Offer_Update_Date],
QR.[Manufacturer],
QR.[Model],
QR.[Serial_Number],
QR.[Application_User_ID],
QR.[Application_ID],
QR.[device_Evaluation_Date],
QR.[Store_ID],
QR.[BAN],
QR.[Subscriber_No],
QR.[Offer_Amount],
QR.[RMA_Number],
QR.[Offer_Status],
QR.[Rejection_Reason],
QR.[RMA_Status],
QR.[Settlement_Amount],
QR.[Eligibility_Status],
QR.[Eligibility_Reason],
QR.[Adjustment_Reason],
QR.[Bill_Status],
QR.[Channel_ID],
QR.[Credit_Issue_Date],
QR.[Program_ID],
QR.[BulkID],
QR.Assurant_Manufacturer AS [Assurant Manufacturer Received],
QR.Assurant_Model AS [Assurant Model Received]
FROM DistributionCenterFeed.[dbo].[QR_Device_Trade_In_Master_2016onward] QR WITH (FORCESEEK)
WHERE QR.[Offer_Generate_Date] >= @FROM_DATE AND QR.[Offer_Generate_Date] <= @TO_DATE;
SELECT QR.[Offer_ID]
,QR.[Offer_Generate_Date]
,QR.[Offer_Update_Date]
,QR.[Manufacturer]
,QR.[Model]
,QR.[Serial_Number]
,QR.[Application_User_ID]
,QR.[Application_ID]
,QR.[device_Evaluation_Date]
,QR.[Store_ID]
,QR.[BAN]
,QR.[Subscriber_No]
,QR.[Offer_Amount]
,QR.[RMA_Number]
,QR.[Offer_Status]
,QR.[Rejection_Reason]
,QR.[RMA_Status]
,QR.[Settlement_Amount]
,QR.[Eligibility_Status]
,QR.[Eligibility_Reason]
,QR.[Adjustment_Reason]
,QR.[Bill_Status]
,QR.[Channel_ID]
,QR.[Credit_Issue_Date]
,QR.[Program_ID]
,QR.[BulkID]
,QP.MARKET_ASSESSED_AMOUNT as [Fair Market Value]
,QP.PRM_NAME AS Promo
,QR.Assurant_Manufacturer as [Assurant Manufacturer Received]
,QR.Assurant_Model as [Assurant Model Received]
,COALESCE(Q1.Answer_Name,'N/A') AS [FMiP OFF]
,COALESCE(Q6.Answer_Name,'N/A') AS [AntiTheft OFF]
,COALESCE(Q2.Answer_Name,'N/A') AS [Liquid Damage]
,COALESCE(Q3.Answer_Name,'N/A') AS [Promo Accepted]
,COALESCE(Q4.Answer_Name,'N/A') AS [LCD Acceptable]
,COALESCE(Q5.Answer_Name,'N/A') AS [Powers On]
FROM [#Assurant Manufacturer Received] QR
LEFT JOIN ODS.[dbo].[QR_DR_TradeIn_Promo] QP ON QR.Offer_ID = QP.Offer_ID
LEFT JOIN ODS.[dbo].[QR_DR_Transaction_Ques_Answers] Q1 ON QR.Offer_ID = Q1.Offer_ID AND Q1.Question_Name = 'Find My Iphone'--
LEFT JOIN ODS.[dbo].[QR_DR_Transaction_Ques_Answers] Q2 ON QR.Offer_ID = Q2.Offer_ID AND Q2.Question_Name = 'Liquid Damage'
LEFT JOIN ODS.[dbo].[QR_DR_Transaction_Ques_Answers] Q3 ON QR.Offer_ID = Q3.Offer_ID AND Q3.Question_Name = 'ATLANTIS_PROMO'
LEFT JOIN ODS.[dbo].[QR_DR_Transaction_Ques_Answers] Q4 ON QR.Offer_ID = Q4.Offer_ID AND Q4.Question_Name = 'LCD Issue'
LEFT JOIN ODS.[dbo].[QR_DR_Transaction_Ques_Answers] Q5 ON QR.Offer_ID = Q5.Offer_ID AND Q5.Question_Name = 'Powers On'
LEFT JOIN ODS.[dbo].[QR_DR_Transaction_Ques_Answers] Q6 ON QR.Offer_ID = Q6.Offer_ID AND Q6.Question_Name = 'Anti Theft Disabled'
--Quote me
November 29, 2018 at 7:57 pm
polkadot - Thursday, November 29, 2018 7:45 PMYes! @jonathan-2 AC Roberts I was just heading over to add the Force Seek to the UNIONs as one more tweak to my last post, when I see that you commented on that.
I even went ahead and added Force Seek to all the UNIONs as you originally advised a couple posts back, and now an Index Seek is always performed and the queries return in 1 to a few seconds whether I pass BAN, BulkID, IMEI, Date, or combination of these. This has been a huge help and we are done here.
IF you can tell me why every Index Seek Non Clustered is paired with a Key Look up (Clustered) as it is the next costliest operation in all scenarios, I would really be learning. What is Key Look Up (Clustered) even doing?
If you have columns in the select query that aren't in the index then to get those details it has to go to the table (the clustered index) to get the rest of the data. So it seeks to the correct position in the index, gets the address of the rest of the data on that row then looks up the rest of the data on the clustered index . But you said this query only returns 9 rows out of 23 million rows on the table. So 9 key look ups is next to nothing compared to a clustered index scan (full table scan).
Putting in those hints will always make the query do a seek, but if the query returns a lot of rows (say 1 million) it would be much more more efficient for the query to do a clustered index scan (which it won't do now). So just make sure that this query isn't going to return a lot of rows.
November 29, 2018 at 8:37 pm
Yes, the most rows I expect returned at any one run is 200 (or really radically, 1000), and that should be unusual.
--Quote me
November 30, 2018 at 3:39 am
polkadot - Thursday, November 29, 2018 8:37 PMYes, the most rows I expect returned at any one run is 200 (or really radically, 1000), and that should be unusual.
So you should just stick to having the one column indexes without all the include columns.
Viewing 10 posts - 46 through 54 (of 54 total)
You must be logged in to reply to this topic. Login to reply