Performance Issues
Found at http://www.vbweb.co.uk/show.asp?id=42

Introduction
After spending weeks or even months creating your application, the last
thing you want to find is that it takes half an hour to load, and uses up
more memory than even windows manages to consume! This tutorial will show
you how to limit memory usage, decrease file size, increase application
performance, and how to measure performance. I hope your applications
benefit from it!


Memory
One disadvantage of visual basic is the lack of control over memory. If you
compile an empty project, and then run it, that program will use almost 2MB
of memory, without you having written a single line of code! This is due to
all VB programs requiring MSVBMV60.dll (or 50 etc depending on the vb
version). Unfortunately, there is not much you can do about the memory usage
of this DLL. However, there are a number of other ways.

1) Ensure that you are referencing only to files that you need. Click
References on the Project menu and see there are any you do not need. You
will always need the following items checked:

Visual Basic For Applications
Visual Basic runtime objects and procedures
Visual Basic objects and procedures
OLE Automation

2) Ensure that your program is only using the components it needs. If you
are using just one or two items from the Microsoft Common Controls, try to
find a seperate control (normally free) from another site. For example, we
have a Progress Bar & Hyperlink control, and VB Accelerator has Image Lists,
TreeViews, ListViews, Outlook bars and more! This will cut down the size of
the controls you have to distribute, as well as the memory usage. Using any
control from the Microsoft Common Controls requires at least another 1MB of
memory.

3) When your program is running, if a form is not likely to be needed again
quickly, use the Unload statement to remove the form from the memory. If you
use the Hide statement, it simply hides the form - it is still resident in
the memory. If you need the form, use the Load statement. This does take
longer than using the Show statement (only after you have shown the form
once. The first time the form is shown, using the Show statement, it also
loads the form into the memory), however, if your program is very memory
hungry, it is one way of cutting down its memory usage.

4) Put infrequently used code into forms or classes, which can be created
and destroyed as required. If you put it in a module, it will be loaded when
your program is first run, whether it is needed or not.

5) Use the right data type declarations. If you have a variable that is only
going to hold numbers 1 to 256, use the Byte data type, rather than Integer
or Long. This will use 1 byte of memory instead of 4 bytes. Declaring one
variable as a byte instead of Long will not make much difference, but if you
have a large number of variables, it can help cut memory usage down. Even if
the effects are not noticeable, it is a good habit to get into.

Have a performance tip that you would like mentioned? Email us with it.


Compiling
The release of VB5 at last gave VB programmers a chance to compile their VB
applications to Native code. When you compile your VB project, and decide
whether to compile as Native or P-code, and Fast or Small, there are a
number of things to consider. To change any of these settings of your
program, click the Compile tab on the project properties dialog.

The first is speed. How important this is depends on the type of program you
are writing. Below is some results on a PIII 500Mhz, using the code below as
the 'stuff to do'.

For I = 0 To 1000000 Step 0.1
A = Sqr(I) + A
z = A / 32.34
Next

Compile Mode Time Taken
Native Code - Fast Code 8.077
Native Code - Small Code 9.1745
Native Code - Fast Code (Favour Pentium) 8.7876
Native Code - Small Code (Favour Pentium) 8.9505
Native Code - No Optimization 9.4461
P-Code 9.5598
Run-time 10.0695

As you can see, P-code is significantly slower than when compiling to Native
Code.

The other is size. When you are distributing your program using CD-rom, this
is not very significant. However, if you are making your program available
over the internet, things are different. Below is the different sizes of my
Developers Pad program when compiled in the different modes.

Compile Mode Size
Native Code - Fast Code 816KB
Native Code - Small Code 772KB
Native Code - No Optimization 876KB
P-Code 472KB

As you can see, P-code is also significantly smaller than when compiling to
Native Code. What you will have to decide on, is which mode you want to use.
This will all depend on how you are distributing your program, and how
important speed actually is. For simple things such as a Word processor,
speed would not be very important (unless you perform a large amount of
processing as the user is typing), and you may opt for the smaller
executable file.

It is also worth noting that compiling to P-code is much, much quicker than
compiling to Native code.

*** If you want to detect as many possible errors without compiling, start
your program by pressing Ctrl+F5 instead of F5, as this will compile your
program fully. You can also do this by unchecking compile on demand in the
options dialog. Thanks to Magik for pointing this out to me. You can visit
his website @ magikweb.cjb.net ***


Increasing Speed
Have a performance tip that you would like mentioned? Email us with it.

There are a large number of factors that affect the speed of your program.
Below is a list of some of them.

Pass variables ByVal instead of ByRef (the default).
This way, Visual Basic does not have to send the address of the variable,
and retreive it after the procedure has been called. For more information on
this, click here.

Use the magic $.
When using string functions that have an alternative function with a $ after
it, use that instead. Click here for why!

Don't calculate the same thing over and over again.
For example, if you constantly want to know the length of a string that does
not change, save the length to a variable, instead of constantly calling the
Len function.

