Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 358
  • Last Modified:

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

I'm using MS Access 2002, VBA with Forms and buttons
0
pgerman
Asked:
pgerman
4 Solutions
 
SNilssonCommented:
No it does not slows it down, it is ignored you can put as many comments you want without a performance hit.
0
 
jmoss111Commented:
I've read that it does slow things down a bit, but not likely enough to notice.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
aikimarkCommented:
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
 
aikimarkCommented:
@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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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
 
pgermanAuthor Commented:
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
 
aikimarkCommented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<
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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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
 
aikimarkCommented:
@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
 
aikimarkCommented:
@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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now