Opensourcepos: Optimise temporary tables

[daN4cat]

New Feature or Enhacement

Current database temporary tables are not optimised resulting in performance issues with large dataset.
Refactor temp tables, add indexes and use temp tables only when and where required.

[daN4cat]

I invite everybody to take the latest master and test all the reports and Sales Takings especially if you have a large set of data as I improved a lot the performance of the queries and usage of temporary tables.

[daN4cat]

I just run some test to see the effect of having the indexes in sales_items_temp:

1 month sales = 198 rows in my test setup

with indexes in sales_items_temp

• first run
  
  
  
  • 0.0091729164123535 seconds
  
  
  • 0.0083279609680176 seconds
  
  
• second run
  
  
  
  • 0.0086960792541504 seconds
  
  
  • 0.0075860023498535 seconds
  
  

without indexes in sales_items_temp

• first run
  
  
  
  • 0.020700931549072 seconds
  
  
  • 0.020079135894775 seconds
  
  

It's 2.5 times faster just with that change.

[daN4cat]

Run some test with Inventory Summary and Inventory Low and the addition of the indexes improved the performance 1.2 times for a result of about 2000 rows.
Not so much like sales but still good.

[daN4cat]

Please note that those tests are just for the addition of the indexes, a lot of improvements are thanks to the other changes.

[jekkos]

Sounds great. Thanks for this huge improvement.