Performance problems while Looping

Question

Performance problems while Looping

johnw48

Grasshopper

Points: 22
More actions
November 14, 2003 at 6:02 am

#83554

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

Viewing 5 posts - 1 through 4 (of 4 total)

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

Jonathan SSC-Insane Points: 20427 More actions · Answer 1

Merge your tables into one table with a column for Break Number. Your current schema violates Codd's "Information Rule," forcing you to use hacks like this with dynamic SQL.

--Jonathan

johnw48 Grasshopper Points: 22 More actions · Answer 2

Thanks. That’s what I was thinking. This database drives me nuts!! A re-write of the system could be as expensive as the time we loose dealing w/ this !@#$%^& database!!

John Wright

bdohmen SSCrazy Points: 2996 More actions · Answer 3

You can add constraints to each table and them build a view to union the tables together. The end effect should be similar to creating a single table. If the constraints are checked at time of creation, SQL will use the constraints to determine which tables of the view need to be used.

Brian

Jonathan SSC-Insane Points: 20427 More actions · Answer 4

quote:
You can add constraints to each table and them build a view to union the tables together. The end effect should be similar to creating a single table. If the constraints are checked at time of creation, SQL will use the constraints to determine which tables of the view need to be used.
Brian

Yes, I thought of something like this. It's also a hack, but you can create a view to merge the rows into a simulacrum of the correct schema. Something like:



CREATE VIEW v_AllBreaks AS

SELECT 1 BreakNum, NetworkID, ProgramID, BreakDate, Length

FROM Break1

UNION ALL

SELECT 2, NetworkID, ProgramID, BreakDate, Length

FROM Break2

UNION ALL

SELECT 3, NetworkID, ProgramID, BreakDate, Length

FROM Break3

UNION ALL...

Then use the view in a single select statement to get your result set. It would probably be faster than your loop, anyway, and wouldn't require any temporary tables or dynamic sql.

--Jonathan