June 13, 2006 at 1:59 pm
Here's another Union subject...
I have two indiviual statements that are fast (8 seconds, 2 seconds), but when I union them in a view, the query takes over a minute.
When do I begin? I always have this issue and just end up making two views and using ASP to do the joins. (The worst way to do it). I have more time to do some R&D with this project, So I want to learn how I can do this.
June 13, 2006 at 2:24 pm
I really don't think ASP is doing the joins. SQL Server is doing the joins.
Also I believe that you are doing a CROSS JOIN (Cartesian Product), selecting all possible combinations from the two views. I think you are in need of an inner join, or possible a left or right join.
Please post your query here, sample date from the two views and the output you wish for, using the supplied test data.
N 56°04'39.16"
E 12°55'05.25"
June 13, 2006 at 2:27 pm
I would guess you're using UNION when you should be using UNION ALL, which forces a potentially costly sort/distinct operation. But just a guess until you post more info.
June 14, 2006 at 11:49 am
Here is the code. Right now if I run this, it's around 12 seconds. If I take out both the Where clauses and move this into a view, and then query the view using the where clause, it takes over a minute. (timesout in ASP).
SELECT H.PlantNumber, H.PONumber, 'W' as PlantType, H.StatusCode, VM.VendorNumber, VM.VendorName, H.OrderDate, Case when D.ItemTypeCode = 'E' THEN 'N' ELSE D.ItemTypeCode END as InventoryItem, D.ItemNumber, D.PartNumber,
D.PartDescription AS POPartDescr, IM.PartDescription AS IMPartDescr, IM.ProductCode, WPC.ProductDescription AS ProductCodeDescr,
WPC.ProductGroupCode + ' - ' + PGC.ProductGroupDescription AS ProductGroupCode, WPC.ProductGroupType AS CommodityGroup, ID.WarehouseLocation,
D.TotalOrderQty, (Select Sum(InventoryQty) FROM DataWarehouse.dbo.MSSvEDIDetails ED WHERE ED.InvoicePONumber = H.PONumber AND (ED.ParkerPartNumber = D.PartNumber OR ED.CustomerPartNumber = D.PartNumber)) as ShipQty, CASE WHEN Len(D.ReceiptDate)<> 0 THEN D.ReceivedQty END as ReceivedQty,
ISNULL(D.Price*ER.ExchangeRate, 0) AS UnitPriceCND, ISNULL(D.Price, 0) AS UnitPriceUS,
ISNULL(D.TotalOrderQty*D.Price*ER.ExchangeRate, 0) AS ExtPriceCnd, ISNULL(D.TotalOrderQty*D.Price, 0) AS ExtPriceUS,
D.NeedDate01 AS RequestDate, (Select Max(ShipDate) FROM DataWarehouse.dbo.MSSvEDIDetails ED WHERE (ED.InvoicePONumber = H.PONumber OR ED.PONumber = H.PONumber) AND (ED.ParkerPartNumber = D.PartNumber OR ED.CustomerPartNumber = D.PartNumber)) as ShipDate, D.ReceiptDate, H.ShipVia, V.BuyerId,
CASE WHEN ID.ManufacturingLeadTime <> 0 THEN ID.ManufacturingLeadTime ELSE WPC.StandardDeliveryDays END AS LeadDays,
(Select distinct 'Y' FROM DataWarehouse.dbo.WhseOpenOrders WHERE PartNumber = D.PartNumber) as CrossDock
FROM dbo.MSSvPOHeader H INNER JOIN
dbo.MSSvPODetails D ON H.PlantNumber = D.PlantNumber AND H.PONumber = D.PONumber LEFT OUTER JOIN
dbo.MSSvVendorMaster VM ON H.VendorControlNumber = VM.VendorControlNumber LEFT OUTER JOIN
Reference.dbo.R_VendorBuyer V ON VM.VendorNumber = V.VendorNumber LEFT OUTER JOIN
dbo.MSSvItemMaster IM ON D.PartNumber = IM.PartNumber LEFT OUTER JOIN
dbo.MSSvItemInventoryData ID ON D.PartNumber = ID.PartNumber AND D.PlantNumber = ID.PlantNumber LEFT OUTER JOIN
dbo.WhseProductCodes WPC ON IM.ProductCode = WPC.ProductCode INNER JOIN
Reference.dbo.R_ProductGroupCode PGC ON WPC.ProductGroupCode = PGC.ProductGroupCode INNER JOIN
Reference.dbo.ExchangeRate ER ON H.OrderDate between ER.Date1 and ER.Date2
WHERE H.StatusCode<>'C' AND D.TotalOrderQty > ISNULL(D.ReceivedQty,0)
UNION ALL
SELECT H.PlantNumber, H.PONumber, 'C' as PlantType, CASE WHEN D.ReceiptDateTime is null then 'O' Else 'C' END as StatusCode, H.VendorNumber, H.VendorName, H.OrderDate, H.InventoryCode as InventoryItem, D.ItemNumber, D.PartNumber,
D.PartDescription, '' as IMPartDescr, '' as ProductCode, '' as ProductCodeDescr, '' as ProductGroupCode, '' as CommodityGroup,
(SELECT DISTINCT Min(StockLocation) FROM DataWarehouse.dbo.MfgShopComponents WHERE PlantNumber = D.PlantNumber AND Component = D.PartNumber) as WarehouseLocation,
D.OnOrderQty, '' as ShipQty, D.ChangeQty as ReceivedQty,
CASE WHEN H.Country = 'CA' THEN ISNULL(D.ExtendedCost/D.OnOrderQty,0) ELSE ISNULL((D.ExtendedCost/D.OnOrderQty)* ER.ExchangeRate,0) END AS UnitPriceCND,
CASE WHEN H.Country = 'US' THEN ISNULL(D.ExtendedCost/D.OnOrderQty,0) ELSE ISNULL((D.ExtendedCost/D.OnOrderQty)/ ER.ExchangeRate,0) END AS UnitPriceUS,
CASE WHEN H.Country = 'CA' THEN ISNULL(D.ExtendedCost,0) ELSE ISNULL((D.ExtendedCost)* ER.ExchangeRate,0) END AS ExtPriceCND,
CASE WHEN H.Country = 'US' THEN ISNULL(D.ExtendedCost,0) ELSE ISNULL((D.ExtendedCost)/ ER.ExchangeRate,0) END AS ExtPriceUS,
D.RequestDate,null as ShipDate, D.ReceiptDateTime as ReceiptDate, '' as ShipVia, D.RequesterId as BuyerId, '' as LeadDays,
(Select distinct 'Y' FROM DataWarehouse.dbo.MfgShopComponents C INNER JOIN DataWarehouse.dbo.View_MfgBacklog M ON C.OrderNumber = M.OrderNumber Where C.Component = D.PartNumber) as CrossDock
FROM MfgPOHeader H INNER JOIN
MfgPODetails D ON H.PlantNumber = D.PlantNumber AND H.PONumber = D.PONumber INNER JOIN
Reference.dbo.ExchangeRate ER ON H.OrderDate Between ER.Date1 and ER.Date2
WHERE D.ReceiptDateTime is null AND D.OnOrderQty > ISNULL(D.ChangeQty,0)
My way around this in the past, was the make two views and use TSQL/ASP to do the union and where clauses. I'm not sure the best way on this.
June 14, 2006 at 12:28 pm
The problem is not the UNION.
The problem is all the sub-SELECTs within each SELECT, eg:
(Select distinct 'Y' FROM DataWarehouse.dbo.WhseOpenOrders WHERE PartNumber = D.PartNumber) as CrossDock
When you place a SELECT within a SELECT, you implement essentially a cursor based solution that can nearly always be guaranteed to perform abominably.
In most cases, these can be re-expressed as derived tables in the FROM part of your query. EG, above, you need a 'Y' indicator if a part number exists in aother table ? In that case, build a derived table of the PartNumbers and LEFT JOIN to it. The derived table gets evaluated ONCE, and doesn't have to be evaluated for each row thru the main resultset. Here is how you would get your column "CrossDock" using a derived table:
SELECT {Your select columns go here},
CASE WHEN dt.PartNumber IS NOT NULL THEN 'Y' ELSE 'N' END AS CrossDock
FROM {Your Current FROM goes here}
LEFT JOIN
(
SELECT DISTINCT PartNumber
FROM DataWarehouse.dbo.WhseOpenOrders
) dt
On (dt.PartNumber = D.PartNumber)
June 14, 2006 at 2:33 pm
PW:
Thank you, I never thought about sub-selects slowing things down that much. I did like you said and put it into a derived table (never thought about doing that either). I'll try that out and change the rest of them and see how the union works after that.
June 19, 2006 at 9:37 am
I made the changes and the union seems to be working a little more quicker and stable. Seperate the queries are 8 seconds together. In the view, it's around 17 seconds. (But atleast its not timing out in the ASP Page)
June 19, 2006 at 11:35 am
Can you post the individual statements, with their WHERE clauses, and the select from the view, with its WHERE clause? There shouldn't be any significant difference in execution times.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
June 19, 2006 at 1:06 pm
I don't know how to post code the right way, but here is a dump of everything....
View - 17 seconds
SELECT PO.PlantType,PO.CrossDock,ISNULL(Left(SP.FirstName,1) +' '+ SP.LastName,PO.BuyerId),PO.VendorNumber,PO.VendorName,PO.PlantNumber,PO.PONumber,PO.ItemNumber,PO.PartNumber,PO.POPartDescr,PO.IMPartDescr,PO.ProductCode,PO.ProductCodeDescr,PO.ProductGroupCode,PO.InventoryItem,PO.OrderDate,PO.RequestDate,PO.ShipDate,PO.ReceiptDate,PO.TotalOrderQty,PO.ShipQty,PO.ReceivedQty,PO.UnitPriceCND,PO.UnitPriceUS,PO.ExtPriceCND,PO.ExtPriceUS,PO.LeadDays
,IV.Avl_Mil,IV.Avl_Cal,IV.Avl_Mtl,PO.ShipVia
FROM DataWarehouse.dbo.View_PurchaseOrders PO LEFT OUTER JOIN
StaffProfiles.dbo.StaffProfiles SP ON PO.BuyerId = SP.ClockNumber
LEFT OUTER JOIN (SELECT PartNumber, SUM(CASE WHEN PlantNumber='08' THEN AvailableInventory END) as Avl_Mil, SUM(CASE WHEN PlantNumber='10' THEN AvailableInventory END) as Avl_Cal, SUM(CASE WHEN PlantNumber='14' THEN AvailableInventory END) as Avl_Mtl FROM DataWarehouse.dbo.MSSvItemInventoryData GROUP BY PartNumber) IV ON PO.PartNumber = IV.PartNumber
WHERE PO.OrderDate between '6/1/2006' and '6/12/2006' AND PO.TotalOrderQty > ISNULL(PO.ReceivedQty,0)
ORDER BY PO.RequestDate
T-SQL - 7 seconds
SELECT H.PlantNumber, H.PONumber, 'W' as PlantType, CASE WHEN D.ReceiptDate is null then 'O' WHEN Len(D.ReceiptDate)<>0 THEN 'C' ELSE H.StatusCode END as StatusCode, VM.VendorNumber, VM.VendorName, H.OrderDate, Case when D.ItemTypeCode = 'E' THEN 'N' ELSE D.ItemTypeCode END as InventoryItem, D.ItemNumber, D.PartNumber,
D.PartDescription AS POPartDescr, IM.PartDescription AS IMPartDescr, IM.ProductCode, WPC.ProductDescription AS ProductCodeDescr,
WPC.ProductGroupCode + ' - ' + PGC.ProductGroupDescription AS ProductGroupCode, WPC.ProductGroupType AS CommodityGroup, ID.WarehouseLocation,
D.TotalOrderQty, ED.InventoryQty as ShipQty, CASE WHEN Len(D.ReceiptDate)<> 0 THEN D.ReceivedQty END as ReceivedQty,
ISNULL(D.Price*ER.ExchangeRate, 0) AS UnitPriceCND, ISNULL(D.Price, 0) AS UnitPriceUS,
ISNULL(D.TotalOrderQty*D.Price*ER.ExchangeRate, 0) AS ExtPriceCnd, ISNULL(D.TotalOrderQty*D.Price, 0) AS ExtPriceUS,
D.NeedDate01 AS RequestDate, ED.ShipDate, D.ReceiptDate, H.ShipVia, V.BuyerId,
CASE WHEN ID.ManufacturingLeadTime <> 0 THEN ID.ManufacturingLeadTime ELSE WPC.StandardDeliveryDays END AS LeadDays,
CASE WHEN WB.PartNumber IS NOT NULL THEN 'Y' END as CrossDock
FROM dbo.MSSvPOHeader H INNER JOIN
dbo.MSSvPODetails D ON H.PlantNumber = D.PlantNumber AND H.PONumber = D.PONumber LEFT OUTER JOIN
dbo.MSSvVendorMaster VM ON H.VendorControlNumber = VM.VendorControlNumber LEFT OUTER JOIN
Reference.dbo.R_VendorBuyer V ON VM.VendorNumber = V.VendorNumber LEFT OUTER JOIN
dbo.MSSvItemMaster IM ON D.PartNumber = IM.PartNumber LEFT OUTER JOIN
dbo.MSSvItemInventoryData ID ON D.PartNumber = ID.PartNumber AND D.PlantNumber = ID.PlantNumber LEFT OUTER JOIN
dbo.WhseProductCodes WPC ON IM.ProductCode = WPC.ProductCode INNER JOIN
Reference.dbo.R_ProductGroupCode PGC ON WPC.ProductGroupCode = PGC.ProductGroupCode INNER JOIN
Reference.dbo.ExchangeRate ER ON H.OrderDate between ER.Date1 and ER.Date2 LEFT OUTER JOIN
(SELECT distinct PartNumber FROM DataWarehouse.dbo.WhseOpenOrders) WB ON D.PartNumber = WB.PartNumber LEFT OUTER JOIN
(SELECT CASE WHEN Len(InvoicePONumber)<> 0 THEN InvoicePONumber ELSE PONumber END as PONumber, CASE WHEN Len(CustomerPartNumber)<> 0 THEN CustomerPartNumber ELSE ParkerPartNumber END as PartNumber, MAX(ShipDate) as ShipDate, Sum(InventoryQty) as InventoryQty FROM DataWarehouse.dbo.MSSvEDIDetails GROUP BY CASE WHEN Len(InvoicePONumber)<> 0 THEN InvoicePONumber ELSE PONumber END, CASE WHEN Len(CustomerPartNumber)<> 0 THEN CustomerPartNumber ELSE ParkerPartNumber END ) ED ON H.PONumber = ED.PONumber AND D.PartNumber = ED.PartNumber
WHERE H.OrderDate between '6/1/2006' and '6/12/2006' AND D.TotalOrderQty > ISNULL(D.ReceivedQty,0)
UNION ALL
SELECT H.PlantNumber, H.PONumber, 'C' as PlantType, CASE WHEN D.ReceiptDateTime is null then 'O' Else 'C' END as StatusCode, H.VendorNumber, H.VendorName, H.OrderDate, H.InventoryCode as InventoryItem, D.ItemNumber, D.PartNumber,
'' as POPartDescr, D.PartDescription as IMPartDescr, '' as ProductCode, '' as ProductCodeDescr, '' as ProductGroupCode, '' as CommodityGroup,
MSC.StockLocation as WarehouseLocation,
D.OnOrderQty as TotalOrderQty, null as ShipQty, D.ChangeQty as ReceivedQty,
CASE WHEN H.Country = 'CA' THEN ISNULL(D.ExtendedCost/D.OnOrderQty,0) ELSE ISNULL((D.ExtendedCost/D.OnOrderQty)* ER.ExchangeRate,0) END AS UnitPriceCND,
CASE WHEN H.Country = 'US' THEN ISNULL(D.ExtendedCost/D.OnOrderQty,0) ELSE ISNULL((D.ExtendedCost/D.OnOrderQty)/ ER.ExchangeRate,0) END AS UnitPriceUS,
CASE WHEN H.Country = 'CA' THEN ISNULL(D.ExtendedCost,0) ELSE ISNULL((D.ExtendedCost)* ER.ExchangeRate,0) END AS ExtPriceCND,
CASE WHEN H.Country = 'US' THEN ISNULL(D.ExtendedCost,0) ELSE ISNULL((D.ExtendedCost)/ ER.ExchangeRate,0) END AS ExtPriceUS,
D.RequestDate,null as ShipDate, D.ReceiptDateTime as ReceiptDate, '' as ShipVia, D.RequesterId as BuyerId, '' as LeadDays,
CASE WHEN MSC.OrderNumber is not null then 'Y' END as CrossDock
FROM MfgPOHeader H INNER JOIN
MfgPODetails D ON H.PlantNumber = D.PlantNumber AND H.PONumber = D.PONumber INNER JOIN
Reference.dbo.ExchangeRate ER ON H.OrderDate Between ER.Date1 and ER.Date2 LEFT OUTER JOIN
(Select SC.PlantNumber, SC.Component, Min(SC.StockLocation) as StockLocation, Min(M.OrderNumber) as OrderNumber FROM DataWarehouse.dbo.MfgShopComponents SC LEFT OUTER JOIN DataWarehouse.dbo.View_MfgBacklog M ON SC.OrderNumber = M.OrderNumber GROUP BY SC.PlantNumber, SC.Component) MSC ON D.PlantNumber = MSC.PlantNumber AND D.PartNumber = MSC.Component
WHERE H.OrderDate between '6/1/2006' and '6/12/2006' AND D.OnOrderQty > ISNULL(D.ChangeQty,0)
So I think the T-SQL is quicker because each statement gets the where clause, compared to the view that unions both statements and the applies the where clause. Or am I way off ??
June 20, 2006 at 9:03 pm
(
H.PlantNumber, H.PONumber, 'W' as PlantType,
dbo.MSSvPOHeader H
JOIN dbo.MSSvPODetails D ON H.PlantNumber = D.PlantNumber AND H.PONumber = D.PONumber
OUTER JOIN dbo.MSSvVendorMaster VM ON H.VendorControlNumber = VM.VendorControlNumber
OUTER JOIN Reference.dbo.R_VendorBuyer V ON VM.VendorNumber = V.VendorNumber
OUTER JOIN dbo.MSSvItemMaster IM ON D.PartNumber = IM.PartNumber
OUTER JOIN dbo.MSSvItemInventoryData ID ON D.PartNumber = ID.PartNumber AND D.PlantNumber = ID.PlantNumber
OUTER JOIN dbo.WhseProductCodes WPC ON IM.ProductCode = WPC.ProductCode
JOIN Reference.dbo.R_ProductGroupCode PGC ON WPC.ProductGroupCode = PGC.ProductGroupCode
JOIN Reference.dbo.ExchangeRate ER ON H.OrderDate between ER.Date1 and ER.Date2
OUTER JOIN (SELECT distinct PartNumber FROM DataWarehouse.dbo.WhseOpenOrders) WB ON D.PartNumber = WB.PartNumber
OUTER JOIN (SELECT CASE WHEN Len(InvoicePONumber)<> 0 THEN InvoicePONumber ELSE PONumber END as PONumber,
ALL
H.PlantNumber, H.PONumber, 'C' as PlantType, CASE WHEN D.ReceiptDateTime is null then 'O' Else 'C' END as StatusCode,
MfgPOHeader H
JOIN MfgPODetails D ON H.PlantNumber = D.PlantNumber AND H.PONumber = D.PONumber
JOIN Reference.dbo.ExchangeRate ER ON H.OrderDate Between ER.Date1 and ER.Date2
OUTER JOIN (Select SC.PlantNumber, SC.Component, Min(SC.StockLocation) as StockLocation, Min(M.OrderNumber) as OrderNumber
PO
OUTER JOIN StaffProfiles.dbo.StaffProfiles SP ON PO.BuyerId = SP.ClockNumber
OUTER JOIN ( SELECT PartNumber,
PO.OrderDate between '6/1/2006' and '6/12/2006' AND PO.TotalOrderQty > ISNULL(PO.ReceivedQty,0)
BY PO.RequestDate
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply