FE/BE Performance Issues

[etrain]

When I split my DB into a FE and BE I expected a decrease in performance when going into forms, reports, and queries etc. What I didn't expect it for the forms to take so long going into design mode. I am having this kind of problem through my development FE environment. It is also a lot slower with just Open Mode as well.

I have read through threads posted here but nothing seemed to help. The network shouldn't be the problem but could the server the BE is sitting on be the problem? Meaning the amount of RAM and how much processor speed the server has?

The DB is A2K front and back end. The production FE size is 11.5 MB, development FE is 22 MB and the BE is 6.5 MB. I have, per suggestion of an article I read, imported everything to a new DB, decompiled, recompiled, and compacted the DB. It did reduce the size but didn't help with the speed.

To answer upfront, "Yes" there could be some fine-tuning and maybe rewriting of some SQL statements and VBA code. The DB application performance the way it is now is unacceptable and I do not have the time to do the rewriting because that is unacceptable to the project manager and executive management.

Are there any other tricks to speeding the application up? Again opening a form in design shouldn't take anytime at all because that is local and going directly to design mode shouldn't have to access the BE so this one has stumped me most.

Thanks for any suggestions.
Remember the Past, Plan for the Future, yet Live in the Now for tomorrow may never come.
-etrain

 

[rarich01]

Hmmm... Your question about why it takes longer for your form to open in design mode (now that you have split your project into a FE and BE) actually is not so bad. If you think about it, if your form is connected to a recordsource that lies in the BE, when the form is opened in design mode, A2K's jet engine has to obtain the schema for the recordsource in case there has been any changes to it. This requires A2k to open a connection through your network, get the schema, and then populate that schema for your form. So, if your network connections are slow, or the server in which the BE is located in is slow, then yes, opening up your form in design view should still give you performance issues.

So, how to fix this? Well, that really depends on how you plan on using your project. If it's going to be local, meaning, only accessed via a LAN setup, then optimizing your performance can be as simple as keeping your connection opened to the BE throughout the life of your FE (I'll explain more later). However, if this is going to be a WAN app, then you may want to look into transferring data between FE and BE via XML. This will definately increase performance. Heck, it would probably increase performance in a LAN setup as well, but I don't know if it's worth the effort.

So, back to increasing performance for a LAN setup. There's a little trick that I use that has helped my apps increase their performance. Basically, the problem in performance for LAN setups comes in when your FE is constantly accessing information from the BE, but your connection in between is slow. What's happening in this scenerio is that every time your FE needs information, the JE has to open a connection, get the information (all of it), and then close the connection when you're done. This can be a slow process. So, to speed things up, basically what I have done is forced my FE to establish a connection to the BE when it first opens, and then keep that connection opened until the FE closes. I did this by creating 1) a phony table in the BE called, say tblKeepLinkOpen, whose sole purpose is to provide me with a handle to connect to, 2) create a global recordset object in my FE called, say, rcdKeepLinkOpen, and finally, 3) when my startup form opens up, basically I bind the recordset to the table, thus establishing my connection. What this does is give me a constant connection between the FE and BE so that when I DO need information, the JE does not have to go through the extra process of establishing and closing the connection every time. The connection will automatically be closes when my FE closes.

Also, you might want to take into consideration what kind of cursor you are using to obtain the information from your BE. I.e., is it a forward only cursor, static cursor, dynamic cursor, etc... if your sole task is to retrieve information without actually modifying it (for example, reports), then you'll want to use a forward only cursor. This will help the performance of your app. In other instances, other cursors may work best. Therefore, my suggestion to you is that you look into the different types of cursors and how you can use them to your advantage.

Hope this helped.

Rogelio Arichabala

http://www.WebGuruInc.com

 

[etrain]

Thanks rarich01. I will look into to doing this and let you know how it goes. One thing I did find on the forum here was another trick that increased my performance dramatically by just changing a database property option. Here is the thread in case you are interested. thread702-206410 The WAN idea of transferring data from FE to BE via XML. I do not need to do that as of now but may need to do something like that in the future. I have never done anything even remotely related to this. Is there a book or other reading material that you would recommend that addresses this type of thing?

thanks for the feedback.
Remember the Past, Plan for the Future, yet Live in the Now for tomorrow may never come.
-etrain

 

[Sorcerer1]

Please note that their is a bug in Access 2000, noted by Microsoft which details this exact problem, check the knowledge base for more details.