Speed optimization for multi-user networked sol.

[d94dsj]

Hi everyone,

I have a problem which is bugging me enormuosly. I'm in the process of developing quite a large solution (30+ files) for a trading company. The system deals with most processes needed for running the business. This includes storing/processing for the following entities:

Customers

Contacts

Suppliers

Orders (from Customers)

Orders (to Suppliers)

Products/Items (including "automatic" inventory management system with trigger points for semi-automatic ordering of products from suppliers when orders for those products have passed the trigger points)

Delivery addresses

Invoices

Events

Reports

Price contracts

Service/Repairs

Quotations

etc...

The number of records in the files vary from few up to currently about 60 000 in the biggest line items files.

When registering an order, a script is run which checks numerous things (the customers credit level, that all necessary information is filled in on the order form and within bounds and so on). If these checks pass, the order is given a state of "registered" and then some sub-scripts is run to update stored fields containing the number of products/items in stock ("inventory balance"). To implement the "automatic" ordering of new products/items and other things, three differens types of "inventory balance" is needed. These are:

"Ordering inventory balance"

"Disposable inventory balance"

"Actual inventory balance"

Not wanting to engulf you with the details of each of these, suffice it to say that each of these depends on different line items files, such as CustomerOrdersLineItems, SupplierOrdersLineItems, and InvoiceLineItems, and also sometimes on the Orders file.

In the products/items file, I have three calculated fields for the three different balances. In the first version, I just copied the values of these calculated fields to the stored versions. This was too slow, because the calculated fields took too long to update, since they are dependant on related files, sometimes in two steps, and also on calulated fields in the related files. Now I've written a series of scripts to update the stored versions "manually". This improved the speed with a factor of maybe 5-10, but it's still far from the performance I need. For the most ordered products, it could take about 40 seconds to register an order. I would need this to come down to 5-10 seconds to get a reasonable response time for the users' demands. I've pinpointed the big performance penalty step being an assignment of a summary field in a file (let's call it file A) to a stored field in file B. Now, the number of records that the summary is based on is not that big, maybe about 600 records at most, but it still takes 25-30 seconds before the stored field in file B is updated with the summary field from file A. Now, I've understood from Debi Fuch's excellent paper "Going to Related Record from a Found Set in FileMaker Pro: A Discussion of Alternative Techniques" (www.aptworks.com), that when accessing a summary field in a networked solution, the data from ALL fields in the file is transferred, not only the field(s) the summary is based on. This data is presumably stored in a cached file on the user's local hard drive after the transfer, to speed up following transactions.

I've also confirmed this myself by running a TCP/IP packet analysis tool (Ethereal for Windows - uses a tcpdump compatible format) and analyzing the data packets transferred when performing the update script. I think this is the main reason why it performs so slow.

Instead of using a summary field, I tried summarizing the field with a simple loop, just increasing the value of a global when looping over the records. To my great disappointment, this was actually some seconds slower than using the summary field.

I'm pretty sure that it's not a bandwidth-related issue. Ethereal told me that the average transfer speed was 0,5Mbit/s during this operation, so the mixed 10/100Mbit network in use here should cope fine with it. The FM server (5.0) is a brand new G4 with SCSI hard drives, tuned according to FM's white paper about FM Server. I guess a RAM disk would give me better response times, but that's not a viable option at the moment.

Now, my question is if anyone knows a workaround to this? Is there a way of making this summary any faster?

Any input is highly appreciated.

Thanks,

Daniel

[LiveOak]

Two things come to mind. 1) use a relationship and the Sum() function to grab the summary value. 2) don't compute inventory totals on the fly.

A lot of inventory systems use a two level apporach. Good intentory systems create the total inventory quantity from a file of stock and draw transactions. This is the best approach from auditability and data integrity, but can be slow to compute on the fly. Instead, a single number "inventory quantity" is calculated off-line each night and stored in a field. Daily stocks and draws can be added and subtracted from this number directly or a relationship can sum just todays transactions and subtract them from this number on the fly.

