Target
MightyData developed an order management system for Lettermen Sports several years ago.ย Lettermen Sports is one of the premier sports apparel production companies in Minnesota with locations in Blaine and Plymouth and a large production facility where it completes nearly 300 orders per week for sports apparel screen printing, lettering and shipping.
The primary purpose of its FileMaker application is to manage the flow of orders through its production facility. A core facet of the solution is its interface with the SQL-based point-of-sale program, CounterPoint. The FileMaker solution was developed by MightyData to import orders and order detail from CounterPoint.
Performance of the import/update process began to lag behind the increasingly fast pace of the business as the record counts in some of the FileMaker tables grew to over 300,000. With the update process taking an average of 23 minutes, the in-house FileMaker server performance was being compromised by the load. Employee productivity was reduced by having to wait for orders to be imported, and by the performance hit to the FileMaker server.
Lettermen Sports’ management approached MightyData to re-design the order import process to accomplish the following:
- Reduce the time required to import and update orders from CounterPoint to the FileMaker order management system
- Automate the import process
- Eliminate the up-to-24-hour delay for new and updated CounterPoint orders to be created/updated in the FileMaker database
Technology
MightyData’s technical team arrived at a solution to redesign the import/update process to more closely match the current needs of the business. The original import/update process involved importing data from the point-of-sale solution using FileMaker’s import script step to update matching records and create new records that were unmatched. Import subscripts were then used to import data from across the CounterPoint solution so that data required for shop floor production was available in the FileMaker database. This approach was very effective at ensuring that all data was successfully imported as the solution would import across all orders and line item detail each time the update was run.
As the business rules evolved, however, the update process became more complex. Combined with high record counts, a new approach was required.
The new design was based on only working with new and changed records in the CounterPoint system. In so doing, the number of order records that the scripts were required to process was reduced dramatically, to just 3 or 4 at a time. Conveniently enough, CounterPoint provides an order creation and update timestamp which is updated any time an order or its line-item detail is changed. This allowed for a complete overhaul of the scripting process that simultaneously accomplished all three of the customer’s goals. Furthermore, all script execution was moved to the server, allowing staff to focus on order production and not on managing the update process.
Transformation
This project is an excellent an example of how value is driven by the interface of a customer’s need with great technical planning and execution in a spirit of partnership with the customer to get the job done well. The new solution reduced the update execution from an average of 23.4 minutes to just 2.5 seconds – a 99.8% performance gain. While technically impressive, the real value comes from production staff getting new orders and updates no longer than 30 minutes after they are recorded in the point-of-sale system. If even that is too long, the update can be run manually in a few seconds, giving the team immediate access to new order data.
As an added bonus, MightyData also gets to work with some really good people at Lettermen Sports. They help us help them. And that partnership makes all the difference.
Good article but there is probably a mathematical error in it !?
If the new execution time is now 2.5 seconds while the older execution time was 1404 seconds (23.4 minutes), then the performance increase is more like 562 times better !?
Maybe I incorrectly understood your phrase (and title) but increasing an execution time by 100% is only making it twice faster. Something from 23 minutes to 11 minutes.
Thanks !
Gaston is right, you’re undervaluing your success ๐
You have reduced the time needed for a single operation by 99.8 %.
But performance is reverse value of time – it’s how many times you can repeat the operation in a specific time interval. You can now do the same operation 561 times in the same time interval, so you’re 561 times faster, that is 56000 % performance improvement, I think…
You guys are cracking me up! Thank goodness my algebra skills were not needed to develop this solution for for the customer ๐ I just KNEW that the someone would come along and fiddle with my math, which was why I turned to this to get my original figures:
http://www.percent-change.com/index.php?y1=1404&y2=2.5
Maybe this is just a question of semantics? “Performance improvement” being defined by the “reduction in time needed for single operation”!?!?!?
Yes, you have hit the point.
Just like the difference between speed and pace.
Actually, I don’t get the meaning of pace – why would anyone want to indicate higher speed with a lower number? ๐
HOnza,
I appreciate the precision and insight you bring to the performance of FileMaker solutions. Keep chiming in. I will stop by your booth at DevCon!
Since the CounterPoint is a SQL based solution, wouldn’t it be better to update the Filemaker solution to just host the tables that have the data you need and have the data be ‘live’?
You would obviously have to update your TOs and scripts and other logic, but the end solution would be better.
Don’t get me wrong, the improvement on import speed you made is great and all, but wouldn’t the ultimate solution to be the always live data solution with no import/update need at all?
Brian,
Thanks for chiming in. That is an excellent point, however it is not a simple matter of data display. The data required on the production floor lives across many normalized tables. Part of the process is to de-normalize and convert the data so that it can be manipulated to suit the needs of production. Another goal is avoid any modifications to data on the point of sale side. It is a one-way process.