Optimization

  • Thread starter Thread starter Debanjan Dey
  • Start date Start date
D

Debanjan Dey

I am having a client/server application project with Vb
6.0 as front end and MS Access 2000 as back-end. I have
used ODBC connectivity. Right now the problem that is
being faced is as the number of records are increasing,
accessing the application is becoming slower and
slower.How do I speed up the acccessing process of the
database or to be specific, how do I optimize the database?
 
I am having a client/server application project with Vb
6.0 as front end and MS Access 2000 as back-end.

That is not really a client to server application. The more correct term is
a file share. (clinet to server would suggest that you are connection to
some database on the server side...which in your case is a simple file that
you open).
I have
used ODBC connectivity.

While you may be using a odbc string, in fact, you are opening the file
accross your network (the odbc connection ALWAYS resolves to a actaul file
path. (I not nit picking here...but most of the time when you use ODBC to a
server, you don't actaully resolve to a file path, but in fact resolve to a
socket connection (IP address).
Right now the problem that is
being faced is as the number of records are increasing,
accessing the application is becoming slower and
slower.How do I speed up the acccessing process of the
database or to be specific, how do I optimize the database?

You don't mention the number of records. However, the first things to check
is can you retrieve the record(s) via some key or field that has a index on
the field. This can dramatic speed things up. If you try and grab a few
records via a sql statement and the where clause cannot be optimized, then
all records are pulled to the client. If you have 50,000 records and grab
ONE record via a invoice number (for example), then only ONE record is
transferred across the network IF THE invoice field has a index. If no index
is present, then all 50,000 records are transferred.

Don't go overboard on indexing too many fields, as that can cost update time
also. (you have to strike a balance here).

Of course, stuff like opening a reocrdset can be real costly in terms of
time, so avoid any opening/creating of a reocrdste inside of a loop. Most of
these types of loops can be replaced with sql update statement.

There is good list of things to check here: (most apply to ms-access, but
some do apply to JET)

http://www.granite.ab.ca/access/performancefaq.htm
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top