Optimize Form Load/Show

[jtrapat1]

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

 

[woyler]

Can you post how you are opening your recordsets & SQL code. There are quite a few tweaks that can be done.

 

[jtrapat1]

Thanks for the quick response.
Here's the main .bas module which starts with the General Declarations section:

Public Cn As ADODB.Connection
Public bam As Word.Application
Public RsCommittees As ADODB.Recordset
Public RsSQLDeputyBillAssignments As ADODB.Recordset
...etc. (about 15 more ADODB Recordsets declared)

Next, in the Sub Main() portion of the .bas module, the DSN connection is created:
Set Cn = New ADODB.Connection
Cn.ConnectionString = "Provider=SQLOLEDB.1;" & _
"Initial Catalog=bt2001;Data Source=POOH; Trusted_connection=Yes;"
Cn.ConnectionTimeout = 60
Cn.Open
'default directory with word documents is assigned
BamDir = "Z:\Bams\" & CurrentYear & "\"
'An initial SQL string is created to check for valid user:
SQLUserPrivilege = "Select username,staff,role,pcsusername from privilege where username = substring(suser_sname(suser_sid()), charindex('\',suser_sname(suser_sid()))+1, 99)"
Set RSPriv = New ADODB.Recordset
Set RsPrivileges = New ADODB.Recordset
RSPriv.Open SQLUserPrivilege, Cn, adOpenForwardOnly, adLockReadOnly
RsPrivileges.Open "select * from privilege", Cn, adOpenForwardOnly, adLockReadOnly
'if valid, references are set for several recordsets, and connections built on several SQL strings are opened as read-only recordsets
Set RsStatusCodes = New ADODB.Recordset
SQLBamCodes = "select code from bamcodes order by sequence"
SQLDRcodes = "select code from DRCodes order by sequence"
SQLLFINCodes = "select code from LFINCodes order by sequence"
...etc....for about seven more select statements.
RsBamCodes.Open SQLBamCodes, Cn, adOpenForwardOnly, adLockReadOnly
RsDRCodes.Open SQLDRcodes, Cn, adOpenForwardOnly, adLockReadOnly
RsLFINCodes.Open SQLLFINCodes, Cn, adOpenForwardOnly, adLockReadOnly
...etc....for about seven more select statements.

Finally, my frmMain.Show statement is executed.

Thanks in advance.
John

 

[woyler]

Just a couple of suggestions, I do not know if you will see a difference:
1) Not a speed issue, but try to avoid using
the 'New' keyword. Rather use CreateObject.
This will become an issue for instance in VB.net.
Set rs = CreateObject("ADODB.Recordset&quot

2)When you open your recordset use:
RSPriv.Open SQLUserPrivilege, Cn, adOpenForwardOnly, adLockReadOnly, adOpenCmdText AND adExecuteNoRecords
'This will specify that is is a SQL call returning no value

3) when dealing with database key words like SELECT,WHERE, keep them in caps.

 

[Ruairi]

John,
I am curios as to why you would need to open 20 recordsets when you open the first form? Does your application only have one form? Do you really need all that information at the time or did you just figure it would be easier to open all the recordsets at once? It looks like you are using read-only, forward-only recordsets which is good. As you said you really havent given enough information for anyone to really answer your question. Could you explain why you need to open all the recordsets before showing the first form?
Ruairi

Could your manufacturing facility benefit from real time process monitoring? Would you like your employees to be able to see up to the minute goal and actual production?
For innovative, low cost solutions check out my website.

http://www.plccontroltechnologies.com

 

[jtrapat1]

Ruairi,

Thanks for the response.
I just got handed this project and am still going thru the code.
Yes there is basically one form with a tabbed dialog from Sheridan at the bottom of the form.
The user can click on different tabs to get other info and do different things, based on a bill number selected in the top half of the form.

That's why it's tough to split up the opening of the recordsets in different parts of the program.

Someone suggested disconnected recordsets so I'll have to read up on them.

Thanks
John