Linked tables painfully slow!

[bjlu003]

Hi,

I'm developing an application in Access 2k. From the start I had everything (code, forms, tables) in the same database (mdb) file and the performance was good. Now I have splitted it into front end and back end parts (linked tables). When I now try to open a form in the front end it takes AGES until some of the form opens. Is there any trick how to avoid this problem?

Regards

Bjorn L.

 

[rbowes]

What is the backend? .mdb or SQL server, oracle? Instead of using the linked tables, I would suggest turning it all into ADO recordsets or commands. ADO will let the back end process the record retrieval for you and speed up the recordset to you. By using linked tables, you are making the backend database process your request and then the frontend's Jet engine has to process it - so the same request gets processed twice. If the sql statement has many linked tables and requires a lot of fields, this is going to be a long process. ADO will let you bypass Jet in the frontend and then you can make any changes necessary to the data before binding it to a control on the form.

Hope that helps.

Bob

 

[skfoster]

Did you index backend database?

 

[Quehay]

The front end should be on user PC's (in a network environment) once it's in a stable form (no further modifications needed). Other suggestions:

Access is a file-server db which means that processing a recordset request means bringing all data involved over the network before processing the actual SQL command.

1. If you have lookup tables that don't require user additions you could put the items in value lists (each join requires time).

2. If a table can serve the function of two well denormalized--again because joins take time and complete normalization is not always essential in the real world.

3. Watch out for forms that are based on queries or show query results somewhere, i.e., listboxes.

4. Reduce the controls and code in your forms--bells & whistles are great but they cost efficiency.

RBowes: If you are still following this could you elaborate on the use of ADO? Do you mean using VB forms or Access forms unbound? With a Jet backend and Access frontend do you really have backend processing of data sets possible?

 

[rbowes]

ADO (the much maligned ADO, that is) is truly a great technology. You have a choice of recordset, Connection, Command and Parameter objects to create your recordset. They can be used in Access forms easily by setting the recordset property of a form to an ADO recordset (A2K only). This will temporarily unbind your Form, but so what! You could create an unbound form with unbound controls and set their values to the fields in the recordset. And the beauty is that you don't even need to have tables linked to your application. They would link as a VB form would. You could use ADO to reuse forms easily. And performance is increase by leaps and bounds. I was a DAO stalwart until I had to create an Access app that summerized data stored in a SQL Server 7.0 database. DAO was as slow as using querydefs. But, ADO Commands, Connections and Recordset sped up all of my forms. That's why I believe that backend processing is being done without Jet involved. The only drawback (though not really in this world of changing technologies) is that ADO requires that you use SQL syntax that is natural to the database you are connecting to. For instance, with SQL Server, you will have to use Transact-SQL language process your sql statements. Still, if you are interested in enterprise development, which Access is a great development tool for, you would have to learn Backend languages anyway. I personally see Access as a development tool that has a database engine with it - you don't have to use that engine if you don't want to. Plus, you can create and execute stored procedures on the database that process data faster than normal queries, and they can be dynamically created or sent parameters without adding the SQL to your code - its already on the database. Although it is a little difficult at first (very frustrating at times really) once you've mastered creating a connection string, you have everything you need to get ADO to do all of the work for you.

Sorry this was so long.

Bob