Solved

Optimize Form Load/Show

Posted on 2001-07-18
12
525 Views
Last Modified: 2008-02-01
I'm using VB6 against an SQL Server 7 database.
My boss would like to optimize the app or at least speed up the executable so the launching of the main form or form.show would be quicker.

The program is written with ADO so there are a lot of recordsets; the DSN is hard-coded so I don't think that's taking up too much time to make the connection.
There are some OLE objects so I know they tend to eat up memory.

Can anyone give some basic tips or tricks to increase the startup speed?
Is there somewhere that I could go thru a checklist to verify some coding standards.

Here's some more detail:

My main startup form is actually a bas module that declares and sets all the ADODB recordsets; i.e. Instantiate and set References to about 20 recordsets.
(My boss did a lot of Access programming, so it's similar to that where the bas module gets executed first.)

Our Form_Load simply contains all of the text boxes that should be enabled/disabled and command buttons that should be visible/invisible.

I think the Form_Initialize and Form_Activate methods get called before Form_Load, right?
So, couldn't I jam some code in there to speed up the launch of the application?
Maybe put the setting of the recordsets in the Form_Initialize event would speed it up?

I just want to make sure that my code is optimized; I'm running Windows NT, PIII 350, and 128 MB RAM, and it seems very slow to display the main form.
Trouble is, the users only have 64 MB RAM.

I know this probably isn't enough info to tell me where to place the code, but I was thinking it could be optimized.

Any help would be appreciated.
Thanks in advance.
John
0
Comment
Question by:jtrapat1
  • 2
  • 2
  • 2
  • +4
12 Comments
 
LVL 8

Expert Comment

by:Dave_Greene
Comment Utility
<<the DSN is hard-coded so I don't think
that's taking up too much time to make the connection.>>

This doesn't matter that much!  <milliseconds>

One good way to save on boot up time is to only load the forms that you need at startup.  

Now, move as much code as possible to a 'startup' module.  this code isn't processed until you access it.  where with a form, each line of code is process as it loads.
0
 
LVL 5

Expert Comment

by:rkot2000
Comment Utility
1.     You can use command objects to return multiple recordsets.

2.     If it?s a static recordset you can cache it on the client,
save rs to the file on exit and read from a file on startup.

3.     Try to load only necessary recordsets.(2 or 3 not 20)

4.     You can put you application in the windows start up folder,
so it will be load but hidden during windows boot and use a shortcut to do a fake start (send an api message to show main form)

You can fake load speed
Sub Main()
MainForm.Show
Doevents
MainForm.Enabled =false

Load your recodrsets and show progress bar to users

MainForm.Enabled =true
0
 
LVL 8

Expert Comment

by:Dave_Greene
Comment Utility
Put all of the useless controls, like labels and such into control arrays...

lblStatic(x)
0
 
LVL 5

Expert Comment

by:rkot2000
Comment Utility
or you can try to use windowless controls from microsoft.
0
 
LVL 4

Expert Comment

by:amit_panjwani
Comment Utility
I really do not see any reason that you open up all the recorsets at the same time.

Open whatever recordsets on need basis - as and when needed.


Alternatively if you want to give the impression that application is loading faster show splash screen and use doevents

meanwhile you can also use a progressbar on splash screen to help user what recorsets are being loaded. This would help in debugging if there is any future problem due to database changes.

hope it helps

amit

0
 
LVL 8

Expert Comment

by:DennisBorg
Comment Utility
>I think the Form_Initialize and Form_Activate methods get
>called before Form_Load, right?
>So, couldn't I jam some code in there to speed up the
>launch of the application?
>Maybe put the setting of the recordsets in the
>Form_Initialize event would speed it up?

FYI, the order of those events are:

   1) Initialize
   2) Load
   3) Activate

Placing code in the Initialize event rather than the Load event is not going to make that code run faster. The issue of code speed is not dependent upon the event in which it is used, but rather dependent upon the nature of the code itself.


Here are my suggestions as to speed things up.


You can give the *impression* of faster load times by making the form visible prior to it being completely loaded. You would do this by placing the following code in your form's Load Event:

   Me.Visible = True
   DoEvents

This causes the user to feel as if the application was loading faster.



Another thing you can do is to reduce the number of recordsets which are opened when the form loads.

Are all 20 recordsets needed immediately? Or are there some which may or may not be used, depending upon the actions of the user?

It may be possible to defer opening certain recordsets until the time when it is actually required. Perhaps a certain recordset is not needed until the user clicks on a certain button.

For example, suppose you have a form-level recordset variable (rsOrders) which is not needed until the user clicks on the button btnOrders. Instead of opening rsOrders in the form's Load event, it may be more advantageous to open that recordset when the button is clicked on. Then if the user never clicked on that button, then you would not be opening the recordset unnecessarily.

The button's code then might look something like:

   Private Sub btnOrders_Click()
      ' rsOrders is declared in the
      ' General Declarations section of the form
      If rsOrders Is Nothing Then
        ' *** Code here to open the rsOrders Recordset
      End If

      ' *** Code here to use the data in rsOrders
   End Sub



Another way to gain a little speed is to use the With statement. See your VB documenation for more info about the With statement.

For example, consider the following code:

   Dim I As Integer

   List1.Clear
   For I = 1 To 1000
      List1.AddItem CStr(I)
      List1.ItemData(I) = I * 2
   Next I

