locked
string is running slow RRS feed

  • Question

  • Hi All,

    when i check last 2 year invoice with details its running slow (8sec) for 248321 row

    hear is my string where i am wrong?

    SELECT
    A.DeliveryNoteDt,
    (CASE WHEN ExpectedWeight=0 THEN B.Weight * A.Price ELSE B.Pcs * A.Price END) [Net Amount],
    (
    CASE WHEN VAT >0 THEN
    (CASE WHEN ExpectedWeight=0 THEN B.Weight * A.Price ELSE B.Pcs * A.Price END) * (VAT/100) END
    ) [VAT Amount]
    FROM (SELECT
    dbo.tblDeliveryNote.JobCardId,
    dbo.tblDeliveryNote.DeliveryNoteDt,
    dbo.tblDeliveryNote.DeliveryNoteNo,
    dbo.tblDeliveryNote.ExRate,
    dbo.tblDeliveryNote.Price,
    dbo.tblJobCard_Mst.VAT,
    ISNULL(dbo.tblJobCard_Mst.ExpectedWeight,0) ExpectedWeight
    FROM dbo.tblDeliveryNote, dbo.tblJobCard_Mst
    WHERE DeliveryNoteDt BETWEEN '20140101' AND '20150731' AND dbo.tblDeliveryNote.JobCardId = dbo.tblJobCard_Mst.JobCardId) A
    LEFT JOIN
    (SELECT
    dbo.tblDispatch_Master.InvId,
    dbo.tblDispatch_Master.JobCardNo,
    (dbo.tblDispatch_Master.MaxNo) AS Pcs,
    (dbo.tblDispatch_Master.Weight) AS Weight
    FROM dbo.tblDispatch_Master
    ) B
    ON A.JobCardId = B.JobCardNo AND A.DeliveryNoteNo = B.InvId

    Thursday, July 23, 2015 12:40 PM

Answers

  • Deleted
    • Proposed as answer by Naomi N Thursday, July 23, 2015 4:06 PM
    • Marked as answer by Eric__Zhang Friday, July 31, 2015 2:07 AM
    Thursday, July 23, 2015 1:08 PM

All replies

  • Deleted
    • Proposed as answer by Naomi N Thursday, July 23, 2015 4:06 PM
    • Marked as answer by Eric__Zhang Friday, July 31, 2015 2:07 AM
    Thursday, July 23, 2015 1:08 PM
  • First, there is no reason in your query to use a sub-query.  This should do the same thing.

    SELECT 
     A.DeliveryNoteDt, 
     (CASE WHEN ISNULL(dbo.tblJobCard_Mst.ExpectedWeight,0) THEN B.Weight * A.Price ELSE B.Pcs * A.Price END) [Net Amount], 
     (
     CASE WHEN VAT >0 THEN 
     (CASE WHEN ISNULL(dbo.tblJobCard_Mst.ExpectedWeight,0) THEN B.Weight * A.Price ELSE B.Pcs * A.Price END) * (VAT/100) END
     ) [VAT Amount] 
     FROM dbo.tblDeliveryNote
    	 INNER JOIN dbo.tblJobCard_Mst 
    		 ON dbo.tblDeliveryNote.JobCardId = dbo.tblJobCard_Mst.JobCardId
    	 INNER JOIN dbo.tblDispatch_Master
    		 ON dbo.tblDispatch_Master.JobCardId = dbo.tblDeliveryNote.JobCardNo 
    			AND dbo.tblDeliveryNote.DeliveryNoteNo = dbo.tblDispatch_Master.InvId 
    WHERE dbo.tblDeliveryNote.DeliveryNoteDt BETWEEN '20140101' AND '20150731'

    Thursday, July 23, 2015 1:26 PM