November 26, 2018 at 10:55 pm
From the plan:
There is a huge difference in the estimated and actual number of rows. Have you checked the statistics update date and rows and sampled are same or any skew.
Your query retrieving a lot of column, the CI is not having all columns.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
November 27, 2018 at 1:11 am
Create this index and check out the plan. I'd expect to see a seek for the date and residual predicate for the remaining filter columns, then a key lookup to collect the remaining columns referenced by the query.
CREATE INDEX ix_Stuff ON [dbo].[QR_Device_Trade_In_Master_2016onward] (ImportFileDate) INCLUDE (BAN, BulkID, Serial_Number, Offer_Generate_Date)
I think your WHERE clause is still incorrect, try this:
WHERE (
BAN = @LOC_BAN
OR BulkID = @LOC_BulkId
OR Serial_Number= @LOC_IMEI
OR ([Offer_Generate_Date] >= @LOC_STARTOFFER AND [Offer_Generate_Date] <= @LOC_ENDOFFER)
)
AND ImportFileDate > getdate() - 180
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 27, 2018 at 1:51 am
you didn't supply us with the indexes on the table - as it seems you generated the script through SSMS I would advise you to set your scripting options to always generate the missing bits - by default it will not script indexes, pk's, compression options and other rather important things.
I think the following may improve performance - but does require the correct indexes in place.
It also assumes that the where clause is correctly set - if not you will need to get it right and only then look at what the indexes should be.
for the sql given on the explain plan file the following indexes would probably be the required ones
create nonclustered index BAN on QR_Device_Trade_In_Master_2016onward
(BAN
)
create nonclustered index BulkID on QR_Device_Trade_In_Master_2016onward
(BulkID
)
create nonclustered index Serial_Number on QR_Device_Trade_In_Master_2016onward
(Serial_Number
)
create nonclustered index ImportFileDate_Offer_Generate_Date on QR_Device_Trade_In_Master_2016onward
(ImportFileDate
,Offer_Generate_Date
)
Note that option ANSI_NULLS being set ON or OFF will affect how this (and the original one) query executes.
optionally and if that is indeed the intention each of the where clause should really be
where @variable is not null and column = @variable
I've also used select * on the unions - this was just to make it easier to read here - should not be used, and you should instead list all required columns
;with base
as
(select *
from DistributionCenterFeed.[dbo].[QR_Device_Trade_In_Master_2016onward] QR
where BAN = @Loc_Ban
union all
select *
from DistributionCenterFeed.[dbo].[QR_Device_Trade_In_Master_2016onward] QR
where BulkID = @Loc_BulkId
union all
select *
from DistributionCenterFeed.[dbo].[QR_Device_Trade_In_Master_2016onward] QR
where Serial_Number = @Loc_Imei
union all
select *
from DistributionCenterFeed.[dbo].[QR_Device_Trade_In_Master_2016onward] QR
where ([Offer_Generate_Date] >= @Loc_StartOffer
and [Offer_Generate_Date] <= @Loc_EndOffer)
and ImportFileDate > getdate() - 180
)
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 base 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'
where BAN = @Loc_Ban
or BulkID = @Loc_BulkId
or Serial_Number = @Loc_Imei
or ([Offer_Generate_Date] >= @Loc_StartOffer
and [Offer_Generate_Date] <= @Loc_EndOffer)
and ImportFileDate > getdate() - 180
November 27, 2018 at 5:12 am
Tiny nit. Take away the calculation. Make it before in a local variable. It might lead to different row calculations. You are getting a VERY large disparity in estimated versus actual suggesting that your stats may be out of date. However, it's more likely it's all the OR calculations leading to the optimizer effectively throwing up it's hands. I like the suggestion earlier of using UNION ALL. Otherwise, with all the individual indexes, what you're trying to achieve is index intersection. It's a thing, but it's very rare and hard to get. I have an example of it in my book, but that represents hours and hours of work to try to get the perfect set of indexes to intersect. Chances of doing that in the wild are reduced, pretty radically.
Breaking down the query into a set of UNION ALL with individual WHERE clauses gives the optimizer more choices in satisfying the query. Instead of combining everything in one statement, each individual statement can filter on those discrete indexes best suited to it. Another option to consider is that of the 32 million rows in the table, how many will be eliminated just based on the ImportFileDate column? Maybe load those into a temp table and then filter based on the other columns. One query and one WHERE clause can be the preferred mechanism, but sometimes divide and conquer also works.
Even tinier nit, consistency when writing your code will serve you extremely well. Add the table owner to all columns as you write the query, even though only one table is being referenced here. That will not help performance of the query, but it will help your performance and others down the road as you make the code easier to read. Again, teeny-tiny nit.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
November 27, 2018 at 7:07 am
Declare you variables, and populate them:DECLARE
@BULKID varchar(50) = 5006,
@BAN varchar(50) = null,-- '955932237',
@FROM_DATE date = NULL,
@TO_DATE date = NULL,
@IMEI varchar(100) = NULL
Create a temporary table:IF OBJECT_ID('tempdb..[#Assurant Manufacturer Received]','U') IS NOT NULL BEGIN
PRINT 'DROP TABLE [#Assurant Manufacturer Received]'
DROP TABLE [#Assurant Manufacturer Received]
END
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,
[Bill_Status] [varchar](1) NULL,
[Channel_ID] [varchar](500) NULL,
[Assurant_Manufacturer] [varchar](300) NULL,
[Assurant_Model] [varchar](300) NULL
)
Then select only rows from DistributionCenterFeed.[dbo].[QR_Device_Trade_In_Master_2016onward] into it using unions: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],
[Bill_Status],
[Channel_ID],
[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 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 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 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 [#Assurant Manufacturer Received] QR
WHERE [Offer_Generate_Date] BETWEEN @FROM_DATE AND @TO_DATE
Then create the rest of your query from the temporary table ([#Assurant Manufacturer Received]):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'
November 27, 2018 at 10:10 am
ChrisM@Work - Tuesday, November 27, 2018 1:11 AMCreate this index and check out the plan. I'd expect to see a seek for the date and residual predicate for the remaining filter columns, then a key lookup to collect the remaining columns referenced by the query.CREATE INDEX ix_Stuff ON [dbo].[QR_Device_Trade_In_Master_2016onward] (ImportFileDate) INCLUDE (BAN, BulkID, Serial_Number, Offer_Generate_Date)
I think your WHERE clause is still incorrect, try this:
WHERE (
BAN = @LOC_BAN
OR BulkID = @LOC_BulkId
OR Serial_Number= @LOC_IMEI
OR ([Offer_Generate_Date] >= @LOC_STARTOFFER AND [Offer_Generate_Date] <= @LOC_ENDOFFER)
)
AND ImportFileDate > getdate() - 180
If that's the correct WHERE structure -- specifically that you (nearly) always specify an ImportFileDate range, then you need to cluster the table first on ImportFileDate. Strongly consider adding BulkID or OfferID as key#2 in the clus index, if that combination of columns would always be unique. That allows you to define the clus key as UNIQUE, which would be beneficial for this table.
The best, most complete way to improve query speed is to get the best clus index on the table first. Otherwise, you're going to be constantly having to futz with nonclus indexes and still never getting consistently good query performance.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
November 27, 2018 at 1:46 pm
ScottPletcher - Tuesday, November 27, 2018 10:10 AMChrisM@Work - Tuesday, November 27, 2018 1:11 AMCreate this index and check out the plan. I'd expect to see a seek for the date and residual predicate for the remaining filter columns, then a key lookup to collect the remaining columns referenced by the query.CREATE INDEX ix_Stuff ON [dbo].[QR_Device_Trade_In_Master_2016onward] (ImportFileDate) INCLUDE (BAN, BulkID, Serial_Number, Offer_Generate_Date)
I think your WHERE clause is still incorrect, try this:
WHERE (
BAN = @LOC_BAN
OR BulkID = @LOC_BulkId
OR Serial_Number= @LOC_IMEI
OR ([Offer_Generate_Date] >= @LOC_STARTOFFER AND [Offer_Generate_Date] <= @LOC_ENDOFFER)
)
AND ImportFileDate > getdate() - 180If that's the correct WHERE structure -- specifically that you (nearly) always specify an ImportFileDate range, then you need to cluster the table first on ImportFileDate. Strongly consider adding BulkID or OfferID as key#2 in the clus index, if that combination of columns would always be unique. That allows you to define the clus key as UNIQUE, which would be beneficial for this table.
The best, most complete way to improve query speed is to get the best clus index on the table first. Otherwise, you're going to be constantly having to futz with nonclus indexes and still never getting consistently good query performance.
Where did the additional criteria "AND ImportFileDate > getdate() - 180" come from?
November 27, 2018 at 2:13 pm
Jonathan AC Roberts - Tuesday, November 27, 2018 1:46 PMScottPletcher - Tuesday, November 27, 2018 10:10 AMChrisM@Work - Tuesday, November 27, 2018 1:11 AMCreate this index and check out the plan. I'd expect to see a seek for the date and residual predicate for the remaining filter columns, then a key lookup to collect the remaining columns referenced by the query.CREATE INDEX ix_Stuff ON [dbo].[QR_Device_Trade_In_Master_2016onward] (ImportFileDate) INCLUDE (BAN, BulkID, Serial_Number, Offer_Generate_Date)
I think your WHERE clause is still incorrect, try this:
WHERE (
BAN = @LOC_BAN
OR BulkID = @LOC_BulkId
OR Serial_Number= @LOC_IMEI
OR ([Offer_Generate_Date] >= @LOC_STARTOFFER AND [Offer_Generate_Date] <= @LOC_ENDOFFER)
)
AND ImportFileDate > getdate() - 180If that's the correct WHERE structure -- specifically that you (nearly) always specify an ImportFileDate range, then you need to cluster the table first on ImportFileDate. Strongly consider adding BulkID or OfferID as key#2 in the clus index, if that combination of columns would always be unique. That allows you to define the clus key as UNIQUE, which would be beneficial for this table.
The best, most complete way to improve query speed is to get the best clus index on the table first. Otherwise, you're going to be constantly having to futz with nonclus indexes and still never getting consistently good query performance.
Where did the additional criteria "AND ImportFileDate > getdate() - 180" come from?
its on the explain plan supplied
November 28, 2018 at 6:05 am
polkadot - Monday, November 26, 2018 12:39 PMAnswers to Qs:
Grant Fritchey: (1) I added parenthesis around Date. (2) using parameters. Attached query.
ChrisM: (1) table has 98 columns (2) selectivity of columns will change over time (3) rows returned will be between 5 to 50
Erik Erikson: (1) IMEI is mobile. (2) you'll see from DDL that some columns are quite huge and all but one, Bill_Process_Failure_Reason @ varchar(4000) is justified.
Jonathan AC Roberts: I don't think I will take UNION route because report query actually has a number of JOINS that I have commented out for sake of addressing bottlenecks one step at a timeattached exec plan / inserted DDL, and report query.
DDL
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GOSET ANSI_PADDING ON
GOCREATE TABLE [dbo].[QR_Device_Trade_In_Master](
[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,
[Serial_Number_Type] [varchar](50) NULL,
[Application_User_ID] [varchar](500) NULL,
[Application_ID] [varchar](500) NULL,
[device_Evaluation_Date] [datetime] NULL,
[Store_ID] [varchar](100) NULL,
[Purchase_Device_Sku] [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_Expiration_Date] [datetime] NULL,
[Customer_Email_Address] [varchar](100) NULL,
[Customer_Phone_Number] [varchar](30) NULL,
[Offer_Status] [varchar](100) NULL,
[Rejection_Reason] [varchar](300) NULL,
[RMA_Status] [varchar](100) NULL,
[RMA_Expiration_Date] [datetime] NULL,
[Settlement_Amount] [numeric](8, 4) NULL,
[Network_Operator] [varchar](100) NOT NULL,
[Shipping_Label_URL] [varchar](2000) NULL,
[Eligibility_Status] [varchar](100) NULL,
[Eligibility_Reason] [varchar](1000) NULL,
[Is_Customer_Billed] [varchar](1) NULL,
[Adjustment_Reason] [varchar](2000) NULL,
[Bill_Status] [varchar](1) NULL,
[Bill_Process_Failure_Reason] [varchar](4000) NULL,
[Eligibility_Reason_Code] [varchar](100) NULL,
[Sender_ID] [varchar](500) NULL,
[Channel_ID] [varchar](500) NULL,
[Session_ID] [varchar](500) NULL,
[Work_Flow_ID] [varchar](500) NULL,
[Activity_ID] [varchar](500) NULL,
[Dealer_Code] [varchar](500) NULL,
[Credit_Issue_Date] [datetime] NULL,
[Create_Date] [datetime] NOT NULL,
[Created_By] [varchar](500) NULL,
[Update_Date] [datetime] NULL,
[Updated_By] [varchar](500) NULL,
[Image_Url] [varchar](4000) NULL,
[Sales_Document_Number] [varchar](100) NULL,
[Purchase_Order_Number] [varchar](100) NULL,
[Sap_Channel] [varchar](50) NULL,
[Sap_Return_Type] [varchar](10) NULL,
[Sap_Return_Message] [varchar](1000) NULL,
[Transaction_Type] [varchar](100) NULL,
[ProcessLogID] [int] NOT NULL,
[ImportFileDate] [date] NOT NULL,
[Event_Type] [varchar](100) NULL,
[Retail_Store_Type] [varchar](50) NULL,
[Assurant_Model] [varchar](300) NULL,
[Assurant_Manufacturer] [varchar](300) NULL,
[Cancel_Reason] [varchar](500) NULL,
[Program_ID] [varchar](10) NOT NULL,
[Fulfillment_Ord_Number_Forward] [varchar](30) NULL,
[EIP_Balance] [numeric](8, 4) NULL,
[EIP_Plan_ID] [varchar](50) NULL,
[EIP_Device_ID] [varchar](50) NULL,
[Claim_Auth_Code] [varchar](50) NULL,
[Claim_Req] [varchar](2) NULL,
[Claim_Auth_Date] [date] NULL,
[Claim_Auth_Available] [varchar](2) NULL,
[Deductible_Amount] [numeric](8, 4) NULL,
[Device_Collection_Required] [varchar](2) NULL,
[Claimed_Device_Collected] [varchar](1) NULL,
[Jump_TradeIn_Value] [numeric](8, 4) NULL,
[Claim_Submitted_By] [varchar](100) NULL,
[TOTAL_DAMAGE_AMOUNT] [numeric](10, 4) NULL,
[INSURANCE_DEDUCTABLE_AMOUNT] [numeric](10, 4) NULL,
[DEVICE_SHIPMENT_DATE] [date] NULL,
[DEVICE_RETURN_EXPIRATION_DATE] [date] NULL,
[DEVICE_RETURN_DATE] [date] NULL,
[DEVICE_RETURN_SESSION_ID] [varchar](100) NULL,
[ORIGINAL_RMA_NUMBER] [varchar](100) NULL,
[UPDATED_RMA_EXPIRATION_DATE] [date] NULL,
[Adjustment_Id] [varchar](50) NULL,
[Tracking_Carrier] [varchar](100) NULL,
[Tracking_Number] [varchar](50) NULL,
[Return_Tracking_Number] [varchar](100) NULL,
[Trade_In_Sub_Status] [varchar](50) NULL,
[Trade_In_Status] [varchar](50) NULL,
[Return_Tracking_Carrier] [varchar](100) NULL,
[Bill_Credit_Balance] [numeric](8, 4) NULL,
[Device_Received_Date] [date] NULL,
[Trade_In_Credit_Date] [date] NULL,
[Credited_Down_Payment] [numeric](8, 4) NULL,
[Returned_Date] [date] NULL,
[TRADEIN_INTENT] [varchar](50) NULL,
[ORDER_LINE_ID] [varchar](50) NULL,
[BulkID] [varchar](50) NULL
) ON [PRIMARY]
GOSET ANSI_PADDING
OFF
GO
Report Query
In report query you'll notice I am pulling from table with '_2016onward' which is a copy of the table, containing records from year 2016 onward, so as not to interfere with production table while testing new indexes in production.
Have commented out the JOINS to focus on primary table
DECLARE
@BULKID varchar(50) = 5006,
@BAN varchar(50) = null,-- '955932237',
@FROM_DATE date = NULL,
@TO_DATE date = NULL,
@IMEI varchar(100) = NULL
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 DistributionCenterFeed.[dbo].[QR_Device_Trade_In_Master_2016onward] 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'
WHERE
BAN = @BAN
OR BulkID = @BULKID
OR Serial_Number= @IMEI
OR ([Offer_Generate_Date] >= @FROM_DATE AND [Offer_Generate_Date] <= @TO_DATE)
GO
It's not your fault... the forum software sucks pretty badly and they're supposedly going to fix it in the near future (of course, they've been saying that for 2 years but whatever). Here's a way to help us help you in the future until they do.
The code above is horrible to read because of all the white space between each line. Instead of just doing a copy and paste, do a copy and then paste into NotePad. That get's rid of a lot of junk white space. Then copy from NotePad into this forum within the SQL tags you used.
--Jeff Moden
November 28, 2018 at 7:18 am
frederico_fonseca - Tuesday, November 27, 2018 1:51 AMyou didn't supply us with the indexes on the table - as it seems you generated the script through SSMS I would advise you to set your scripting options to always generate the missing bits - by default it will not script indexes, pk's, compression options and other rather important things.I think the following may improve performance - but does require the correct indexes in place.
It also assumes that the where clause is correctly set - if not you will need to get it right and only then look at what the indexes should be.
for the sql given on the explain plan file the following indexes would probably be the required ones
create nonclustered index BAN on QR_Device_Trade_In_Master_2016onward
(BAN
)create nonclustered index BulkID on QR_Device_Trade_In_Master_2016onward
(BulkID
)create nonclustered index Serial_Number on QR_Device_Trade_In_Master_2016onward
(Serial_Number
)create nonclustered index ImportFileDate_Offer_Generate_Date on QR_Device_Trade_In_Master_2016onward
(ImportFileDate
,Offer_Generate_Date
)Note that option ANSI_NULLS being set ON or OFF will affect how this (and the original one) query executes.
optionally and if that is indeed the intention each of the where clause should really be
where @variable is not null and column = @variableI've also used select * on the unions - this was just to make it easier to read here - should not be used, and you should instead list all required columns
;with base
as
(select *
from DistributionCenterFeed.[dbo].[QR_Device_Trade_In_Master_2016onward] QR
where BAN = @Loc_Banunion all
select *
from DistributionCenterFeed.[dbo].[QR_Device_Trade_In_Master_2016onward] QR
where BulkID = @Loc_BulkIdunion all
select *
from DistributionCenterFeed.[dbo].[QR_Device_Trade_In_Master_2016onward] QR
where Serial_Number = @Loc_Imeiunion all
select *
from DistributionCenterFeed.[dbo].[QR_Device_Trade_In_Master_2016onward] QR
where ([Offer_Generate_Date] >= @Loc_StartOffer
and [Offer_Generate_Date] <= @Loc_EndOffer)
and ImportFileDate > getdate() - 180
)
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 base 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'
where BAN = @Loc_Ban
or BulkID = @Loc_BulkId
or Serial_Number = @Loc_Imei
or ([Offer_Generate_Date] >= @Loc_StartOffer
and [Offer_Generate_Date] <= @Loc_EndOffer)
and ImportFileDate > getdate() - 180
The OP would need to use UNION, they may get duplicate rows if they use UNION ALL .
November 28, 2018 at 7:27 am
DECLARE
@BULKID varchar(50) = 5006,
@BAN varchar(50) = null,-- '955932237',
@FROM_DATE date = NULL,
@TO_DATE date = NULL,
@IMEI varchar(100) = NULLIF OBJECT_ID('tempdb..[#Assurant Manufacturer Received]','U') IS NOT NULL BEGIN
PRINT 'DROP TABLE [#Assurant Manufacturer Received]'
DROP TABLE [#Assurant Manufacturer Received]
END
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,
[Bill_Status] [varchar](1) NULL,
[Channel_ID] [varchar](500) NULL,
[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],
[Bill_Status],
[Channel_ID],
[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 ImportFileDate > getdate() - 180
AND 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 ImportFileDate > getdate() - 180
AND 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 ImportFileDate > getdate() - 180
AND 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 [#Assurant Manufacturer Received] QR
WHERE ImportFileDate > getdate() - 180
AND [Offer_Generate_Date] BETWEEN @FROM_DATE AND @TO_DATESELECT 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'November 28, 2018 at 8:03 am
This could offer a perf lift:
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(qn.Q1_Answer_Name,'N/A') AS [FMiP OFF]
,COALESCE(qn.Q6_Answer_Name,'N/A') AS [AntiTheft OFF]
,COALESCE(qn.Q2_Answer_Name,'N/A') AS [Liquid Damage]
,COALESCE(qn.Q3_Answer_Name,'N/A') AS [Promo Accepted]
,COALESCE(qn.Q4_Answer_Name,'N/A') AS [LCD Acceptable]
,COALESCE(qn.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
OUTER APPLY (
SELECT
Q1_Answer_Name = MAX(CASE WHEN Q1.Question_Name = 'Find My Iphone' THEN Q1.Answer_Name END),
Q2_Answer_Name = MAX(CASE WHEN Q1.Question_Name = 'Liquid Damage' THEN Q1.Answer_Name END),
Q3_Answer_Name = MAX(CASE WHEN Q1.Question_Name = 'ATLANTIS_PROMO' THEN Q1.Answer_Name END),
Q4_Answer_Name = MAX(CASE WHEN Q1.Question_Name = 'LCD Issue' THEN Q1.Answer_Name END),
Q5_Answer_Name = MAX(CASE WHEN Q1.Question_Name = 'Powers On' THEN Q1.Answer_Name END),
Q6_Answer_Name = MAX(CASE WHEN Q1.Question_Name = 'Anti Theft Disabled' THEN Q1.Answer_Name END)
FROM ODS.[dbo].[QR_DR_Transaction_Ques_Answers] Q1
WHERE QR.Offer_ID = Q1.Offer_ID
AND Q1.Question_Name IN ('Find My Iphone', 'Liquid Damage', 'ATLANTIS_PROMO', 'LCD Issue', 'Powers On', 'Anti Theft Disabled')
) qn
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 28, 2018 at 10:47 am
Has the OP stated that the correct where clause isWHERE (
BAN = @LOC_BAN
OR BulkID = @LOC_BulkId
OR Serial_Number= @LOC_IMEI
OR ([Offer_Generate_Date] >= @LOC_STARTOFFER AND [Offer_Generate_Date] <= @LOC_ENDOFFER)
)
AND ImportFileDate > getdate() - 180
and not his original one? e.g. ImportFileDate applying to ALL other filters, not just the Offer_Generate_Date
It does make a significant different but I have not seen the OP agree with the above.
November 29, 2018 at 2:03 am
Very exciting development. The specific UNION solution Jonathan AC Roberts supplied is currently (it's midnight) is showing a lot of promise, between lightening fast. 00:00:00 ms and 9 sec, when I pass BulkID or BAN. (Not quite fast enough when I supply IMEI / 19 sec or dates /16 sec but a huge improvement)
Interestingly & Suprisingly, frederico-fonseca's application of UNIONs with cte is very slow (yes I specified column names instead of SELECT *) yet I would have expected it to have similar performance!
** Attached are execution plans for when I passed BulkID value**
(1) During temp table INSERTs a covering Non Clustered index, that I once created but up til now was getting ignored, called idx_BTI_include is getting used PLUS the Clustered Index on QueryID (don't understand why) PLUS BAN NonClustered PLUS Seriel_Number NonClustered .. but the BulkID NonClustered is not used, even though it is the BulkID parameter for which I supplied value. Are index hints advisable here in each UNION query?
(2) During final query execution, Clustered Indexes from both tables in the JOIN on OfferID are called to action and this makes sense.
--Here's the Jonathan AC Roberts query that I'm excited about.
BTW, ImportFileDate > getdate() - 180 is something I removed within 10 min of posting report query / sorry for confusion. I am considering using only if I have to / but not preferred)
USE [DistributionCenterFeed]
GO
DECLARE
@BULKID varchar(50) = 5006,
@BAN varchar(50) = null, --'955932237',
@FROM_DATE date = NULL,
@TO_DATE date = NULL,
@IMEI varchar(100) = NULL;
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 [#Assurant Manufacturer Received] QR
WHERE QR.[Offer_Generate_Date] BETWEEN @FROM_DATE AND @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'
Non clustered covering index:
USE [DistributionCenterFeed]
GO
/****** Object: Index [idx_BTI_include] Script Date: 11/28/2018 11:58:24 PM ******/
CREATE NONCLUSTERED INDEX [idx_BTI_include] ON [dbo].[QR_Device_Trade_In_Master_2016onward]
(
[BulkID] ASC
)
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]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
--Quote me
November 29, 2018 at 2:34 am
Good progress.
I would however like to see both explain plans for my code (changing the union all to union) and Jonathan's with indexes without included columns.
for the volumes of rows returned you mentioned I do not think they are needed.
e.g. the ones I posted earlier but with removal of ImportFileDate_Offer_Generate_Date as you said this is no longer needed.
Temp tables vs CTE can change the plans a lot - and the step up from a CTE is going to a temp table
Viewing 15 posts - 16 through 30 (of 54 total)
You must be logged in to reply to this topic. Login to reply