I have a mammoth SQL statement, however it's taking a long time to load (27 secs on the server). I think the issue lies with the IN statements towards the bottom (the IN statement is repeated too, can this be stored so it only does it once?) (taking it out shaves 23 seconds, anything under 10 will be brilliant) but i'm unsure, is there any better ways of writing this?
Thanks in advance!
Dave
DECLARE @site INT = 4
DECLARE @langid INT = 9
DECLARE @checkID INT = 0
DECLARE @minstockQTY INT = 0
DECLARE @branchCount INT = (SELECT COUNT(*)
FROM Branch
WHERE ApplicationID = @site)
if @site = 4 BEGIN
SET @checkID = 4
end
select p.*,
stuff((select ',' + cast(BrandID as nvarchar(max))
from Product_Brand_Mapping d
where d.ProductCode = p.ProductCode
AND d.ApplicationID = @site for xml path('')) , 1, 1, '') as BrandIDs,
stuff((select '|' + cast(Name as nvarchar(max))
from Brands e
where e.ID IN (SELECT BrandID
FROM Product_Brand_Mapping f
WHERE f.ProductCode = p.ProductCode
AND f.ApplicationID = @site) for xml path('')), 1, 1, '') as BrandNames,
stuff((select ',' + cast(DepartmentID as nvarchar(max))
from Product_Department_Mapping e
where e.ProductCode = p.ProductCode
and e.ApplicationID = @site for xml path('')), 1, 1, '') as DepartmentIDs,
stuff((select ',' + cast(DepartmentID as nvarchar(max)) + '|' + cast(DisplayOrder as nvarchar(max))
from Product_Department_DisplayOrder e
where e.ProductCode = p.ProductCode
and e.ApplicationID = @site for xml path('')), 1, 1, '') as DisplayOrders,
stuff((select '|' + cast(Size as nvarchar(max))
from Products e
where e.ProductCode = p.ProductCode
and e.Sell > 0
AND e.Department > 0
AND e.ApplicationID = @site
AND e.Published = 1
AND e.PrimaryPLU IN (select SKU
FROM Stock
WHERE BranchID IN (1,2,11,12,32,31,13,14,15,0,96)
AND ApplicationID = @site
GROUP BY SKU
HAVING(SUM(CASE WHEN Stock < 0
AND BranchID = 31
THEN Stock
WHEN BranchID = 31
THEN Stock END)) - p.MinStockQty > 0) for xml path('')), 1, 1, '') as Sizes,
stuff((select '|' + cast(Colour as nvarchar(max))
from Products e
where e.ProductCode = p.ProductCode
and e.Sell > 0
AND e.Department > 0
AND e.ApplicationID = @site
AND e.Published = 1
AND e.PrimaryPLU IN (select SKU
FROM Stock
WHERE BranchID IN (1,2,11,12,32,31,13,14,15,0,96)
AND ApplicationID = @checkID
GROUP BY SKU
HAVING(SUM(CASE WHEN Stock < 0
AND BranchID = 31
THEN Stock
WHEN BranchID NOT IN (31)
THEN Stock END)) - p.MinStockQty > 0) for xml path('')), 1, 1, '') as Colours,
(select MAX(pr.Sell)
from Products pr
where pr.ProductCode = p.ProductCode
AND pr.ApplicationID = @site) as SellTo,
(select MAX(pr.WholeSale)
from Products pr
where pr.ProductCode = p.ProductCode
AND pr.ApplicationID = @site) as WasTo,
(select MAX(Department)
from Products pr
where pr.ProductCode = p.ProductCode
AND pr.ApplicationID = @site) as Department
from ProductDescription p
WHERE p.ApplicationID = @site
AND p.LanguageID = @langid
AND p.Published = 1
AND p.Deleted = 0
AND p.ProductCode IN (SELECT Distinct pr.ProductCode
FROM Products pr
WHERE pr.Sell > 0
AND pr.Department > 0
AND pr.ApplicationID = @site
AND pr.Published = 1)
AND ((SELECT SUM(st.Stock)
FROM Stock st
WHERE st.ApplicationID = 4
AND st.SKU IN (SELECT DISTINCT pr.PrimaryPLU
FROM Products pr
WHERE p.ProductCode = pr.ProductCode
AND pr.ApplicationID = @site)
AND st.BranchID IN (1,2,11,12,32,13,14,15,0,96))
+ ISNULL((SELECT SUM(st.Stock)
FROM Stock st
WHERE st.ApplicationID = 4
AND st.SKU IN (SELECT DISTINCT pr.PrimaryPLU
FROM Products pr
WHERE p.ProductCode = pr.ProductCode
AND pr.ApplicationID = @site)
AND st.BranchID = 31
AND st.Stock < 0), 0)) - p.MinStockQty > 0
CASEstatements have unnecessary conditions... \$\endgroup\$ – Clockwork-Muse Dec 12 '12 at 23:12CREATE TABLEandINSERTstatements for all of the relevant tables as pasties and link them in your post. If it is confidential, then manufacture some fake data that shows the problem. Ideally, provide a SQLFiddle with your query running and showing results, and you will get lots of help. \$\endgroup\$ – mellamokb Apr 18 '13 at 13:15