0

My query has to return a statistics for example for march containing productname and price and its sidedishes (1:n relation) with the right price of each sidedish and the right tax for each sidedish and product (can be different). 

SELECT count(*) as daySum,
    p_name,
    SUM(pp_price) as daySumPrice,
    o_day,
    o_time,
    s_id,
    s_name,
    t_name,
    t_id,
    SUM(ust10) as ust10, 
    SUM(ust20) as ust20,
    SUM(sdUst10) as sdUst10, 
    SUM(sdUst20) as sdUst20 ,
    fk_p_id,
    u_name
FROM 
(
    SELECT tbl_orders.o_id,
        tbl_orders.o_comment,
        DATE_FORMAT( $dateField,  '$sqlDateFormat' ) AS o_day,
        tbl_orders.o_time,
        tbl_orders.o_t_name AS t_name,
        tbl_orders.fk_t_id AS t_id,
        tbl_orders.fk_u_id AS u_id,
        tbl_orders.fk_s_id AS s_id,
        tbl_orders.o_s_name AS s_name,
        tbl_orders.o_u_name AS u_name,
        tbl_orders.fk_p_id,
        CONCAT(tbl_orders.o_p_name,IF(sideDish.p_name IS NULL,'',' ('),IFNULL(GROUP_CONCAT(DISTINCT(sideDish.p_name) SEPARATOR ', '),''),IF(sideDish.p_name IS NULL,'',')'),IF(tbl_orders.o_comment='','',' - ".getLanguageKey('label_comment').": '),tbl_orders.o_comment,IF(tbl_orders.o_comment='','','')) as p_name,
        ROUND(IFNULL(tbl_orders.o_p_price,0)+IFNULL(sideDishPrice.pp_price,0),2) as pp_price, 
        IF(tbl_orders.o_p_ust=10,IFNULL(tbl_orders.o_p_price/(100+tbl_orders.o_p_ust)*tbl_orders.o_p_ust,0),0) AS ust10,
        IF(tbl_orders.o_p_ust=20,IFNULL(tbl_orders.o_p_price/(100+tbl_orders.o_p_ust)*tbl_orders.o_p_ust,0),0) AS ust20,
        sideDishPrice.ust10 AS sdUst10,
        sideDishPrice.ust20 AS sdUst20
    FROM tbl_orders 
    INNER JOIN tbl_dailyReport dr1 
        ON dr1.dr_id = tbl_orders.fk_dr_id
    LEFT JOIN 
    (
        SELECT  o2.o_id,
            SUM(IFNULL(o2.o_p_price,0)) as pp_price,o2.o_p_name as p_name,o2.o_parent AS o_parent,
            SUM(IF(o2.o_p_ust=10,IFNULL(o2.o_p_price/(100+o2.o_p_ust)*o2.o_p_ust,0),0)) AS ust10,
            SUM(IF(o2.o_p_ust=20,IFNULL(o2.o_p_price/(100+o2.o_p_ust)*o2.o_p_ust,0),0)) AS ust20
        FROM tbl_orders o2 
        GROUP BY o_parent
    ) sideDishPrice
        ON tbl_orders.o_id = sideDishPrice.o_parent 
    LEFT JOIN 
    (
        SELECT o_id,o_p_name AS p_name,o_parent 
        FROM tbl_orders 
        GROUP BY o_id

        ) sideDish
            ON tbl_orders.o_id = sideDish.o_parent
WHERE 
                    tbl_orders.fk_c_id = '$c_id'
                    $stornoNull

                    AND tbl_orders.o_parent IS NULL
                    $onlyDayClosingData
                    $whereClauseTime
                    $whereClauseID
                    $whereClauseStorno
                        GROUP BY  tbl_orders.o_id,tbl_orders.o_p_name,sideDish.o_parent

                    ) products  $groupBy $orderBy;

The query is working and it gets the right numbers but it takes too long.

rows in database: 7000 orders in march: 3500 time to print 6 of these queries with different group by's (date, waiter, table, products, payment method, and cancellations,...): about 30-40 secs.

Imagine how long it would take if we have 10000000 of rows (which could be realistic in a few years?)

Is there any way to improve this performance?

EDIT: I already solved it using a second table. As the table tbl_orders is used for the orders itself (recursive, for orders with sidedishes) i just put it joined into a new table tbl_report. There it is possible now for me to group things like i want them with good speed :)

Thank you for your advices. Some of them were helpful though :)

how should i mark my question? solved?

Improve this question
8
  • Did you try building this in a report writer instead of in SQL? – Mike Sherrill 'Cat Recall' Mar 27 '13 at 14:49
  • what do you mean exactly? – Chris Mar 27 '13 at 14:52
  • 3
    I advise you to divide and conquer to spot what is triggering the slow performance. Take each select individually and check performance. Is one of these slow? Then check complex expressions. Start simple and add-up until you hit bad performance. If all ok, then join selects one at a time and check performance every time. If one join becomes slow, check field expression. If you are stuck with one expression/join come back. – koriander Mar 27 '13 at 15:37
  • its a bit better now, as i deleted the sideDish subselect join. i think the performance was improved about 40%. what else can i do? is there a better way to get all children of a row? product and its sidedishes (1:n) than group_concat(distinct(sideDishName))? – Chris Mar 27 '13 at 18:23
  • @Chris, that query is too hard to read for people to spend time with it, with so many questions around. I can not even see the Group by that should go along with the group_concat you are using. Plus I can not see why your explanation of what you want to do (sum up product values together with related sidedish values) becomes such a complex query. Also, you don't show the data model behind it. Please break the query in manageable small parts and bring more specific questions here with the data model behind it (and I believe SO is still the better place). Help us so that we can help you :) – koriander Mar 28 '13 at 11:30
-2

All time we use lot of SQL queries. But we don’t consider about its performance. If we follow some tips then our query will be more efficient. Some of them are.

  • Use views and stored procedures instead of heavy-duty queries.
  • Try to use constraints instead of triggers, whenever possible.
  • Use table variables instead of temporary tables.
  • Try to use UNION ALL statement instead of UNION

For more http://cybarlab.blogspot.com/2013/02/sql-queries-optimization-tips.html

Thanks n regard

Improve this answer
3
  • Table variables in MySQL? – ypercubeᵀᴹ Mar 30 '13 at 9:03
  • thx for your advice! i already solved it :) – Chris Apr 3 '13 at 11:10
  • 2
    You do not give any valuable information concerning the question. It is not about "a lot of SQL queries" but a specific one. None of the points you list is relevant – Olivier S May 18 '13 at 18:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.