The object 'List1', in this example, has to be dereferenced 2001 times. By using the With statement, we can knock that down to only one time:

   Dim I As Integer

   With List1
      .Clear
      For I = 1 To 1000
         .Add Item CStr(I)
         .ItemData(I) = I * 2
      Next I
   End With

This would be even more pronounced when you have an object deeply nested inside another:

   'List the Name of each field for the
   'recordset rsOrders on frmOrders:

   Dim Idx As Long

   For Idx = 0 To frmOrders.rsOrders.Fields.Count - 1
      Debug.Print frmOrders.rsOrders.Fields(Idx).Name
   Next Idx

Here, for each iteration of the loop, frmOrders has to be dereferenced twice, rsOrders has to be dereferenced twice, and the Fields collection object has to be dereferenced twice.

This could be rewritten as follows:

   'List the Name of each field for the
   'recordset rsOrders on frmOrders:

   Dim Idx As Long

   With frmOrders.rsOrders.Fields
      For Idx = 0 To .Count - 1
         Debug.Print .Item(Idx).Name
      Next Idx
   End With


Of course, I would also recommend using a For-Each statement as well as the With statement in this particular case, but the intention was to illustrate the value of the With Statement.


These things should help you in your objective.

-Dennis Borg
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 14

Expert Comment

by:wsh2
Comment Utility
<ping>
0
 
LVL 4

Expert Comment

by:amit_panjwani
Comment Utility
dennisborg,

---->
Another way to gain a little speed is to use the With statement. See your VB documenation for more info
about the With statement.

For example, consider the following code:

  Dim I As Integer

  List1.Clear
  For I = 1 To 1000
     List1.AddItem CStr(I)
     List1.ItemData(I) = I * 2
  Next I

The object 'List1', in this example, has to be dereferenced 2001 times. By using the With statement,
we can knock that down to only one time:

  Dim I As Integer

  With List1
     .Clear
     For I = 1 To 1000
        .Add Item CStr(I)
        .ItemData(I) = I * 2
     Next I
  End With

------------>

since you are using list1.clear in both the cases, will it really make significant difference.

amit

0
 
LVL 8

Expert Comment

by:DennisBorg
Comment Utility
Amit:

>since you are using list1.clear in both the cases, will it really make significant difference.

I am not sure I understand your question.

My whole point in that example is that in the first version, 'List1' is dereferenced 2,001 times:

   List1.Clear   <--- dereferences List1 one time
   List1.AddItem <--- dereferences List1 1,000 times (inside For-Loop)
   List1.ItemData <--- dereferences List1 1,000 times (inside For-Loop)

By placing the .Clear method *and* the entire For-Loop inside the With Statement, List1 would only be dereferenced *ONCE*, instead of 2,001 times.


Does that help answer your question?


-Dennis Borg
0
 
LVL 4

Expert Comment

by:VincentLawlor
Comment Utility
I think you should think about making you application into a tiered application:

To speed up things why don't you only create the recordsets, connections etc  when you need them.

Alternatively add a Splash Screen while the application is loading and indicate the progress of your application as it loads data etc

When this processing is done show your main Form.

Vin.

0
 

Expert Comment

by:AZWolf7
Comment Utility
Where to begin, where to begin....

Although the performance tips differ for the architecture of any application, I'm going to guess yours is a 2 tier style VB program, and the main slowdown is caused by the recordset. UI tips work sometimes, and can sometimes give the appearance of better performance, let's look at it at the real bottleneck first, the 20 recordsets.

Whenever loading a lot of data like that, look at these things first:
1) Use stored procedures every time -- lots of reasons this is advantageous.
2) Look at the cursor type/location of the recordset, and tailor it for the type of recordset you need. For example, if you aren't going to disconnect the recordset, adUseServer is substantially faster than adUseClient. Wrox Press' ADO reference books have a great chapter on performance differences of recordset types.
3) Use the same connection if you are getting all 20 recordsets at the beginning -- see #4 below though
4) As previously suggested, load the recordsets as needed. Once loaded, disconnect the recordset, and check for "Nothing" to prevent duplicate loads. Use the disconnected recordset throughout the application.
5) For 3-tier databases, reduce the number of round trips for each call. For example, for "Description" style tables (have 2 fields, (ID, Description) for an example, pull in one recordset and filter the recordset as needed (write if you need an example or more info on this).
6) Check the indexes on the SQL Server -- bad or no indexes = bad performance

These tips work on every architecture VB/SQL program. For example, I recently did a project for a large international company. By applying these tips (and a few others, but these were the main problems), I was able to get a program which took over 2 minutes to load down 7 seconds for overseas clients.
0
 

Accepted Solution

by:
AZWolf7 earned 100 total points
Comment Utility
The hardcoded DSN *might* make a difference. Establishing a db connection using an OLEDB connection string is substantially faster even than an ODBC connection. This is also covered in the Wrox book (which I don't happen to have with me today to give solid figures). Both an ODBC connection string (DSN-less) and OLEDB are faster than a DSN connection.

Using a DSN required a registry or file read before processing the actual connection. An OLEDB connection string is pretty fast. In fact, connections are very important to overall performance, at least initially.
0

Featured Post

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).

Join & Write a Comment

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

743 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

17 Experts available now in Live!

Get 1:1 Help Now