May 24, 2012 at 12:26 pm
I'm a little late to the battle, but I'm fond of a saying...
How do you eat an elephant? One bite at a time. That Original query is a Monster with lots of bits and pieces. The first thing I'd do is break it apart into more managable pieces. It's been my experience that a big, cool looking spiffy query doesn't do more than impress the less experienced. It's a nightmare to debug.
1. Break it up into managable chunks.
2. Check the plans for each chunk.
3. Are you using Indexes (someone has to have suggested that by now).
Just my $.02...
Crusty.
May 24, 2012 at 12:55 pm
This is the last LEFT OUTER JOIN from your view:
LEFT OUTER JOIN MOC_DT.dbo.TCN_Xref tcn
ON tcn.recid = (SELECT
MIN(recid)
FROM
MOC_DT.dbo.TCN_Xref
WHERE
DsLocationCode = CAST(term.TerminalID AS VARCHAR)
AND (sup.SupplierCode LIKE DsSupcode + '-%')
)
What table is this table being joined to, and what column or columns are being used between these tables? The join above currently has no reference to any of the other tables in the FROM clause.
May 24, 2012 at 9:21 pm
Lynn Pettis (5/24/2012)
This is the last LEFT OUTER JOIN from your view:
LEFT OUTER JOIN MOC_DT.dbo.TCN_Xref tcn
ON tcn.recid = (SELECT
MIN(recid)
FROM
MOC_DT.dbo.TCN_Xref
WHERE
DsLocationCode = CAST(term.TerminalID AS VARCHAR)
AND (sup.SupplierCode LIKE DsSupcode + '-%')
)
What table is this table being joined to, and what column or columns are being used between these tables? The join above currently has no reference to any of the other tables in the FROM clause.
This table is among the isolated ones. This is joined to itself but in the where clause if you see, it is compared with Terminal table(aka term) and supplier table(sup) so somehow it is linked to some other tables as well.
Regards
Chandan
May 25, 2012 at 9:39 am
chandan_jha18 (5/24/2012)
Lynn Pettis (5/24/2012)
This is the last LEFT OUTER JOIN from your view:
LEFT OUTER JOIN MOC_DT.dbo.TCN_Xref tcn
ON tcn.recid = (SELECT
MIN(recid)
FROM
MOC_DT.dbo.TCN_Xref
WHERE
DsLocationCode = CAST(term.TerminalID AS VARCHAR)
AND (sup.SupplierCode LIKE DsSupcode + '-%')
)
What table is this table being joined to, and what column or columns are being used between these tables? The join above currently has no reference to any of the other tables in the FROM clause.
This table is among the isolated ones. This is joined to itself but in the where clause if you see, it is compared with Terminal table(aka term) and supplier table(sup) so somehow it is linked to some other tables as well.
Regards
Chandan
The problem is that this last table appears to be doing a cartesian product (cross join) instead of being a left join. There is no criteria in the ON clause or the WHERE clause linking any data from MOC_DT.dbo.TCN_Xref to any of the tables declared in the FROM clause other than itself. The tables used in the subquery of the ON clause simply limit the records used from MOC_DT.dbo.TCN_Xref.
May 25, 2012 at 9:51 am
Here is the code from the initial post, formatted.
CREATE VIEW [dbo].[vw_ScheduledReceipts]
AS
SELECT term.StateCode AS OriginState
,dest_ad.[State] AS DestinationState
,ImpExp = CASE
WHEN term.StateCode = ord.DestState
THEN 0
ELSE 1
END
,Carrier# = CASE
WHEN ord.CarrierID >= 2000
THEN ord.CarrierID
ELSE 999
END
,car.CarrierCode AS CarrierName
,car.Descr AS CarrierDesc
,vend2.FederalID AS CarrierFederalID
,term.Descr AS Origin
,LTRIM(RTRIM(dest_ad.[City])) + ', ' + LTRIM(RTRIM(dest_ad.[state])) AS Destination
,sup.SupplierID AS Supplier#
,sup.Descr AS SupplierDesc
,vend.FederalID AS SupplierFederalID
,SupplierName = CASE
WHEN vend.Descr IS NULL
THEN sup.Descr
ELSE vend.Descr
END
,CAST(CONVERT(VARCHAR, ord.PostedDate, 101) AS SMALLDATETIME) AS PostedDate
,MONTH(ord.PostedDate) AS [Month]
,DATEPART(Q, ord.PostedDate) AS [Qtr]
,YEAR(ord.PostedDate) AS [Year]
,ordproditm.BOL AS BOL#
,prod.ProdCategory AS ProductCategory
,[Type] = CASE
WHEN prod.ProdCategory = 'X'
THEN prod.ProdCategory + '-' + prod.ProdCode
WHEN prod.ProdCategory = 'K'
AND prod.ProdCode = '11'
AND ord.DestState <> 'OH'
THEN 'D'
WHEN prod.ProdCategory = 'K'
AND prod.ProdCode = '11'
AND ord.DestState = 'OH'
THEN 'K'
WHEN prod.ProdCategory = 'D'
AND prod.ProdCode = '87'
THEN 'X-97'
ELSE prod.ProdCategory
END
,prod.ProdCode AS ProductCode
,prod.Descr AS ProductDesc
,shpprod.TaxExemptReason AS CustomerType
,supterm.PayFlag AS PayFlag
,SUM(CAST(ordproditm.GrossGallons AS DECIMAL(17, 6))) AS SumOfGrossGallons
,SUM(CAST(ordproditm.NetGallons AS DECIMAL(17, 6))) AS SumOfNetGallons
,SumOfBilledGallons = CASE
WHEN ordprod.BillCode = 'N'
THEN SUM(CAST(ordproditm.NetGallons AS DECIMAL(17, 6)))
WHEN ordprod.BillCode = 'G'
THEN SUM(CAST(ordproditm.GrossGallons AS DECIMAL(17, 6)))
ELSE 0
END
,ordprod.BillCode AS BillCode
,cust.CustNum AS Customer#
,cust.CustName AS CustomerName
,term.TermCode AS TerminalCode
,dest_ad.County AS County
,cust.FEIN AS [CustomerFederalIdentification#]
,TerminalControl# = CASE
WHEN ISNULL(tcn.TerminalNumber, '') = ''
THEN ''
ELSE tcn.TerminalNumber
END
,shpprod.StateBillByGN AS StateTaxGrossorNet
,BillFreight = CASE
WHEN CAST(shpprod.BillFreight AS INT) = 1
THEN 'Y'
ELSE 'N'
END
,ord.DeliveryTicketNo AS DeliveryTicket#
,co.oaid AS COA_CompanyID
,co.NAME AS CompanyName
,co.Descr AS CompanyDescription
,co.Companyid AS CompanyID
FROM Orders ord
INNER JOIN MOC_Replication.dbo.OrderProduct ordprod ON ordprod.OrderID = ord.OrderID
INNER JOIN MOC_Replication.dbo.ShipToProduct shpprod ON shpprod.ShipToProdID = ordprod.ShipToProdID
INNER JOIN MOC_Replication.dbo.ShipTo shpto ON shpto.ShipToID = ShpProd.ShipToID
INNER JOIN MOC_Replication.dbo.Carrier car ON car.CarrierID = ord.CarrierID
INNER JOIN MOC_Replication.dbo.OrderProdItem ordproditm ON ordproditm.OrderProdID = ordprod.OrderProdID
INNER JOIN MOC_Replication.dbo.ShipToItem shpitm ON shpitm.ShipToProdID = ShpProd.ShipToProdID
AND ordproditm.ShipToItemID = shpitm.ShipToItemID
INNER JOIN MOC_Replication.dbo.Product prod ON prod.ProductID = shpitm.ProductID
INNER JOIN MOC_Replication.dbo.Terminals term ON term.TerminalID = ordproditm.TerminalID
INNER JOIN MOC_Replication.dbo.Customer cust ON cust.CustomerID = shpto.CustomerID
INNER JOIN MOC_Replication.dbo.Company co ON co.Companyid = ord.CompanyID
LEFT JOIN MOC_Replication.dbo.[Address] dest_ad ON dest_ad.addressid = shpto.addressid
INNER JOIN MOC_Replication.dbo.Supplier sup ON sup.SupplierID = ordproditm.SupplierID
LEFT JOIN MOC_Replication.dbo.SupplierTerminal supterm ON supterm.SupplierID = ordproditm.SupplierID
AND supterm.TerminalID = ordproditm.TerminalID
INNER JOIN MOC_Replication.dbo.Vendor vend ON vend.VendorID = sup.VendorID
INNER JOIN MOC_Replication.dbo.vendor vend2 ON vend2.VendorID = car.VendorID
LEFT JOIN MOC_DT.dbo.TCN_Xref tcn ON tcn.recid = (
SELECT MIN(recid)
FROM MOC_DT.dbo.TCN_Xref
WHERE DsLocationCode = CAST(term.TerminalID AS VARCHAR)
AND (sup.SupplierCode LIKE DsSupcode + '-%')
)
WHERE (
sup.SupplierID NOT BETWEEN 9993
AND 9999
)
AND ord.OrderStatus = 'P'
GROUP BY term.StateCode
,dest_ad.[State]
,ord.DestState
,ord.CarrierID
,car.CarrierCode
,car.Descr
,vend2.FederalID
,term.Descr
,dest_ad.[City]
,dest_ad.[state]
,sup.SupplierID
,sup.Descr
,vend.FederalID
,vend.Descr
,ord.PostedDate
,ordproditm.BOL
,prod.ProdCategory
,prod.ProdCode
,prod.Descr
,shpprod.TaxExemptReason
,supterm.PayFlag
,ordprod.BillCode
,cust.CustNum
,cust.CustName
,term.TermCode
,dest_ad.county
,cust.FEIN
,tcn.TerminalNumber
,shpprod.StateBillByGN
,shpprod.BillFreight
,ord.DeliveryTicketNo
,co.oaid
,co.NAME
,co.Descr
,co.Companyid
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply