Solved

Slow Application

Posted on 2001-06-22
15
200 Views
Last Modified: 2010-05-02
VB/Access program

I?ve developed 17 forms, 72 public sub procedures in one module, 1 class module, 1 data environment, 12 data reports.

During development phase (local drive), there?s no much delay in accessing data from the database and opening/closing one form. During testing phase (network drive), there are delay in accessing data and opening/closing one form does overlapped one another, Main form is in the processing of opening, Password form closing image can be seen.

Password form
e     Start-up form.
e     Do a checking whether the user has ?user? or ?supervisor? access rights.
e     Disable the visible properties of 20 menu items for user access rights.
->Open Main form

Main form (Menu editor)
->35 menu items in the menu editor.
->?User? can access only 20 menu items.
->?Supervisor? can access all menu items.  
->Every click on any menu item will open a query form for user to enter a item code.

Query form
->User need to enter an item code and open another form depends on which menu item is clicked.

My Questions:

1)In Password form, when I clicked ?OK? button, it will open Main form but the processing speed is unbearable. How to speed up my application?
2)Will a connection with the ODBC speed up my VB application, as compared to using connection string?
3)From MSDN, there are many techniques like keeping forms hidden but loaded, Preload data, Simplify your start-up form.
4)How to code and display a progress bar running to keep track of what has been processed.

Thanks!
0
Comment
Question by:yenling
15 Comments
 
LVL 49

Expert Comment

by:Ryan Chong
Comment Utility
Hi,

1. What technology you use in your application, ADO, DAO?

2. ODBC will speed down your VB application!

4. PBar.Max = Max

