Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-08-2015, 11:11 AM   #1
Acropolis
Newly Registered User
 
Join Date: Feb 2013
Location: West Midlands
Posts: 182
Thanks: 17
Thanked 14 Times in 14 Posts
Acropolis is on a distinguished road
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.

Thanks

Acropolis is offline   Reply With Quote
Old 06-08-2015, 11:25 AM   #2
vbaInet
AWF VIP
 
Join Date: Jan 2010
Location: U.K.
Posts: 26,374
Thanks: 0
Thanked 2,423 Times in 2,389 Posts
vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all
Re: Slow running code

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.
vbaInet is offline   Reply With Quote
Old 06-08-2015, 11:31 AM   #3
Acropolis
Newly Registered User
 
Join Date: Feb 2013
Location: West Midlands
Posts: 182
Thanks: 17
Thanked 14 Times in 14 Posts
Acropolis is on a distinguished road
Re: Slow running code

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.

Acropolis is offline   Reply With Quote
Old 06-08-2015, 11:33 AM   #4
vbaInet
AWF VIP
 
Join Date: Jan 2010
Location: U.K.
Posts: 26,374
Thanks: 0
Thanked 2,423 Times in 2,389 Posts
vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all
Re: Slow running code

Well that's one major way of improving performance. And convert your code to a query (as previously mentioned).
vbaInet is offline   Reply With Quote
Old 06-08-2015, 12:00 PM   #5
namliam
The Mailman - AWF VIP
 
Join Date: Aug 2003
Location: Amsterdam/The Netherlands
Posts: 11,388
Thanks: 0
Thanked 800 Times in 787 Posts
namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light
Re: Slow running code

suggest trying the query first, see how that affects performance... will probably gain enough to not have to worry about any indexes

__________________
ruoY yppah namliaM si syawla yppah ot pleh
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.



To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Remember to use the scales (Bottom left) or the thumb (bottom right)

Always looking for new challenges (Amsterdam Area)
namliam is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Qry Running Very Slow Acropolis Queries 4 09-04-2014 02:15 AM
Queries running slow jnolfo Queries 2 12-03-2013 10:16 AM
report running slow jasn_78 Reports 8 05-19-2011 06:16 AM
Question Slow running Database branston General 5 05-27-2009 04:36 AM
Slow Running samer General 1 06-24-2005 03:27 AM




All times are GMT -8. The time now is 05:38 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World