How can I rewrite this in better performance

• Eirikur Eiriksson

  SSC Guru

  Points: 182920

  More actions

  May 4, 2015 at 2:38 am

  #1795304

  MotivateMan1394 (5/3/2015)

  ---

  Hi

  I dont want that you rewrite these queries.Only For you Inform :

  I Tried 2 Times but the result was not very efficient .

  I need an idea or ... I dont Know.

  Perhaps some queries in an office are very important and user must wait for the result . ...

  Do you have any Idea :

  ----------------------------------------------------------------------------------------------------

  Without a rewrite of the scalar functions there aren't many options, almost any kind of sorting is bound to affect the performance so probably the only viable option is to select the set unsorted into a temporary table and do a sorted select from there.

  😎

  Select

  *

  INTO #TEMP_RESULT

  From

  (

  SELECT * , dbo.Func_CheckStatus(PAmount, DiffAmount, RealAmount) AS CheckoutStatus

  FROM

  ( SELECT * , (PAmount - RAmount - ISNULL(RealAmount,0)) AS DiffAmount

  FROM

  ( SELECT

  dbo.View_FullPaymentInfo.*

  ,SHC.ShipmentCode

  ,dbo.Func_CalcAmount(CStatus,OStatus,Amount) AS RealAmount

  ,dbo.Func_GetPAmount(CId, 0) AS PAmount

  ,dbo.Func_GetRAmount(CId, 0) AS RAmount

  FROM dbo.View_FullPaymentInfo WITH (READUNCOMMITTED)

  Cross Apply

  (SELECT Max(Code) ShipmentCode

  FROM Table_OrderSh WITH (READUNCOMMITTED)

  WHERE OId = View_FullPaymentInfo.OId

  AND dbo.Table_OrderSh.IsActive <> 0 ) SHC

  ) AS Paymenttmp1

  ) AS Paymenttmp2

  ) AS OrderPayment;

  SELECT

  *

  FROM #TEMP_RESULT

  ORDER BY CId DESC;

  --ORDER BY CheckoutStatus DESC --- This Ordering Kill Perfomrnace,me and user Crying

  --ORDER BY PAmount DESC --- This Ordering Kill Perfomrnace

  --ORDER BY RAmount DESC --- This Ordering Kill Perfomrnace

  DROP TABLE #TEMP_RESULT;

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  May 4, 2015 at 2:39 am

  #1795305

  All of those functions are likely to cause performance problems if they're called within another select. Data-accessing scalar functions should be avoided whereever possible and especially avoided within another query.

  Try rewriting the functions as in-line table-valued functions (NB, inline, not multistatement) or remove them entirely.

  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