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

 
View Poll Results: Which loop method is more efficient?
name appropriate tags, add to collection 1 100.00%
name tag, add to appropriate collections 0 0%
Voters: 1. You may not vote on this poll

Reply
 
Thread Tools Rate Thread Display Modes
Old 02-12-2019, 04:17 PM   #1
snow-raven
Newly Registered User
Gold Supporter
 
Join Date: Apr 2018
Posts: 32
Thanks: 20
Thanked 2 Times in 1 Post
snow-raven is on a distinguished road
Question Building collection loop efficiency

General looping efficiency question for building a collection of controls;

Do you think I'd be more efficient evaluating all of the property tags that I want to add to a collection before moving to the next collection, or would it be more efficient evaluating each tag & adding it to all of the appropriate collections before moving to the next tag?

Example:

Code:
For Each ctl In Me.Controls
    If ctl.Tag = "Project" Or ctl.Tag = "Intervals" Or ctl.Tag = "Lab" Or ctl.Tag = "TestSetUp" (etc...) Then
        colctlsAll.Add ctl, ctl.Name
    End If
    If ctl.Tag = "Collar" Or ctl.Tag = "Intervals" Or ctl.Tag = "Lab" Or ctl.Tag = "TestSetUp" (etc...) Then
        colctlsNewPrjt.Add ctl, ctl.Name
    End If
    If ctl.Tag = "Intervals" Or ctl.Tag = "Lab" Or ctl.Tag = "TestSetUp" (etc...) Then
        colctlsNoCllr.Add ctl, ctl.Name
    End If
Next ctl
Set ctl = Nothing
Or:
Code:
For Each ctl In Me.Controls
    If ctl.Tag = "Project" Then
        colctlsAll.Add ctl, ctl.Name
    End If
    If ctl.Tag = "Collar" Then
        colctlsAll.Add ctl, ctl.Name
        colctlsNewPrjt.Add ctl, ctl.Name
    End If
    If ctl.Tag = "Intervals" Then
        colctlsAll.Add ctl, ctl.Name
        colctlsNewPrjt.Add ctl, ctl.Name
        colctlsNoCllr.Add ctl, ctl.Name
    End If
Next ctl
Set ctl = Nothing

snow-raven is offline   Reply With Quote
Old 02-12-2019, 08:02 PM   #2
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,405
Thanks: 0
Thanked 559 Times in 555 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Building collection loop efficiency

Time the procedures and find out which is faster.

dteStart = Now()
'run procecure
Debug.Print DateDiff("s", dteStart, Now())
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
The Following User Says Thank You to June7 For This Useful Post:
snow-raven (02-13-2019)
Old 02-12-2019, 08:43 PM   #3
theDBguy
Im here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,753
Thanks: 57
Thanked 1,264 Times in 1,245 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Building collection loop efficiency

Also, wouldn’t it be simpler to use a Select Case statement?

__________________
Just my 2 cents...

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.
theDBguy is offline   Reply With Quote
Old 02-13-2019, 06:27 AM   #4
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,547
Thanks: 92
Thanked 1,680 Times in 1,559 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Building collection loop efficiency

I'm of the opinion that you should make the changes to one collection at a time, though it MIGHT be a marginal speed difference.

I say that because of the "collection probes" required. In essence, each collection can be thought of as an array or list. A collection that contains collections is a list that contains lists. It is a nested, double-barrelled search.

If you do the individual INNER additions one at a time, you are repeatedly searching the OUTER collection for a match, then searching the INNER collection. If you instead do the OUTER collection search and, when you find a match, do all INNER items at once (while keeping track of the current collection object, i.e. NOT searching for it - 'cause you found it), I think you do less searching, all other steps being equal.

One man's opinion.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 02-13-2019, 08:10 AM   #5
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,836
Thanks: 35
Thanked 557 Times in 528 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Building collection loop efficiency

No difference.
MajP is offline   Reply With Quote
Old 02-13-2019, 09:49 AM   #6
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,547
Thanks: 92
Thanked 1,680 Times in 1,559 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Building collection loop efficiency

MajP says NO DIFFERENCE, I say MARGINAL DIFFERENCE AT BEST. So in the end analysis, you make your choice and take your chances.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 02-13-2019, 11:04 AM   #7
snow-raven
Newly Registered User
Gold Supporter
 
Join Date: Apr 2018
Posts: 32
Thanks: 20
Thanked 2 Times in 1 Post
snow-raven is on a distinguished road
Re: Building collection loop efficiency

Thanks, all. I don't know why I didn't think of applying a timer. I'll try that when I get a second. (hyuk, hyuk)

Doc_Man, I did nearly implement a select case, but I couldn't think of a way to apply it that didn't include it in each If statement, so it didn't seem that it would add much to efficiency. Admittedly, I have even less experience using select case than with loops in general.

Thanks for the feedback, everyone!

snow-raven is offline   Reply With Quote
Old 02-13-2019, 07:38 PM   #8
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,836
Thanks: 35
Thanked 557 Times in 528 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Building collection loop efficiency

IMO waste of time. So lets say technique A is 75 milliseconds faster 65% of the time? So what. I severely doubt you can measure this with a timer, you will need something far more advanced and any measure will be noise.
MajP is offline   Reply With Quote
Old 02-13-2019, 08:56 PM   #9
NauticalGent
Pristine Curmudgeon
Gold Supporter
 
NauticalGent's Avatar
 
Join Date: Apr 2015
Location: Naples, Italy (EXPAT from Virginia)
Posts: 2,010
Thanks: 445
Thanked 297 Times in 258 Posts
NauticalGent has a spectacular aura about NauticalGent has a spectacular aura about
Re: Building collection loop efficiency

Sounds like a job for Col (Isladogs)...
__________________
It follows then as certain that night succeeds the day, that without normalization, we can do nothing definitive, and with it, everything honorable and glorious. - with apologies to George Washington
NauticalGent is offline   Reply With Quote
Old 02-14-2019, 06:24 AM   #10
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,547
Thanks: 92
Thanked 1,680 Times in 1,559 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Building collection loop efficiency

Like I said, MajP, minimal difference. What was it that P.T. Barnum's barkers used to say in the side-show carnival? "You pays your money and you takes your chances."
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 02-14-2019, 06:48 AM   #11
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,962
Thanks: 114
Thanked 2,996 Times in 2,724 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Building collection loop efficiency

Quote:
Originally Posted by NauticalGent View Post
Sounds like a job for Col (Isladogs)...
Sorry but I'm going to opt out of this one
I also see little difference being likely.

Whilst you can easily measure to centisecond (0.01s) accuracy, doing anything more precise than that needs more complex code.

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
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.
,
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.


Colin (Mendip Data Systems)
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
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.


"As we know, there are known knowns; there are things we know we know. We also know there are known unknowns; that is to say we know there are some things we do not know. But there are also unknown unknowns the ones we don't know we don't know. It is the latter category that tend to be the difficult ones" Donald Rumsfeld
isladogs is offline   Reply With Quote
Reply

Tags
collection , loop , vba

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Loop In Collection & Field calvinle Modules & VBA 2 06-21-2016 07:04 PM
Item not in collection when using field collection morphus Modules & VBA 4 01-14-2011 09:16 AM
*For loop efficiency* access09 Modules & VBA 7 04-27-2009 01:28 AM
Building email using record loop Rainbowhawk General 4 05-15-2008 12:35 AM
Security - allow Query building, but not Table building/modification workaholic_tor General 2 09-24-2006 01:28 PM




All times are GMT -8. The time now is 03:32 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