Optimize Form Load/Show

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

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

Put all of the useless controls, like labels and such into control arrays...

lblStatic(x)

or you can try to use windowless controls from microsoft.

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

>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

<ping>

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

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

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.

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.