Post reply

About the query

  • July 24, 2008 at 5:53 am

    #125029

    Hi,

    can I use join for the following query in respect as optimization.

    SELECT

    QUOTATIONDET9ID,MAINITEMID,ITEMID,Itemdesc,WarehouseID,

    PriceFrom,QTY,UNITID,PriceType,INDICATORID,CURRENCIESID,UNITCOST,

    QuotedPrice,SalePrice,DISCOUNTTYPEID,Discount,DiscountAMT,TOTAL,

    AMCYear,AMCAMT,CMCDuration,CMCAmount,

    ( SELECT ITEMNAME FROM ITEM subitm where subitm.ITEMID = IQD.MAINITEMID ) as ItemasName,

    ( SELECT ItemName FROM item subitm where subitm.ITEMID = IQD.ITEMID ) as ItemName,

    ( SELECT WAREHOUSENAME FROM WAREHOUSE subitm where subitm.WarehouseID = IQD.WarehouseID ) as WAREHOUSENAME,

    isnull((SELECT itm.AvailableQTY FROM ITEM itm WHERE itm.ITEMID = IQD.ITEMID),0) as ItemAvailableQTY,

    isnull(( SELECT itemtypeid FROM ITEM subitm where subitm.ITEMID = IQD.ITEMID ),0) as ItemTypeid,

    (select itemtype from itemtype where itemtypeid IN ( SELECT itemtypeid FROM ITEM subitm where subitm.ITEMID = IQD.ITEMID )) as ItemType,

    (select DiscountType from discounttype distype where distype.DiscountTypeID = IQD.DISCOUNTTYPEID) as DiscountType,

    (select ChargesDESC from charges chr where chr.chargesID = IQD.DISCOUNTTYPEID) as ChargesDESC,

    (SELECT unt.UNITNAME FROM UNIT unt where unt.UNITID = IQD.UNITID) as UnitName,

    (SELECT cur.CURRENCYNAME FROM CURRENCIES cur where cur.CURRENCIESID = IQD.CURRENCIESID) as CURRENCIESNAME,

    (SELECT pt.inoutpricetype FROM vwpricetype pt where pt.OutPriceTypeDETID = IQD.PriceType) as PriceTypeNAME,

    (SELECT INDICATOR FROM INDICATOR di where di.INDICATORID = IQD.INDICATORID) as INDICATOR,

    REQDATE,convert(varchar(12),DELIVERYDATE,103) as DELIVERYDATE,IndustryListID, IQD.REMARKS,

    (SELECT IndustryListDESC from IndustryList Ind where Ind.IndustryListID = IQD.IndustryListID)

    as IndustryListDESC FROM QUOTATIONDET9 IQD WHERE (IQD.QUOTATIONID = 1 )

  • July 24, 2008 at 6:35 am

    #847679

    Correlated subqueries in the select clause (which you have lots and lots of) run multiple times depending on how many rows are in the outer query. You essentially have a cursor in disguise.

    Look at moving subqueries into the from clause and treating them like tables.

    Lastly, please format your code to be a little more readable if you're going to post it here for comments.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 24, 2008 at 6:46 am

    #847688

    Thanks for suggestion.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply