Solved

Does having extra code that is all commented out within funtions or subs slow down your db?

Posted on 2009-04-15
13
339 Views
Last Modified: 2013-11-28
I'm using MS Access 2002, VBA with Forms and buttons
0
Comment
Question by:pgerman
13 Comments
 
LVL 8

Assisted Solution

by:SNilsson
SNilsson earned 20 total points
ID: 24149311
No it does not slows it down, it is ignored you can put as many comments you want without a performance hit.
0
 
LVL 18

Assisted Solution

by:jmoss111
jmoss111 earned 20 total points
ID: 24149571
I've read that it does slow things down a bit, but not likely enough to notice.
0
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 20 total points
ID: 24149620
It's going to slow it down two ways:
1.  On compile, because the compiler needs to see it as a comment and ignore it.
2. Loading source code, which will take slightly longer and use more memory.
  I don't though unless you are really excessive that you will notice.  In all likely hood, with today's processors, your probably talking fractions of a second.
JimD.
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 45

Accepted Solution

by:
aikimark earned 65 total points
ID: 24150102
It WILL slow down your code.  You will not likely notice this performance degradation unless your comments (and blank lines) are inside a loop or you have a LOT of comments relative to actual VBA statements.

Despite the menus, Compile does not actually compile code.  It is still interpreted when it is executed. :-(
Note: This interperative condition does not matter whether you are running the code in an MDB or MDE.

======================
What else is going on in your database/application?

Have you measured the performance?

Have you identified where the performance is bad?

Is this a performance condition that came on suddenly or has been growing worse over time?
0
 
LVL 45

Expert Comment

by:aikimark
ID: 24158858
@pgerman

Thanks for the points.  I'm glad we could help answer your question.

To help us answer your future questions, please let us know how we might have earned an Excellent (A) rating.  Although we are subject matter experts, we realize that there is always room for improvement, just like performance and tuning endeavors.  However, in order for us to improve, we need to know were/how we are deficient.  It is your turn to help us.

I thank you in advance,
aikimark
0
 
LVL 57
ID: 24158925
aikimark,
<<Despite the menus, Compile does not actually compile code.  It is still interpreted when it is executed. :-(
Note: This interperative condition does not matter whether you are running the code in an MDB or MDE.>>
Sorry, I don't believe that is correct.  "Compile" in a VBA project does compile code, but it is an incremental compile and not a full compile down to machine code.  There is still some interpretation done at run-time.
Second thing is there that there is no reason in the world for the complier to include comments in the p-code and from what I'm aware of, it does not.  This is why in a MDE format (where all source code is stripped out), it is impossible to retrieve comments from the p-code that's left.
 A comment is a no-op and it would be plain silly to include that in the p-code.
JimD.
0
 

Author Comment

by:pgerman
ID: 24159005
Sorry, I'm relatively new to the site.

I assigned the points as I did because I wasn't really sure who was 100% correct, but felt satisfied enough to move on.

I'm not sure if there is anyway to see other questions i have posted, but I am having some other more difficult issues that I have posted in other questions.

Thanks for the help everyone.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 24159027
Jim

I based my comments on performance tests I've done against both 'compiled' and interpreted VBA code in an MS-Access environment.

Try this.  Time some looping code and then edit the code to make it appear on one line (separating the statements with a colon ":" character) and see the difference in the execution time.

Even though the comment line (or comments following a VB statement) are discarded, the line-by-line parser/syntax-checker actions are still evident.
0
 
LVL 57
ID: 24159132
<<
Try this.  Time some looping code and then edit the code to make it appear on one line (separating the statements with a colon ":" character) and see the difference in the execution time.
>>
  Are you saying to include a comment?  I can't see where that would be a vlaid test anyway; you going from one structure to another.  You would need to keep it the same making the only difference the inclusion of a comment or not.
JimD.
0
 
LVL 57
ID: 24159270
FWIW, I just went and tested this.  The result; no difference.  Attached is the code I used.
JimD.

Sub TestComment()
 
  Dim i As Long
  Dim k As Long
  
  i = 0
  
  Call StartTimer
  
  For k = 1 To 1000000
     ' comment 1
     ' comment 2
     i = i + 1
  Next k
  
  Debug.Print StopTimer()
  
End Sub
 
Option Compare Database
 
Private Declare Function timeGetTime Lib "winmm.dll" () As Long
 
Private lngStartTime As Long
 
Public Sub StartTimer()
 
  lngStartTime = timeGetTime()
  
End Sub
 
Public Function StopTimer() As Long
 
  StopTimer = timeGetTime() - lngStartTime
  
End Function

Open in new window

0
 
LVL 45

Expert Comment

by:aikimark
ID: 24159276
@pgerman

The points distribution wasn't the issue.  You gave the accepted solutions a "B" grade, which indicates that we didn't completely answer your question, or perhaps only helped you arrive at an answer.  I wanted to know how we might improve future answers.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 24161573
@JimD

I get very similar times for all three, but they are inconsistent.  I can't account for what I'd seen in the earlier tests I'd run.  Maybe I was testing under Access97 and the Compile operation actually saves the P-code in later versions.

What is so strange is the different times I got depending on the order of the sections.  Quite unexpected.  I've posted my test code in the snippet.

=============
@pgerman

I retract my assertion that the VBA code runs interpreted.

If you are having some performance issues, you can post a more general question about your performance problem and ask for guidance in finding and fixing the causes.  

If you post a performance question, you should supply the following information with the original question post:
* What else is going on in your database/application

* Have you measured the performance

* Have you identified where the performance is bad

* Is this a performance condition that came on suddenly or has been growing worse over time

* What is the configuration of users and databases (front-end, back-end, attached ODBC/ISAM tables, etc.)

Option Compare Database
Option Explicit
 
Private Declare Function timeGetTime Lib "winmm.dll" () As Long
Private Declare Function timeBeginPeriod Lib "winmm.dll" (ByVal uPeriod As Long) As Long
Private Declare Function timeEndPeriod Lib "winmm.dll" (ByVal uPeriod As Long) As Long
 
Private lngStartTime As Long
 
Sub TestComment()
 
  Dim i As Long
  Dim k As Long
  
  timeBeginPeriod 1  'force 1ms granularity
  i = Int(Rnd * 2)
  
  i = 0
  
  Call StartTimer
  
  For k = 1 To 1000000
     i = i + Int(Rnd * 2)
  Next k
  
  Debug.Print "Without Comments", StopTimer()
  '========================================
  i = 0
  
  Call StartTimer
  
  For k = 1 To 1000000
     ' comment 1
     
     i = i + Int(Rnd * 2)
     ' comment 2
  
  Next k
  
  Debug.Print "With Comments", StopTimer()
  '========================================
  i = 0
  
  Call StartTimer
  
  For k = 1 To 1000000: i = i + Int(Rnd * 2):  Next k
  
  Debug.Print "Single line", StopTimer()
  
  timeEndPeriod 1  'end 1ms granularity
  
End Sub
 
Public Sub StartTimer()
  lngStartTime = timeGetTime()
End Sub
 
Public Function StopTimer() As Long
  StopTimer = timeGetTime() - lngStartTime
End Function

Open in new window

0
 
LVL 57
ID: 24162482
<<I get very similar times for all three, but they are inconsistent.  >>
  Well remeber that your CPU is dealing with multiple tasks and the timing loop I had didn't take any of that into account.  On a small scale, you'd see some variation because of task switching.  If you pushed the loop out more, you should get less.
<< Maybe I was testing under Access97 and the Compile operation actually saves the P-code in later versions.>>
  There were some changes between A95 and A97 and again with A2000 in regards to the VBA project, but fundamentally, VBA has always worked this way.
  Where you may have seen a difference is in dealing with un-compiled vs complied code.  VBA when it loads code will compile on the fly if the code is uncompiled.  With A95 and A97, there was a difference when VBA decided to carry out this operation and how (whole project vs individual modules) vs later versions of Access.
JimD.
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question