November 29, 2018 at 2:47 am
@polkadot, there is an error in your code:
FROM [#Assurant Manufacturer Received] QR
WHERE QR.[Offer_Generate_Date] BETWEEN @FROM_DATE AND @TO_DATE
should be
FROM DistributionCenterFeed.[dbo].[QR_Device_Trade_In_Master_2016onward] QR
WHERE QR.[Offer_Generate_Date] BETWEEN @FROM_DATE AND @TO_DATE
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 29, 2018 at 3:01 am
That plan has some strange features, e.g. estimated row count for BrandID of over a million for just 9 rows, and parallelism...
Can you try this please, and post up the plan? Using the same value for the variable as you used for the plan you posted up.
SELECT
[Offer_ID],
[Offer_Generate_Date],
[Serial_Number],
[BAN],
[BulkID]
INTO #AssurantManufacturerReceived
FROM DistributionCenterFeed.[dbo].[QR_Device_Trade_In_Master_2016onward]
WHERE QR.BulkID = @BULKID
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 29, 2018 at 9:03 am
Hi polkadot,
You said the query only returns 9 rows. This means you should be ok to put hints to force usage of the indexes.
Make sure you have indexes on the columns for each select in the unions:
DistributionCenterFeed.[dbo].[QR_Device_Trade_In_Master_2016onward]CREATE INDEX IX_QR_Device_Trade_In_Master_2016onward_BAN ON QR_Device_Trade_In_Master_2016onward(BAN);
CREATE INDEX IX_QR_Device_Trade_In_Master_2016onward_BulkID ON QR_Device_Trade_In_Master_2016onward(BulkID);
CREATE INDEX IX_QR_Device_Trade_In_Master_2016onward_Serial_Number ON QR_Device_Trade_In_Master_2016onward(Serial_Number);
CREATE INDEX IX_QR_Device_Trade_In_Master_2016onward_ ON QR_Device_Trade_In_Master_2016onward(Offer_Generate_Date);
Then add the hint: WITH (FORCESEEK) to each of the selects in the unions: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] BETWEEN @FROM_DATE AND @TO_DATE;
November 29, 2018 at 9:19 am
@frederico_fonseca - I will send the query and execution plan in private email tonight when server less busy. This morning the CTE query isn't completing, going 9 min. In addition to CTE, is it because all filters aka 'calculations' are at the end and query optimizer 'throws up it's hands' as @chrism-2 said??
@chrism-2 - I need the final query to pull from temp table: [#Assurant Manufacturer Received]. Please restate another way what modification you want me to make and post another exec plan, because I'm confused. Existing query contains a union with BULKID variable INSERT.
@jonathan-2 AC Roberts, i've checked the run of the query this morning and it returns in 12 sec which is still a serious progress (over 2 1/2 min before).
Adding FORCE SEEK generated this error:
Msg 8622, Level 16, State 1, Line 48 Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.
USE [DistributionCenterFeed]
GO
DECLARE
@BULKID varchar(50) = 5006,
@BAN varchar(50) = null, --'955932237',
@FROM_DATE date = null,--getdate() -60,
@TO_DATE date = null,--getdate(),
@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 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 [#Assurant Manufacturer Received] QR WITH (FORCESEEK)
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'
--Quote me
November 29, 2018 at 9:25 am
polkadot - Thursday, November 29, 2018 9:19 AM@jonathan-2 AC Roberts, i've checked the run of the query this morning and it returns in 12 sec which is still a serious progress (over 2 1/2 min before).Adding FORCE SEEK generated this error:
Msg 8622, Level 16, State 1, Line 48 Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.
I think that must be because you haven't got one or more of the indexes I listed created on that table.
There has to be an index that the query can use to do the seek for the hint to work.
Make sure you have the following indexes:CREATE INDEX IX_QR_Device_Trade_In_Master_2016onward_BAN ON QR_Device_Trade_In_Master_2016onward(BAN);
CREATE INDEX IX_QR_Device_Trade_In_Master_2016onward_BulkID ON QR_Device_Trade_In_Master_2016onward(BulkID);
CREATE INDEX IX_QR_Device_Trade_In_Master_2016onward_Serial_Number ON QR_Device_Trade_In_Master_2016onward(Serial_Number);
CREATE INDEX IX_QR_Device_Trade_In_Master_2016onward_ ON QR_Device_Trade_In_Master_2016onward(Offer_Generate_Date);
Or just try to "resubmit the query without specifying any hints and without using SET FORCEPLAN".
November 29, 2018 at 9:26 am
polkadot - Thursday, November 29, 2018 9:19 AM@frederico_fonseca - I will send the query and execution plan in private email tonight when server less busy. This morning the CTE query isn't completing, going 9 min. In addition to CTE, is it because all filters aka 'calculations' are at the end and query optimizer 'throws up it's hands' as @chrism-2 said??@chrism-2 - I need the final query to pull from temp table: [#Assurant Manufacturer Received]
@jonathan-2 AC Roberts, i've checked the run of the query this morning and it returns in 12 sec which is still a serious progress (over 2 1/2 min before).
Adding FORCE SEEK generated this error:
Msg 8622, Level 16, State 1, Line 48 Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.
What column or columns, added to [Offer_ID] (which has nulls), will uniquely identify every row in the table?
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 29, 2018 at 9:31 am
ChrisM@Work - Thursday, November 29, 2018 2:47 AM@polkadot, there is an error in your code:FROM [#Assurant Manufacturer Received] QR
WHERE QR.[Offer_Generate_Date] BETWEEN @FROM_DATE AND @TO_DATEshould be
FROM DistributionCenterFeed.[dbo].[QR_Device_Trade_In_Master_2016onward] QR
WHERE QR.[Offer_Generate_Date] BETWEEN @FROM_DATE AND @TO_DATE
polkadot - Thursday, November 29, 2018 9:19 AM@chrism-2 - I need the final query to pull from temp table: [#Assurant Manufacturer Received]. Please restate another way what modification you want me to make and post another exec plan, because I'm confused. Existing query contains a union with BULKID variable INSERT.
Chris is talking about the spurious [#Assurant Manufacturer Received] in the UNIONS.
I think it's my fault from a copy/paste error.
November 29, 2018 at 9:36 am
@jonathan-2 AC Roberts
I used Generate Scripts to script out all indexes created for this table, and it includes the non clustered you mentioned (different names/ they already existed)
( btw, SET ANSI PADDING ON isn't anything I specified AND I don't know why it scripted out a 'missing' index especially when I'm not using ImportFileDate in query )
USE [DistributionCenterFeed]
GO
CREATE CLUSTERED INDEX [cidx_QR_Device_Trade_In_Master] 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
SET ANSI_PADDING ON
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>] ON [dbo].[QR_Device_Trade_In_Master_2016onward]
(
[ImportFileDate] ASC,
[Offer_Generate_Date] ASC
)
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]) 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 [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) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
CREATE NONCLUSTERED INDEX [idx_ImportFileDate_include] ON [dbo].[QR_Device_Trade_In_Master_2016onward]
(
[ImportFileDate] 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],
[Program_ID],
[BulkID],
[Assurant_Model],
[Assurant_Manufacturer]) 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 [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
CREATE NONCLUSTERED INDEX [ImportFileDate_Offer_Generate_Date] ON [dbo].[QR_Device_Trade_In_Master_2016onward]
(
[ImportFileDate] ASC,
[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
SET ANSI_PADDING ON
GO
CREATE NONCLUSTERED INDEX [ix_Stuff] ON [dbo].[QR_Device_Trade_In_Master_2016onward]
(
[ImportFileDate] ASC
)
INCLUDE ( [BAN],
[BulkID],
[Serial_Number],
[Offer_Generate_Date]) 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
--Quote me
November 29, 2018 at 9:37 am
ChrisM@Work - Thursday, November 29, 2018 3:01 AMThat plan has some strange features, e.g. estimated row count for BrandID of over a million for just 9 rows, and parallelism...Can you try this please, and post up the plan? Using the same value for the variable as you used for the plan you posted up.
SELECT
[Offer_ID],
[Offer_Generate_Date],
[Serial_Number],
[BAN],
[BulkID]
INTO #AssurantManufacturerReceived
FROM DistributionCenterFeed.[dbo].[QR_Device_Trade_In_Master_2016onward]
WHERE QR.BulkID = @BULKID
Hi Polkadot, did you get anywhere with this?
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 29, 2018 at 9:44 am
ChrisM@Work - Thursday, November 29, 2018 9:37 AMChrisM@Work - Thursday, November 29, 2018 3:01 AMThat plan has some strange features, e.g. estimated row count for BrandID of over a million for just 9 rows, and parallelism...Can you try this please, and post up the plan? Using the same value for the variable as you used for the plan you posted up.
SELECT
[Offer_ID],
[Offer_Generate_Date],
[Serial_Number],
[BAN],
[BulkID]
INTO #AssurantManufacturerReceived
FROM DistributionCenterFeed.[dbo].[QR_Device_Trade_In_Master_2016onward]
WHERE QR.BulkID = @BULKIDHi Polkadot, did you get anywhere with this?
USE [DistributionCenterFeed]
GO
--Quote me
November 29, 2018 at 9:51 am
DECLARE @BULKID varchar(50) = 5006
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 29, 2018 at 9:54 am
@jonathan-2 AC Roberts
I used Generate Scripts to script out all indexes created for this table, and it includes the non clustered you mentioned (different names/ they already existed)
( btw, SET ANSI PADDING ON isn't anything I specified AND I don't know why it scripted out a 'missing' index especially when I'm not using ImportFileDate in query )
I think it might be because you haven't changed FROM [#Assurant Manufacturer Received]QR
WHERE QR.[Offer_Generate_Date] BETWEEN @FROM_DATE AND @TO_DATE
instead ofFROM DistributionCenterFeed.[dbo].[QR_Device_Trade_In_Master_2016onward] QR
WHERE QR.[Offer_Generate_Date] BETWEEN @FROM_DATE AND @TO_DATE
in the unions?
Can you paste in the full select with UNION statement?
November 29, 2018 at 10:09 am
ChrisM@Work - Thursday, November 29, 2018 9:51 AMJust this, please:DECLARE @BULKID varchar(50) = 5006
SELECT
[Offer_ID],
[Offer_Generate_Date],
[Serial_Number],
[BAN],
[BulkID]
INTO #AssurantManufacturerReceived
FROM DistributionCenterFeed.[dbo].[QR_Device_Trade_In_Master_2016onward]
WHERE QR.BulkID = @BULKID
attached execplan
USE [DistributionCenterFeed]
GO
DECLARE @BULKID varchar(50) = 5006
IF OBJECT_ID('tempdb..[#AssurantManufacturerReceived]','U') IS NOT NULL
DROP TABLE [#AssurantManufacturerReceived];
SELECT
[Offer_ID],
[Offer_Generate_Date],
[Serial_Number],
[BAN],
[BulkID]
INTO #AssurantManufacturerReceived
FROM DistributionCenterFeed.[dbo].[QR_Device_Trade_In_Master_2016onward] QR
WHERE QR.BulkID = @BULKID
--Quote me
November 29, 2018 at 11:32 am
before 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]
November 29, 2018 at 2:03 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 don't see the non clustered index on OfferGenerateDate used, and do not understand why the clustered on OfferID is used.
I think we agree that Query2 query cost is not the issue.
@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.
query :USE [DistributionCenterFeed]
GO
DECLARE
@BULKID varchar(50) = 5006,
@BAN varchar(50) = null, --'955932237',
@FROM_DATE date = null,--getdate() -60,
@TO_DATE date = null,--getdate(),
@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 [#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'
--Quote me
Viewing 15 posts - 31 through 45 (of 54 total)
You must be logged in to reply to this topic. Login to reply