The system was created by another software development company and largely worked well, but one of the key processes was taking far too long to run. In some cases, users had to wait more than an hour for data to upload and be processed successfully.
The process involved the uploading and processing of (typically) 100s of items of data. The Alpha Five "XBasic" coding written to process the data was working with one item at a time in a loop - and this loop was writing records individually to several MySQL tables.
One of the key advantages of a SQL database (including MySQL) is that records can be "batch updated" and so the first aspect of improving performance was to rewrite the SQL so that the loop was no longer needed.
Once done, performance was improved but still far too slow so a full analysis of the database structure was carried out.
The key to improving performance was noting that the tables were not optimally indexed. Once indexed correctly, we got the hour down to just 22 seconds - a 160x speed improvement!
Result - happy client.
If we can help you with performance issues in Alpha Five, Alpha Anywhere, MySQL or SQL Server, please do get in touch.
NICE POST
ReplyDeleteMovers And Packers