September 13, 2011 at 6:50 am
SELECT A.[Product Number (SKU)],A.[Serial Numbers],A.[Sell Through Quantity],A.[Seller - Company Name],C.[Partner Locn ID],A.[Eclipse ID],A.[MC Eligibility],A.[Seller - Company ID],A.[Trans Date],A.[Trans Doc ID],B.[Programe ID],B.[Promo Type],B.[Rebate Value] FROM dbo.FinalOutput A LEFT JOIN dbo.PromoMaster B ON (B.SKU <> 'ALL' And A.[Product Number (SKU)] = B.SKU Or B.SKU = 'ALL' And A.[Product Line ID] = B.PL) LEFT JOIN dbo.PARTNER_MST C ON A.[Seller - Company ID] = C.[Partner Pro ID] WHERE A.[Trans Date]>=B.[Start Date] And A.[Trans Date]<=B.[End Date] AND Charindex(A.[Seller - Company ID],B.[T1 ID])>0
Here, The main issue is its taking too much time(00:02:49 mins) to display the result.
Because of this delay, an error occurs in the application saying
"Query Timeout Expired"
I want to make this query work faster. Can any one please guide me on this issue
September 13, 2011 at 6:59 am
With that Charindex call in the Where clause, it's always going to be a scan, not a seek. If you have any significant number of rows, it's going to be slow.
That's probalby the single biggest problem. Is there any way to get that same logic without having to look inside a string column like that?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 13, 2011 at 7:17 am
Read this link for how to post performance problems[/url].
From just looking at your code, it's difficult to suggest much other than the obvious.
SELECT A.[Product Number (SKU)],A.[Serial Numbers],
A.[Sell Through Quantity],A.[Seller - Company Name],C.[Partner Locn ID],
A.[Eclipse ID],A.[MC Eligibility],A.[Seller - Company ID],A.[Trans Date],
A.[Trans Doc ID],B.[Programe ID],B.[Promo Type],B.[Rebate Value]
FROM dbo.FinalOutput A
LEFT JOIN dbo.PromoMaster B ON (B.SKU <> 'ALL' And A.[Product Number (SKU)] = B.SKU
Or B.SKU = 'ALL' And A.[Product Line ID] = B.PL)
LEFT JOIN dbo.PARTNER_MST C ON A.[Seller - Company ID] = C.[Partner Pro ID]
WHERE A.[Trans Date]>=B.[Start Date] And A.[Trans Date]<=B.[End Date]
AND Charindex(A.[Seller - Company ID],B.[T1 ID])>0
September 13, 2011 at 10:17 am
With some filtered covering indexes on dbo.PromoMaster
CREATE NONCLUSTERED INDEX FIPromoMasterSKUNotAll
ON dbo.PromoMaster (SKU, [Start Date], [End Date])
INCLUDE ([T1 ID], [Programe ID], [Promo Type], [Rebate Value])
WHERE SKU <> 'ALL';
CREATE NONCLUSTERED INDEX FIPromoMasterSKUAll
ON dbo.PromoMaster (PL, [Start Date], [End Date])
INCLUDE ([T1 ID], [Programe ID], [Promo Type], [Rebate Value])
WHERE SKU = 'ALL';
the following may be quicker:
WITH Final
AS
(
SELECT A.[Product Number (SKU)],A.[Serial Numbers],A.[Sell Through Quantity],A.[Seller - Company Name]
,A.[Eclipse ID],A.[MC Eligibility],A.[Seller - Company ID],A.[Trans Date],A.[Trans Doc ID]
,B.[Programe ID],B.[Promo Type],B.[Rebate Value]
FROM dbo.FinalOutput A
JOIN dbo.PromoMaster B
ON B.SKU <> 'ALL' AND A.[Product Number (SKU)] = B.SKU
AND A.[Trans Date] BETWEEN B.[Start Date] AND B.[End Date]
AND CHARINDEX(A.[Seller - Company ID], B.[T1 ID]) > 0
UNION ALL
SELECT A.[Product Number (SKU)],A.[Serial Numbers],A.[Sell Through Quantity],A.[Seller - Company Name]
,A.[Eclipse ID],A.[MC Eligibility],A.[Seller - Company ID],A.[Trans Date],A.[Trans Doc ID]
,B.[Programe ID],B.[Promo Type],B.[Rebate Value]
FROM dbo.FinalOutput A
JOIN dbo.PromoMaster B
ON B.SKU = 'ALL' AND A.[Product Line ID] = B.PL
AND A.[Trans Date] BETWEEN B.[Start Date] AND B.[End Date]
AND CHARINDEX(A.[Seller - Company ID], B.[T1 ID]) > 0
)
SELECT F.[Product Number (SKU)],F.[Serial Numbers],F.[Sell Through Quantity],F.[Seller - Company Name],C.[Partner Locn ID]
,F.[Eclipse ID],F.[MC Eligibility],F.[Seller - Company ID],F.[Trans Date],F.[Trans Doc ID]
,F.[Programe ID],F.[Promo Type],F.[Rebate Value]
FROM Final F
LEFT JOIN dbo.PARTNER_MST C
ON A.[Seller - Company ID] = C.[Partner Pro ID];
September 13, 2011 at 11:17 am
Indexes on FinalOutput, like the following, may also help.
CREATE NONCLUSTERED INDEX FinalOutputProdNumTransDate
ON dbo.FinalOutput ([Product Number (SKU)], [Trans Date])
INCLUDE ([Seller - Company ID]);
CREATE NONCLUSTERED INDEX FinalOutputProdLineTransDate
ON dbo.FinalOutput ([Product Line ID], [Trans Date])
INCLUDE ([Seller - Company ID]);
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply