Performance problems while Looping

• johnw48

  Grasshopper

  Points: 22

  More actions

  November 14, 2003 at 6:03 am

  #84740

  For certain reports I query a group of tables that hold the same data

  for different areas (1,000,000+ rec ea.). The number of tables vary, but it’s not unusual to have 20 to 40 sometimes more. The query’s are simple it’s just that I have to

  run them against each table. I get complaints about how long it takes these

  reports to run. Got any ideas???

  WHILE (SELECT count(Zone) FROM #TmpZones WHERE ctr = 0 ) > 0

  BEGIN

  SELECT @strID = (SELECT MAX(ZoneID) FROM #TmpZones WHERE ctr = 0)

  EXEC("INSERT INTO #TmpPrgInv

  SELECT "+@strID+" AS ZoneID, i.NetworkID AS NetworkID,

  p.programdate AS Programdate,p.name AS ProgramName,

  p.StartTime,p.StopTime,

  SUM(b.length) AS Avails

  FROM break"+@strID+" b, program p,ProgInventory i

  WHERE b.NetworkID = p.NetworkID

  AND b.NetworkID = i.NetworkID

  AND b.ProgramID = p.ProgramID

  AND b.breakdate= p.ProgramDate

  AND b.breakdate between "+@strStartDate+" AND "+@strStopDate+"

  AND b.breaktime between i.StartTime AND i.StopTime

  AND ((b.breakdate - 3) % 7) in ("+@strDays+")

  AND i.ProgGrpID = "+@strPrgGroupID+"

  GROUP BY i.NetworkID,b.breakdate,p.programdate,p.name,p.StartTime,

  p.StopTime")

  EXEC("UPDATE #TmpZones SET ctr = 1 WHERE Zone ="+ @strID)

  END

  John Wright

  
  John Wright

• Jonathan

  SSC-Insane

  Points: 20427

  More actions

  November 17, 2003 at 8:37 am

  #482234

  Cross-posted at:

  http://www.sqlservercentral.com/forum/topic.asp?TOPIC_ID=18224&FORUM_ID=23&CAT_ID=2

  --Jonathan

  
  
  --Jonathan