SQL Server Performance

How to avoid trinagular join

Discussion in 'T-SQL Performance Tuning for Developers' started by nikshay, Nov 26, 2008.

  1. nikshay New Member

    Hi All,
    The following is a part of my storedProc:UPDATE
    #TMP
    SET
    ItmRecvdYR1=ISNULL((SELECT SUM(Quantity) FROM dbo.GetAllReorderScannedItemsByDate() WHERE VendorID=t.VendorID AND locationno=t.LOC and DATEPART(YEAR,requisitiondate) = DATEPART(YEAR,t.DateYR1)),0),
    FROM
    #TMP t
    INNER JOIN ReportsData..Locations l
    ON l.LocationNo=t.LOC
    LEFT JOIN ReportsData..RequisitionHeader rh
    ON rh.LocationNo=t.LOC
    LEFT OUTER JOIN dbo.RU_SalesByVendor_Reorders sru
    ON sru.VendorID=t.VendorID AND sru.LocationNo=t.LOC and DATEPART(YEAR,sru.rptdate) >= DATEPART(YEAR,t.DATEYR1)
    LEFT OUTER JOIN dbo.CDC_GetAllReorderCounts_V2() cnt on cnt.VendorID=t.VendorID AND cnt.LocationNo=t.LOC
    AND DATEPART(YEAR,cnt.ReqDate)=DATEPART(YEAR,t.DATEYR1)
    WHERE DATEPART(YEAR,rh.requisitiondate) = DATEPART(YEAR,t.DateYr1) IN THE SUBQUERY I AM USING A TRIANGULAR JOIN (notice WHERE clause in subquery)
    AND TO AVOID THAT I NEED SOMETHING LIKE THE FOLLOWING (notice INNERJOIN in the subquery in the following code). THE WHOLE RESULT SHOULD BE RECEIVED IN AN INTEGER VARIABLE NAMED "ItmRecvdYR1" . IN THE INNER JOIN I HAVE TO SELECT 3 COLUMNS IN ORDER TO JOIN THE ISNULL(SELECT) QUERY. IN THE ISNULL(SELECT) I AM SELECTING 3 COLUMNS, BUT THAT CANNOT WORK IF I AM TO ASSIGN THE RESULT INTO AN INT VARIABLE. SO HOW DO I DO IT?
    UPDATE
    #TMP
    SET
    ItmRecvdYR1=ISNULL((SELECT SUM(Quantity), VENDORID, LOCATIONNO FROM dbo.GetAllReorderScannedItemsByDate() INNER JOIN (SELECT VendorID, LOC, DateYR1 FROM #TMP) TMPData ON VendorID=TMPData.VendorID AND locationno=TMPData.LOC and DATEPART(YEAR,requisitiondate) = DATEPART(YEAR,TMPData.DateYR1)),0),
    FROM
    #TMP t
    INNER JOIN ReportsData..Locations l
    ON l.LocationNo=t.LOC
    LEFT JOIN ReportsData..RequisitionHeader rh
    ON rh.LocationNo=t.LOC
    LEFT OUTER JOIN dbo.RU_SalesByVendor_Reorders sru
    ON sru.VendorID=t.VendorID AND sru.LocationNo=t.LOC and DATEPART(YEAR,sru.rptdate) >= DATEPART(YEAR,t.DATEYR1)
    LEFT OUTER JOIN dbo.CDC_GetAllReorderCounts_V2() cnt on cnt.VendorID=t.VendorID AND cnt.LocationNo=t.LOC
    AND DATEPART(YEAR,cnt.ReqDate)=DATEPART(YEAR,t.DATEYR1)
    WHERE DATEPART(YEAR,rh.requisitiondate) = DATEPART(YEAR,t.DateYr1)
  2. Adriaan New Member

    When running up against a complicated UPDATE query, the best thing to put finger on the problem is to rewrite it as a SELECT query.
    In this case, the original query is quite probably overcomplicated. I would suggest analyzing that UDF to see if it cannot be done inside the main query.
    Once you have the results showing both current value(s) and the new value(s), you can easily rewrite as an UPDATE query.
    Performance-wise one of the main issues could be the DATEPART and YEAR functions, which are preventing SQL from using indexes on those fields - with expressions like that, the entire table must be scanned).

Share This Page