Solved

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

Posted on 2009-04-15
13
344 Views
Last Modified: 2013-11-28
I'm using MS Access 2002, VBA with Forms and buttons
0
Comment
Question by:pgerman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 58

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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
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 58
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 58
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 58
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 58
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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

717 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