Slow running code
I have some code (just written this evening) which I am using to check some alert that come through from data loggers.
I am getting all the divide ID's I need to check (c970) and then looping through each one checking for the alerts.
There are 5 alerts I am looking for, numbered 1,3,4,7 and the total number. This will be run once a day for the previous day.
The alerts are stripped out of a header and stored in their own table with a few other bit of information, but only 11 columns.
Everything is working, but it is running very slowly, I have it running at the moment, its been going for about 15 minutes and only got through 75 of the 972 to check!
There is a lot of data in the table it is getting the data from, c 4.1million records at present, this increases daily by 20k or so records. There is nothing i can do about that.
I originally used a count for each alert I was looking for, but that was even slower, so I changed it and am now using a do.recordset for each alert I am looking for which is counting the appropriate records based off alert type, device id and date, I then assigning this to an INT variable before wiring the count into another table as a result against each logger for each day.
Is there a better way I could do this to speed it up, as the way it is at the moment is going to take forever to run through it all.