I want optimize the store procedure-It's take more time to excecute by use while loop

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  September 17, 2010 at 8:28 am

  #1223043

  s.chandrahasan (9/17/2010)

  ---

  Dear All,

  Kindly advise me is it correct ,1st part while used data , Part 2 newly i created

  if it is anything wrong in my coding please advise ...

  -- table aliases 'c' and 'b' have appeared from nowhere

  -- but otherwise it's quite close

  insert into #TEMPBLCommodityNew (EqpId, book_no, CName)

  select d.BLconc, b.BLbnoc, iTVF.CName

  from @TEMPBLCommodityunique d

  CROSS APPLY

  (SELECT CAST(CName AS VARCHAR) + ','

  FROM @TEMPBLCommodityunique

  WHERE BLconc = d.BLconc

  and BLbnoc = c.BLbnoc

  FOR XML PATH('')) iTVF (CName)

  -- try this instead: keep the INSERT commented out

  -- until you are sure the rest of the statement works

  --INSERT INTO #TEMPBLCommodityNew (EqpId, book_no, CName)

  SELECT d.BLconc, d.BLbnoc, iTVF.CName

  FROM @TEMPBLCommodityunique d

  CROSS APPLY

  (SELECT CAST(CName AS VARCHAR) + ','

  FROM @TEMPBLCommodityunique

  WHERE BLconc = d.BLconc

  AND BLbnoc = d.BLbnoc

  FOR XML PATH('')) iTVF (CName)

  GROUP BY d.BLconc, d.BLbnoc

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden