poor performance from MySQL stored procedure
Hi,
The issue I have is complaints of poor performance from the attached stored procedure.
I have attached a stored procedure and the DDL of the tables it accesses.
The stored procedure takes data harvested from retail websites, augments it with reporting dimensions and aggregates the data for reporting.
the utl_mst_products table contains approx 100,000 rows, one record per product per store.
the utl_harvest table has data appended to it every day, approx 15 million rows per day. This table is only used by the stored proc and currently has 2 years worth of data. No updates or deletes are performed on this table, data is appended.
the utl_reporting_hist contains 2 years worth of data, grows by approx 15 million rows a day.
utl_reporting_agg contains aggregated data for imports over the last 2 years.
Would appreciate any guidance on this. Thanks.
utl_tech_test_PROC-2-of-3-October-2016
utl_tech_test_TABLES-3-of-3-October-2016
The issue I have is complaints of poor performance from the attached stored procedure.
I have attached a stored procedure and the DDL of the tables it accesses.
The stored procedure takes data harvested from retail websites, augments it with reporting dimensions and aggregates the data for reporting.
the utl_mst_products table contains approx 100,000 rows, one record per product per store.
the utl_harvest table has data appended to it every day, approx 15 million rows per day. This table is only used by the stored proc and currently has 2 years worth of data. No updates or deletes are performed on this table, data is appended.
the utl_reporting_hist contains 2 years worth of data, grows by approx 15 million rows a day.
utl_reporting_agg contains aggregated data for imports over the last 2 years.
Would appreciate any guidance on this. Thanks.
utl_tech_test_PROC-2-of-3-October-2016
utl_tech_test_TABLES-3-of-3-October-2016
ASKER
Hi Geert,
thank you for your input....will look into this and get back to you
thank you for your input....will look into this and get back to you
ASKER
Hi Geert, can you give me an example in the code where the "cleanup" is happening?
Thanks
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
They have changed ee since i last used it....dont see how to close this question!!!!!.... will check the help....in the meantime thanks for your help.....will distribute the points when i find out how :-)
ASKER
Thanks Geert, very much appreciated
cleanup after
you have cleanup as part of the insert process
> that's most likely the slow part
those select distinct indicate you don't have unique keys ...