How to improve performance of simple Generic Inquiries/Primary Lists

​@jlpatch This Might be a relations in the GI.

Please review the below help article 

​@jlpatch,

Some of the “out of the box” GIs like invoices and bills require a little massaging to make them more effiicient as the database grows.  While you say there are no issues with the joins, sometimes the issue has to do with too few joins as opposed to too many.  The DACs on a number of these GIs are accessing normalized data references that “live” in other tables, such as “Users”, “BAccount”, “Location”, “Contact”,and the like.  Adding joins to the tables  where some of the master data is housed may reduce the DB overhead and help with performance.  

With that said, I’ve seen GI performance degradate when dealing with data sets of this magnitude; even if the GI has been optimized. 

If you could share some screenshots of the “Results” tab so we can see the columns that you’re using, that would help.  

Regarding the database, (My SQL vs. MS SQL), this would be a good question for a developer and/or network engineer to answer.  The issue could be more related to processing cores and MYOB licensing as opposed to your database software.  

Hello Paul

Thank you. 

Already checked all that.  Taken out inherent subqueries.

You could try to put default date parameters on the GI going back a few months. Typically most clients don’t need to load years worth of data every time they open the GI. This leaves them the flexibility to still update the date to see all time.

Or, get further creative with the parameters/conditions to only show certain relative data but have the ability to toggle on/off with checkboxes. For an example with the Sales Orders GI, maybe a client has mostly SO orders but a bulk of their massive order count is due to ecommerce orders. You could change the parameters and conditions on the GI to only show SO orders by default, but leave the ability to toggle on/off ecommerce orders. 

Otherwise, you could create multiple “Sales Orders” GIs that are specific to the order type or some other parameters. But leave the out of the box on if needed. I don’t necessarily like making multiple GIs if I don’t have to though.

Using the register table is a good idea.  Something to keep in mind.  Thanks for sharing.

Hello @aaghael

Thank you for providing an example GI.   Your stated performance is significantly better than my clients experience.  

Unfortunately when I loaded your GI, it did not result performance improvement in the My SQL environment.

But it did lead me to test removing all joins and just reporting on the APInvoice table without Joins.  This was a 50% improvement.

Although you mentioned some indexing, that would only assist if the joins were broken or special filtering required.  My suspicion is that My SQL is not as efficient as MS SQL  Your 4.5 seconds (MS SQL) on over 1.1 million records versus my 55 seconds  (My SQL) on 700,000 records.