Optimise Looping Code

Hi,

the code below works but takes a while to pull the results through. If I take the patients section out, it comes back straight away but once it's added, it takes seven seconds. There are approx 3k patient records.

Is there a quicker way of doing this?

Without seeing your actual SQL it's difficult to say for sure, but I'd be surprised if there wasn't a way you could combine those multiple SQL calls into one, that grouped the records in a way that you could loop through and produce the same output.

That would be, by far, the main performance improvement you could make. How many calls to the database is it currently making?

One other thing that would probably make a noticeable, if relatively minor, difference: don't loop through recordsets. Use getRows() to turn it into an array and loop through that. Looping through recordsets is bad, as is referencing values straight out of one. Every time you reference a value using oRs("myfieldname"), it's gotta go searching through the big, cumbersome recordset object for the value. Get values out into variables and use those.

EXEC spGetClusters : "SELECT ClusterID,ClusterDesc FROM tblCluster ORDER BY ClusterID"

EXEC spGetTeams : "SELECT TeamID,TeamDesc FROM tblTeam WHERE ClusterID=@cID ORDER BY TeamDesc"

EXEC spGetClinicians : "SELECT ClinicianID,Surname + ', ' + Forename AS cName FROM tblClinician WHERE TeamID=@tID ORDER BY Surname"

EXEC spGetPatients : "SELECT NHSNo FROM tblPatient WHERE ClinicianID=@cID"

I'll try the getRows. Thanks.

Ok - so I've used the GetRows method but it's still taking 7-8 seconds to display the results.

Ah well - thanks for your help. I learn something new everyday!

Let's say I have a table containing {NORTH, SOUTH, EAST, WEST}. I'll display this as follows {COUNTER, HEADING, NAME, ID}

1 : 0 : NORTH: 3
2 : 0 : SOUTH : 5
3 : 0 : EAST : 7
4 : 0 : WEST : 8

For each section under NORTH in another table, I have to do similar i.e.

5 : 1 : LIVERPOOL : 3
6 : 1 : MANCHESTER : 4
7 : 1 : LEEDS : 5

... and so on for 2-3 more levels... does that make sense? Then for Liverpool I'd have:

8 : 5 : KIRBY: 3
9 : 5 : PENNY LANE: 4
10 : 5 : ALLERTON: 5

SQL runs fine.

It's when it writes over 3k lines that the process slows down!