Slow running code (1 Viewer)

Acropolis

Registered User.
Local time
Today, 14:56
Joined
Feb 18, 2013
Messages
182
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.

Thanks
 

vbaInet

AWF VIP
Local time
Today, 14:56
Joined
Jan 22, 2010
Messages
26,374
It looks like you're doing this all in a recordset so instead of that, index the fields and write a query to return the records you're looking for.
 

Acropolis

Registered User.
Local time
Today, 14:56
Joined
Feb 18, 2013
Messages
182
That's getting a bit beyond me now.

If that involves adding index's to the DB table, then that's going to take a while to get done, as I don't control all the tables in the DB, and trying to get the dev guys to do that will take forever and countless arguments.
 

vbaInet

AWF VIP
Local time
Today, 14:56
Joined
Jan 22, 2010
Messages
26,374
Well that's one major way of improving performance. And convert your code to a query (as previously mentioned).
 

namliam

The Mailman - AWF VIP
Local time
Today, 15:56
Joined
Aug 11, 2003
Messages
11,696
suggest trying the query first, see how that affects performance... will probably gain enough to not have to worry about any indexes
 

Users who are viewing this thread

Top Bottom