-1

is there a way to optimize this kind of query.

See the given below.

Select KPI.*,
(select sum(NP_2g_crfs) from kpi_table where date_upload = '14/01/2020') as num_sum, 
(select sum(NP_2g_den) from kpi_table t1 where t1.id >= kpi_table .id) den_sum,
(select sum(NP_2g_num) from kpi_table t1 where t1.id >= kpi_table .id) num_sum, 
(select sum(NP_2g_num) from kpi_table t1 where t1.id >= kpi_table .id) /
(select sum(ROUND(NP_2g_den, 2)) from kpi_table t1 where t1.id >= kpi_table .id) kpi
from kpi_table 
WHERE date_upload = '14/01/2020'

Load time is up to 5 mins with 5000 rows.

Thanks in advance.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Darius Paul Bacate
  • 1
  • 4
  • 2
    Please add table defintion and an explain plan. ALSO are dates stored as character dataypes in a non standard mysql format?ALSO parsing the same table 5 times is unlikely to be efficient.An explanation of what you are trying to achieve would be helpful. – P.Salmon Jan 13 '21 at 16:15
  • if the same table and conditions is then no need to use sub-query. – Naveed Ramzan Jan 13 '21 at 16:17
  • @Naveed Ramzan not sure about that OP seems to want row for 14/1/2020 and a sum of current id and all future ids. Whether that makes sense is another matter,, – P.Salmon Jan 13 '21 at 16:21
  • NP_2g_num and NP_2G_CSFR are decimals(10,2) while NP_2g_den are integers(11) – Darius Paul Bacate Jan 13 '21 at 16:21
  • So? that's not going to affect performance. – P.Salmon Jan 13 '21 at 16:22
  • You appear to be aggregating for everything recursively based on ID. Ex: IDs 1-10. 1 gets sum of 1-10, ID2 gets 2-10, ID3 gets 3-10, etc. What is the purpose of that. Edit your existing post and describe what and why you are trying to get these "at or higher" than existing ID aggregates. – DRapp Jan 13 '21 at 16:27
  • What is your version of MySql? – forpas Jan 13 '21 at 16:31
  • @DRapp Yes Sir, inorder to get the percentage value of KPI i need to get the of den & num recursively. I think that's what makes it slow. – Darius Paul Bacate Jan 13 '21 at 16:33
  • @P.Salmon It affects the performance of my project I recently get the load speed and just now the result is 6 mins on local server and how much more on the live server. – Darius Paul Bacate Jan 13 '21 at 16:36
  • @forpas Version 10.4.11-MariaDB – Darius Paul Bacate Jan 13 '21 at 16:38
  • I updated your question title and tags to make it clear you are using MariaDB. We should not consider MariaDB to be the same as MySQL, they have been diverging since 2010, and they have different features now. – Bill Karwin Jan 13 '21 at 16:42
  • @BillKarwin thank you Sir – Darius Paul Bacate Jan 13 '21 at 16:46
  • When you ask for query-optimization help, you should run `SHOW CREATE TABLE kpi_table` (or whatever table is referenced by your query), and post the result in your question. This shows readers what columns, data types, indexes, or constraints you have in your table. Otherwise we have to guess. Please help us to help you! – Bill Karwin Jan 13 '21 at 17:27
  • Also remember to post the result _as text_, not as a screenshot image. If it's text, readers can copy & paste it to their test environment to make sure they can duplicate your problem and edit it to make a solution. If you use screenshots, they can't do that. – Bill Karwin Jan 13 '21 at 17:29
  • Additionally, show in your edited post some SAMPLE DATA of these columns/values and what you expect the totals to be... Ex: for ID1 (showing summation of 1-10), ID2 (showing summation 2-10), etc or however your data works. Also, your summation on den and num do not include the date filter criteria. Should it? I mean, if your ID values are always greater than the prior and the first ID in the table on/after your where clause, do you only want those IDs in the summation, or EVERYTHING regardless of the date. Edit EXISTING Post, dont reply in comment. – DRapp Jan 14 '21 at 00:01

1 Answers1

0

Window function SUM() should perform better that these correlated subqueries:

SELECT *,
  SUM(NP_2g_crfs) OVER() num_sum, 
  SUM(NP_2g_den) OVER (ORDER BY id DESC) den_sum,
  SUM(NP_2g_num) OVER (ORDER BY id DESC) num_sum, 
  SUM(NP_2g_num) OVER (ORDER BY id DESC) / SUM(ROUND(NP_2g_den, 2)) OVER (ORDER BY id DESC) kpi
FROM kpi_table 
WHERE date_upload = '2020-01-14'
forpas
  • 160,666
  • 10
  • 38
  • 76