Another thing to avoid is a list view of a whole bunch of inventory items with a summary field displayed. This is death for speed. In the list view only display last nights regular number field. When the user clicks on a particular inventory item, the more correct total (last nights - today's transactions) can be displayed for just the one item.

-bd

[d94dsj]

LiveOak,

Thank you very much for your comment. I will test to implement your #1 suggestion by using a self relationship and the Sum() function.

Your other comments are very valuable as well. I agree that you would have to split line items files to make the solution scalable - but I'm afraid that this would add a lot of complexity and further dependencies to the current solution - and I'm not sure this is viable at the moment (time & resource constraints).

Thanks,

I will report on any performance improvements.

/Daniel

[Kurt Knippel]

I can already see some potential problems. First off you have multiple files for lineitems and orders and types of contacts. This is almost always a bad idea and will lead to poorer performance and generally more difficult maintenance.

By reducing the number of databases and consolidating the information you will have less related files to deal with.

I would start there. If you want some help, just provide a listing of each of your files and explain briefly what records it contains.

For instance: Customers, Contact, Supplies, ect are not really seperate types of records and in most cases should be in the same file. Likewise with CustomerOrders, SupplierOrders and CustomerLineItems, SupplierLineItems; both sets of which are really the same kinds of records and belong in the same files.

A typical solution would be structured like: Companies (optional), Contacts, Orders (or Invoices), LineItems, Products.

[d94dsj]

Hi Kurt,

I'm surprised by your statement! I thought you were an advocate of strict normalization ;-)

Actually, I made an error in my first listing - there is no file "Suppliers" - these are actually stored within the "Customers" file. But I can give you a short description of all the files used in the system (I'm translating the names to English, since the original names are in Swedish). Feel free to prove my design wrong! Although this will be difficult since I cannot possibly describe all business rules that apply here...

Customers - contains attributes such as company name, invoicing address, notes, VAT no, phone no, e-mail, credit time, terms of delivery, category (like key customer, prospect, supplier, and so on) + many more

Contacts - contains contact persons that are either related to Customers (Customers->Contacts is One-to-many) or standalone. Attributes are name, address, phone no, fax no, title, role, notes + some more

Users - contain information about users of the system (employees of the trading company). This is things such as name, login name, access rights, navigation system preferences, startup file and more

Delivery addresses - contains addresses related to the customers. Each company can have one or more delivery addresses. One of each customers' address is flagged as the main delivery address for that customer.

Events - contains events related to the customers and contacts and users. This could be minutes of meetings, notes from phone calls, sales events. It could also be e-mail sent to/from contacts. So this is related to both customers, contacts, and users. E.g. if a meeting took place between a supplier and my client, then the Events file would store foreign keys for the customer, the contacts (multi-line keys) and the users (multi-line keys).

Products - contains information about the items/products that the trading company sells. This contains many fields, some of them are: product identifier (NOT used as a p.k.), description, price category, storage location, foreign key for Customer (supplier), discount group, product type, currency for purchase, purchase price, packaging scheme, trigger point for making new order from supplier, order buffer when ordering from supplier, sorting numbers for price lists,sales arguments...and many more. Some products are actually composed of one or more other products/items, this information is stored in the file ProductComponents.

ProductComponents - contains foreign keys for compund products and for the products the compound product is composed of and the number of products the compound product is composed of. So this is related to the Products file.

SupplierOrders - This file contain orders which the trading company places with their suppliers. This is related to Customers, Contacts and Users.

SupplierOrdersLineItems - Contain the line items for supplier orders. This file contains not only items from the Products file, but could contain manually entered items as well.

CustomerOrders - This file contain orders placed by the trading companys' customers. It is related to Customers, Contacts, and Users. It contains complex logic to handle the order registration process.

CustomerOrdersLineItems - Contain the line items for orders from customers. These are only items from the Products file.

Invoices - Contains invoices from the trading company to its customers.

InvoiceLineItems - Contains the line items for the invoices.

Deliveries - Contain delivery slips / shipment numbers for deliveries related to an order.

PriceContracts - Contains price contracts for customers

PriceContractsLineItems - Contain line items for the price contracts

Quotations - Contain quotations for customers

QuotationsLineitems - Contain line items for quotations

Calendar - A calendar file used for easy viewing and setting dates in date fields

Menu - a menu file containing user preferences layout and navigation to different parts in the solution

Navigation - a file containing information about where a user has navigated in the solution, so they can use generic "Back" and "Forward" buttons (similar to a web browser)

Reports - contains all of the reports in the solution together with a description of them. The actual print layouts are of course in the proper file, but the reports file is a central location to store statistics about report usage and for learning about all reports in the solution.

ReferentialIntegrityCheck - a "system only" file, automatically run at night to make sure there are no primary key duplicates in the system (although there shouldn't be since I use a modified version of your proposed "CaptKurt Scheme"). Yes, I know - I am paranoid.

Revisions - contains developer information about what revisions the solution has gone through, known bugs for a certaion revision, file list for a revision, revision numbers, and so on. Each record in each file is flagged with the revision number it was created in, to facilitate bug tracking/fixes.

SystemParameters - this is a one-record-file containg things general to many files, such as tax rates, address of the trading company, company logotype, and so on

TollCurrencyRates - Contains different currencies and their "toll rate" for the current, and previous month

ServicesAndRepairs - Contains service cases for repairs / guarantee repairs

ServicesAndRepairsEvents - Contain what has happened for a certaion repair/service case, when and so on

ServicesAndRepairsLineItems - contain line items spare parts used in a service/repair case

Phew! That was about...29 files, and there are some more coming in at a later stage. I don't know who cares to read all this, but if you do, please tell me if I could improve my design.

I'm quite confident that I cannot consolidate any of the files because of the complexity of the system. It would be very hard to understand, even if you in some cases could make it perform faster.

Thanks,

Daniel

[Kurt Knippel]

I thought you were an advocate of strict normalization

First there is "normalization" and then there is normalization. I am a big advocate of normalization in general but I do not carry it to the nth degree. Generally when designing I try for the 3rd normal form, but often then back off a bit, because of Filemaker's 1 table per file structure.

Secondly people often mistakenly take normalization to far and break up different "types" of records that are really the same type of records as far as normalization is concearned. Companies/Contacts are all the same, regardless of whether they are prospects, customers, vendors, supplies, personal, etc. That categorization is really just an element of the data in the record. Many people will mistakenly normalize these into seperate files. Similiarly with Invoice/Order and thier LineItems, which will sometimes be broken up into different files for Customer orders and Vendor/Supplier orders.

Users

Customers

Contacts

Delivery addresses

Events

Products

ProductComponents

Good to this point. Although it seems that the "Customer" table is more properly a "Company" table and it does contain some information that is more properly related to specific "Contacts". That said you are on the right track here. Also technically the "Delivery Address" is simply an "Address" file, renaming it will lessen confusion later on.

SupplierOrders

SupplierOrdersLineItems

Quotations

QuotationsLineitems

CustomerOrders

CustomerOrdersLineItems

Invoices

InvoiceLineItems

Ok, now it falls apart. Unless each of the items is RADICALLY different, I see that this should only be 2 files, "Orders" and "LineItems". A Quote becomes an Order. An order is either a Supplier Order or a Customer Order and is either Invoiced or not. Logic in how this stuff is handled is immaterial. Combining them into a single set of files will make maintainence easier and lessen the amount of related fields you need to deal with.

Deliveries

PriceContracts

PriceContractsLineItems

Calendar

Menu

Navigation

Reports

ReferentialIntegrityCheck

Revisions

SystemParameters

TollCurrencyRates

ServicesAndRepairs

ServicesAndRepairsEvents

ServicesAndRepairsLineItems

Everything else looked pretty good.

[d94dsj]

Brent,

Instead, a single number "inventory quantity" is calculated off-line each night and stored in a field. Daily stocks and draws can be added and subtracted from this number directly or a relationship can sum just todays transactions and subtract them from this number on the fly.

This is what I've decided to go with now. Nothing else is scalable or even offers the performance I need today. The only thing is that you have to be very careful when identifying when the different stored "inventory quantities" has to be updated with the delta. In my case I have to do it in 7 separate scripts I think - I'm identifying them now.

Thank you very much for sharing for this insight!

/Daniel

[d94dsj]

Kurt,

Thank you very much for analyzing my solution.

I agree to some of the things you say, but definitely not all of them. Yesterday I started writing a post to explain my rationale and give some follow-up questions to you, but after 45 minutes I was still not finished - so I haven't posted it yet.

I hope to post my reply next week, this could be a really interesting discussion that we could both learn from!

Have a nice weekend!

/Daniel