Database development and efficiency: Best Practices

[mpau]

I've been developing a database for the past few years and been learning as I go. My abilities have been proficient enough to develop a database that does almost everything we want it to. We use ours to manage a retail, wholesale, manufacturing  business so records deal with customers, vendors, orders, purchase order, products, parts, shipments, etc. However, there is an issue of speed. Locally hosted, it works fine. (Although now that we've integrated more CRM functionality - including a dashboard - it is sluggish on load.) But we rely on remote access for some of our users and it can get very slow on some layouts and pages like the dashboard are not usable. We do not have more than a few thousand records. I feel certain that the fault lies with my programming and the way the database was initially structured.

 

I've spent the past several days going through and testing different optimizations - for example, replacing as many un-stored calculations as I could with auto-enters. Though, I am not seeing much improvement and to be honest, I'm not even sure that this was the right approach. I find myself wondering more about best practices (filtered portals vs table occurrences, indexes versus calculations, script triggers versus calculations, etc) and as much as I read the forums and do tutorials I can't come to solid answers or understanding. 

 

I'm wondering if the folks in the forum would pass along some of their knowledge about what best practices are in terms of the following:

 

1. Filtered Portals or Table Occurrences - Filtered portals make the relationship graph so much cleaner. But which is faster?

 

2. Un-stored Calculations versus auto-enter calculations. This one really stumps me. I've spent days eliminating as many un-stored calculations as I could - to the point where I felt it seemed like overkill because some fields just are by their nature calculations. Nonetheless, I don't see much of a boost in performance. Actually, in some instances I wonder if all the triggered auto-calcs (particularly those triggered by scripts) don't take longer to run than the straight calculation field. 

 

3. Script triggered auto-enter calculations versus calculations

 

4. Memory Cache settings

 

5. Sorting - Sorted records seems so essential for the user but from what I read, it sounds like this has a major impact on performance speed. True? 

 

6. What is the number one most important developmental aspect of an efficient database? Table relations? Stored data? 

 

7. Other best practices that I'm missing or that everyone should know, please share.

 

Thanks for any help or thoughts with this. Your expertise and knowledge is greatly appreciated. 

[bruceR]

Regarding filtered portals: they can be very handy but they work best when the underlying relationship limits the record count to a modest value. Maybe a few thousand; or less. 

 

A filtered portal is really an unstored calculation that operates on every record in the related record set, and the calculation takes place in strict creation order of the records, whether or not the relationship or portal is sorted.

[Vaughan]

IMHO replacing unstored calculation fields with static auto-entered fields is not necessarily an optimisation. Those fields now need to be manually updated, and that processes needs to be managed carefully.

 

The engineers at FMI are putting effort into optimising unstored calculations. I was just reading that FM12 no longer evaluates unstored calculation fields unless the data is actually required; it will not be evaluated if the field is on a layout but is not visible, for instance. Earlier versions moved the load of performing the unstored calculations from the client onto the server which saves a lot of network traffic.