For i = 1 to myQuery.RecordCount
   PBar.Value = PBar.Value + 1
   DoEvents
   
   ''' Do Something here ..

   myQuery.MoveNext
Next i
0
 
LVL 9

Accepted Solution

by:
Valliappan AN earned 80 total points
Comment Utility
Some tips:

1. You could use a Splash screen, in the project startup and in the background, you could load the startup for, so that shows no delay to user, when app. starts.

2. When opening recordsets, always, get the data, that which you want, and not all data. i.e. Use "Select * FROM", in less instances, if data is large.

  Retrieve the necessary fields only if possible.
 
  Open records asynchronously, so that the records retrieval will come up fastly.

3. Use mouse pointers when there is a delay in process, to show to users. As you have mentioned, you could use progress bars, say, when a recordset is loading, or a long process is activating.

4. Try to use direct SQL statements for INSERT, UPDATE, DELETE etc. as much as possible, instead of recordsets, which are slower.

5. Try to replace the Data controls, and use Recordsets if possible. This is my opinion, about this.

Have a look at this link. Hope, you definitely have a lot of tips to learn on how to improve performance in VB:

http://www.experts-exchange.com/jsp/qShow.jsp?ta=visualbasic&qid=20131169

Cheers.


0
 
LVL 4

Expert Comment

by:wileecoy
Comment Utility
valli_an makes great points.  Especially when it comes to the SQL statements and pulling recordsets that include only the records and fields that you need.

Instead of "Select * From Table1", try

Select field1, field2, field3 From Table1 Where field3 = YourCriteria

This takes much less memory for your application.

-----------

Also - look at all of your publicly declared variables and constants.

For example, if in the declaration section of your forms, you do the following:

Public iVar1 as Integer
Public iVar2 as Integer
Public iVariable
Public sVar1 as String
Public sVar2(99) as String
etc....

This takes up additional memory because your application must allocate the memory before the form is actually displayed.

Consider instead, modifying your code to use more variables within the procedures.

If you feel that you need variables to be Public - consider sending variables as parameters between procedures and functions.  This will improve the start-up performance of your application.

Also, an additional benefit is that it will help your code to be more portable between applications.

By this, I mean that you can more easily re-use your code because you will pass parameters for all of the information necessary to perform the tasks of the procedure or function.  You can simply cut and paste your code to any application and know that if you pass the indicated parameters, your code will work properly.

Another point to make about the Public variables I noted above - declaring public arrays when they aren't required to be public can really drag the memory requirements of startup - you have to allocate enough memory for all elements of the array.

An array variable such as Public sVar2(99) as string must allocate enough memory for 99 string variables.  That can add up quickly if you have many public arrays declared.  Especially if you only use them in a couple of places.  They will remain allocated in memory for the life of the application.

------------

Related to your 72 Public prodeures in your module - I would look at the necessity of each of them being public.  If they are all necessary, consider creating a seperate dll just for that module.  Then the application won't have to load all of them at start-up.  They will only be loaded when called by your application, and then the memory will be released when the call to the dll returns.

--------

Splash Screens - They are very good if you must load forms that require a lot of resources or a longer time to load.

Adding a progress bar to one will also help the patience of the user.

Valli_an's link is also a good one.  You should definately read through that.

Good Luck!
0
 
LVL 8

Expert Comment

by:Dave_Greene
Comment Utility
Ok, my two cents... since the application was designed poorly you may never get the results you're looking for.  But to speed it up.  Change all of your bs controls, like labels and frames into control arrays.  The control array acts like a single control in memory, hence the savings.  Also, any code you place into a form is validated when the form opens.  But code that you put into module(s) is only validated when it is accessed, saving you the form load time.

other than that, make doubly sure that you are cleaning up all of the objects you create, once they are no longer needed.

Good Luck!
-Dave
0
 

Author Comment

by:yenling
Comment Utility
I?ve already set up the ODBC connection from Data Sources (ODBC) in the Control Panel.What is the next step?

My startup is a login screen.I'm using a Jet *.mdb using ADO and the Jet 4.0 OLE DB provider and create a recordset against the database itself.Does it slow down my system?
0
 

Author Comment

by:yenling
Comment Utility
What is the command line to load a form when startup form is being activated?

Thanks!
0
 
LVL 9

Expert Comment

by:Valliappan AN
Comment Utility
Can you put the OK_Click (or whatever) code on your password form, and the Form_Load and Form_Activate events code of your next form you are loading. The problem, could be in these locations, if I am right.

Also, the problems could be that, you load a big module in memory, with lot of functions, so it decreases the speed.

Hope this helps,
Cheers.
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:yenling
Comment Utility
Related to your 72 Public prodeures in your module - I would look at the necessity of each of them being
public.  If they are all necessary, consider creating a seperate dll just for that module.  Then the
application won't have to load all of them at start-up.  They will only be loaded when called by your
application, and then the memory will be released when the call to the dll returns.

>>How to create dll for that module?
0
 

Author Comment

by:yenling
Comment Utility
1)Any tutorial for using direct SQL statements for INSERT, UPDATE, DELETE etc?Pls show me examples for each statement in complete code.

INSERT [INTO] TableA (field1,field2,field3,field4) VALUES ('1','3','A','1254');"

--->This is my sql statement but I don't know how to use it.(I'm used to used recordset)

2)How to create dll for that module for public procedures?

0
 

Author Comment

by:yenling
Comment Utility
Regarding question 1,how to pass defined-string variables into the insert statement?

Like:Dim a,b,c,d as string

INSERT INTO TableA (field1,field2,field3,field4) VALUES (a,b,c,d)"

error:no value given for required parameters....

pls help!

0
 
LVL 9

Expert Comment

by:Valliappan AN
Comment Utility
Regarding your question on passing parameters to INSERT query, if I had understood properly, It is like this.

Assume a,b,c,d are variables containing values, say field1, field2 are numeric and field3,field4 are string values.

Conn1.Execute "INSERT INTO TableA (field1, field2, field3, field4) VALUES (" & a & "," & _
                    b & "," & _
                    "'" & Replace(c,"'","''") & "'," & _
                    "'" & Replace(d,"'","''") & "')"

n.b. A Replace command is issued to c,d to avoid errors, if there is a single quote in the value of the variables c,d, by escaping single quotes in string value to double single quotes.

Hope this helps.
0
 

Author Comment

by:yenling
Comment Utility
How to create dll for that module for public procedures?
0
 
LVL 9

Expert Comment

by:Valliappan AN
Comment Utility
Have a look at:

http://internetcollege.virtualave.net/dllAsp/dllAsp.htm
(Create a .dll File with VB and ASP)

http://www.icrossroads.com/~spider/vbmath.html
(ActiveX DLL)

Cheers
0
 
LVL 49

Expert Comment

by:DanRollins
Comment Utility
Hi yenling,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will suggest to:

    Accept valli_an's comment(s) as an answer.

yenling, if you think your question was not answered at all or if you need help, you can simply post a new comment here.  Community Support moderators will follow up.

EXPERTS: If you disagree with that recommendation, please post an explanatory comment.
==========
DanRollins -- EE database cleanup volunteer
0
 
LVL 1

Expert Comment

by:Computer101
Comment Utility
Comment from expert accepted as expert

Computer101
E-E Moderator
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…

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

12 Experts available now in Live!

Get 1:1 Help Now