Don't ask for the same thing over and over again.
When constantly requesting a property (ie the Text property of a
RichTextBox), which you know will not change, save the value to a local
variable, and use that instead. It is much quicker! For example
sBuffer = RichTextBox1.Text
For i = 0 To Len(sBuffer)
sTemp = Mid$(sBuffer, i, 1)
Msgbox sTemp
Next

Use Select Case instead of multiple If...ElseIf statements.
This is much quicker.

Tell the control to stop redrawing.
If you a performing a lot of operations on a control at once (for example,
formatting different parts of text in a Rich Text box), use the WM_SETREDRAW
constant with the SendMessage Win API call, or the LockWindowUpdate API call
to stop the control constantly redrawing. This makes your program look
tidier (so that the control is not constantly flickering as your code runs),
and improves performance (because the control does not have to constantly
redraw). Here is how to use the two Win API calls:
For using SendMessage:
Private Declare Function SendMessage Lib "user32" Alias "SendMessageA"
(ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As
Any) As Long
Private Const WM_SETREDRAW = &HB
'// Use this for stopping redrawing:
SendMessage TextBox1.hWnd, WM_SETREDRAW, False, 0&
'// Use this to redraw again:
SendMessage TextBox1.hWnd, WM_SETREDRAW, True, 0&
TextBox1.Refresh
For using LockWindowUpdate
Private Declare Function LockWindowUpdate Lib "user32" Alias
"LockWindowUpdate" (ByVal hwndLock As Long) As Long
'// Use this for stopping redrawing:
LockWindowUpdate TextBox1.hWnd
'// Use this to redraw again:
LockWindowUpdate 0
LockWindowUpdate is easier and quicker, however, if the form itself is
requested to do so, it will (ie if the form in minimized and then maximized
again), and the request for no redrawing will be lost.

Use the With statement.
If you have a loop where you reference a control or object over and over,
use the With/End With statement block. It will stop VB from constantly
looking up the address reference to the object each time it is encountered.
VB will look up the reference only once at the beginning of the block saving
redundant lookups. (Thanks to Jeffery Bogusz)

Use Labels instead of TextBoxes, unless the user needs to type into the
TextBox. If you like the look of the TextBox you can reproduce it by setting
a Labels border to Single. (Thanks to Sirius Lee)

Use control arrays for any related controls, especially with Label controls
that just display text and do nothing else. This saves memory, and increase
efficiency. This also works really well with Option Controls contained in a
Frame. Thanks to Sirius Lee.
Set the AutoRedraw property to False.
Unless you have pictures that are updated often, you can set the Autoredraw
to False and as long as you placed controls correctly, no overlapping, set
the Clip Controls option to False. (Thanks to Sirius Lee)
Use the Static variable type only where necessary
Instead, use the variable at the module level (i.e. declare it as private in
a form or module). This increases the speed of operation with this variable.
The advantage of using of variable Static type on level of procedure is the
readability of code, the disadvantage is the speed of operation with this
variable. Therefore it is better to use variables declared in module (the
value of this variable remains the same at all procedures in this module).
(Thanks to Lada Simicek)
Only use public variables where necessary
If your variable does not need to be accessed outside the form/module etc,
declare it as private! (Thanks to Berton Christophe)

Reduce frequent calls to the same procedure and replace it by using code.
When your program frequently calls the same procedure (cycle), the speed
goes down. In this case, it is recommended to write code of called procedure
directly in the cycle. The advantage of it is the faster speed, the
disadvantage is worse readability of the code. (Thanks to Lada Simicek)
Use constants instead of variables wherever possible.
If you want to use the values that will not be changed, use constants.
(Thanks to Lada Simicek)
Use early-binding.
Don't declare as object if at all possible. You can use polymorphism when
using similar classes.
Have a performance tip that you would like mentioned? Email us with it.

The magic $
Many of you will have noticed that there is both a Mid and a Mid$ function,
as well as Left$, Right$, and a number of other functions. However, it is
not obvious what the difference actually is. When you use the Mid function,
it returns a string as a Variant data type. As you would normally save the
result to a string, not a Variant, Visual Basic then has to convert the
Variant into a string. When you use the Mid$ function, it returns a string
in a String variable. This saves Visual Basic the trouble of converting the
Variant to a String, and thus increases performance. When you perform long
loops of code, using Mid, and the other string functions, this can make
quite a difference. I have yet to find an instance when you would actually
want the Mid statement to return a Variant, and not a String, so I am not
sure why Microsoft have done this. To see a list of all the procedures that
have a $ (string) version as well, open the object browser (F2), enter $
into the find box, and press Find. This will give you a list of about 25
procedures where this is the case.


Measuring Performance
When you make changes to your program, hoping to make it run faster, you
need a way to accurately measure the time between certain events. You can do
this by using the QueryPerformanceCounter and QueryPerformanceFrequency to
measure this. As an example, lets use the following code:


Private Declare Function QueryPerformanceCounter Lib "kernel32"
(lpPerformanceCount As Any) As Long
Private Declare Function QueryPerformanceFrequency Lib "kernel32"
(lpFrequency As Any) As Long
'// timer frequency
Dim secFreq As Currency

