Solved

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

Posted on 2009-04-15
13
336 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 45

Accepted Solution

by:
aikimark earned 65 total points
Comment Utility
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
Comment Utility
@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

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:pgerman
Comment Utility
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
Comment Utility
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

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<
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

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
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
Comment Utility
@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
Comment Utility
@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

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

744 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now