Solved

Slow Application

Posted on 2001-06-22
15
206 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
15 Comments
 
LVL 52

Expert Comment

by:Ryan Chong
ID: 6217780
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
ID: 6217878
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
ID: 6218457
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 8

Expert Comment

by:Dave_Greene
ID: 6218561
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
ID: 6223118
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
ID: 6223174
What is the command line to load a form when startup form is being activated?

Thanks!
0
 
LVL 9

Expert Comment

by:Valliappan AN
ID: 6223187
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
 

Author Comment

by:yenling
ID: 6243825
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
ID: 6262136
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
ID: 6262187
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
ID: 6262811
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
ID: 6275629
How to create dll for that module for public procedures?
0
 
LVL 9

Expert Comment

by:Valliappan AN
ID: 6275708
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
ID: 7139682
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
ID: 7174003
Comment from expert accepted as expert

Computer101
E-E Moderator
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month10 days, 15 hours left to enroll

632 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