Private Sub cmdTimeIt_Click()
Dim sec As Currency, secOut As Currency
Dim I As Long
'// start the timer
ProfileStart sec
'// do something
For I = 0 To 1000000
DoEvents
Next
ProfileStop sec, secOut
MsgBox secOut
End Sub
Sub ProfileStart(secStart As Currency)
'// if we do not have the timer frequency, then get it
If secFreq = 0 Then QueryPerformanceFrequency secFreq
'// get the current value
QueryPerformanceCounter secStart
End Sub

Sub ProfileStop(secStart As Currency, secTiming As Currency)
'// get the time passed
QueryPerformanceCounter secTiming
If secFreq = 0 Then
'// there is no high-resolution timer available.
secTiming = 0
Else
'// calculate the time taken from the start value, finish value, and
the timer frequency.
secTiming = (secTiming - secStart) / secFreq
End If
End Sub

As you can see, we pass the ProfileStart procedure an empty Currency
variable (sec) to hold the start value. When the code you want to run is
finished, call the ProfileStop procedure with the start value (sec), and
another empty Currency variable (secOut). The value contained in secOut is
the time taken. You usually get the time taken, accurate to the nearest
microsecond. As you can see, this is invaluable when trying to see the
changes in performance as you change your code.

We can also use GetTickCount API for Measuring Performance. The following
code gives you an example.

Private Sub TimeOperation()
Dim I as Long
Dim x as Long
Dim Y as Long
Dim Z as Double

X = GetTickCount()
For I = 0 to 1000000
Z = SIN(i)
Next I

Y = GetTickCount - x
MsgBox "This operation takes " & Y/1000 & " seconds to finish'
End Sub

Thanks to Georgi Ganchev for this tip.

For more information on this, and other methods for timing code, search for
'HOWTO: Use QueryPerformanceCounter to Time Code' in the MSDN library.


SQL vs DAO
Thanks to Lada Simicek for this (and many other) tips.

How much faster is the selection of data from Microsoft Access database
using SQL statement than DAO common statements?

Many programmers do not take advantage of using SQL statements when working
with DAO databases. This is generally because of the work involved learning
of SQL language, compared to using common DAO statements. On the other hand
SQL is very much faster and more powerful then DAO statements.

This short example shows how much is SQL statement faster then DAO
statement. This program will find each author's name from Biblio.mdb (you
should find this file in VB directory) that begins on the letter "S" and
whose year of born was after 1930. You can simply compare the speed using
SQL statement or DAO statement.

Be sure that in your application directory is located the file Biblio.mdb or
change the line:

Set dbData = dbWork.OpenDatabase (App.Path & "\Biblio.mdb")

In Project|References select Microsoft DAO 3.51 Object Library or Microsoft
DAO 2.51 Object Library.

Add two buttons to the form, and set their Caption properties to the
following:

Command1 - DAO
Command2 - SQL

Then, copy this code:

Private dbWork As Workspace
Private dbData As Database
Private dbTabl As Recordset
Private dblTime As Double
Private dbTablSQL As Recordset

Private I as Long

Private Sub Command2_Click()

Debug.Print "_____________________________________________"
'//Start
dblTime = Timer
Set dbTablSQL = dbData.OpenRecordset("SELECT * FROM " _
& " [Authors] WHERE [Author] Like 'S*' And [Year Born] > 1930 ")

dbTablSQL.MoveLast
dbTablSQL.MoveFirst
For I = 0 To dbTablSQL.RecordCount - 1
Debug.Print " Au_ID: " & dbTablSQL.Fields("[Au_ID]") _
& ", Author's name: " & dbTablSQL.Fields("[Author]") _
& ", Born: " & dbTablSQL.Fields _
("[Year Born]")
dbTablSQL.MoveNext
Next I
'//Stop
dblTime = (Timer - dblTime)
Debug.Print "SQL time: " & dblTime
dbTablSQL.Close

End Sub

Private Sub Command1_Click()

Debug.Print "_____________________________________________"
'//Start
dblTime = Timer

dbTabl.MoveLast
dbTabl.MoveFirst
For I = 0 To dbTabl.RecordCount - 1
If Left$(dbTabl.Fields("[Author]"), 1) = "S" And _
dbTabl.Fields("[Year Born]") > 1930 Then
Debug.Print " Au_ID: " & dbTabl.Fields("[Au_ID]") _
& ", Author's name: " & dbTabl.Fields("[Author]") _
& ", Born: " & dbTabl.Fields("[Year Born]")
End If
dbTabl.MoveNext
Next I
'//Stop
dblTime = (Timer - dblTime)
Debug.Print "DAO time: " & dblTime

End Sub

Private Sub Form_Load()

Set dbWork = DBEngine.Workspaces(0)
Set dbData = dbWork.OpenDatabase _
(App.Path & "\\Biblio.mdb") '//Set the path where _
the file Biblio.mdb is located
Set dbTabl = dbData.OpenRecordset("Authors", _
dbOpenDynaset)

End Sub

Now, try it several times and compare the results.