Avoiding query in loop - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Avoiding query in loop

Author

Topic

djfiii
Starting Member

13 Posts

Posted - 2008-06-09 : 11:43:17

Hello all,

I currently have an asp script that is generating a 12 month rolling report. From asp I'm running a for loop with 12 iterations, each one sending the following query:

select count(a.aReportDate) as ttl from findings f left outer join audits a on a.aID = f.auditID 
where f.findingInvalid <> 1 and month(aReportDate) = " & Mo & " and year(aReportDate) = " & Yr

where the Mo and Yr variables are incremented accordingly.

I actually have 4 sets of data being pulled back to populate a graph, so this results in 48 queries with each page load! Obviously not ideal. So I'm hoping to reduce this to 4 queries. I was playing with the following in enterprise manager:

DECLARE @DT DATETIME
DECLARE @CNT INT
SET @DT = '10/31/07'
SET @CNT = 1
WHILE(@CNT < 12)
BEGIN
	select count(a.aReportDate) as ttl from findings f left outer join audits a on a.aID = f.auditID 
	where f.findingInvalid <> 1 and month(aReportDate) = month(@DT) and year(aReportDate) = year(@DT)

SET @CNT = @CNT + 1
END

I haven't yet added any logic to increment the date, but my concern is that it looks like it is returning 12 separate results. Is there any way to combine this all into one resultset that will be passed back to my asp script? Hopefully this makes sense?

Suggestions on a completely different approach would also be welcome.

Thanks!

Sunkist
Starting Member

4 Posts

Posted - 2008-06-09 : 13:06:48

Normally in this case I would build a stored procedure that would be called from my asp. I would fill a temp table with 12 records, one for each month and year, then do a join on it with the query you have in your loop there.

Example:

select count(a.aReportDate) as ttl from TempDate T, findings f left outer join audits a on a.aID = f.auditID where f.findingInvalid <> 1 and month(aReportDate) = T.Month and year(aReportDate) = T.Year

Hope this helps.

djfiii
Starting Member

13 Posts

Posted - 2008-06-09 : 14:44:11

yep, I did a bit of reading on stored procs and temp tables and threw together what I need. I'm now executing 4 sp's that each return a resultset of 12 records. the difference is night and day - crunch time was previously about 5 seconds, now it's instantaneous. thanks for the direction!!

David

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources