Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2013
    Posts
    7

    Unanswered: Help me make some efficient code !

    I have what I believe to be some highly un-efficient code (lots repeated) - please can someone help me write it a little more efficiently. Thanks

    Nagog
    code is basically...

    SELECT
    ('12 : 1-Dec-2012 to 30-Nov-2013 : ' + CAST(Count( central_enquiry.enquiry_time) as NVARCHAR(10) )) Total,
    (SELECT
    CAST(Count( central_enquiry.enquiry_time) as NVARCHAR(10) ) as Qty
    FROM
    central_enquiry,
    site
    WHERE
    central_enquiry.site_code = site.site_code AND
    central_enquiry.enquiry_time >= '01-DEC-2012 00:00:01' AND
    central_enquiry.enquiry_time < '30-NOV-2013 23:59:59' AND
    site.contract_area_code = 'CW01' ) as CW01,

    (SELECT
    CAST(Count( central_enquiry.enquiry_time) as NVARCHAR(10) ) as Qty
    FROM
    central_enquiry,
    site
    WHERE
    central_enquiry.site_code = site.site_code AND
    central_enquiry.enquiry_time >= '01-DEC-2012 00:00:01' AND
    central_enquiry.enquiry_time < '30-NOV-2013 23:59:59' AND
    site.contract_area_code = 'CW02' ) as CW02,

    (SELECT
    CAST(Count( central_enquiry.enquiry_time) as NVARCHAR(10) ) as Qty
    FROM
    central_enquiry,
    site
    WHERE
    central_enquiry.site_code = site.site_code AND
    central_enquiry.enquiry_time >= '01-DEC-2012 00:00:01' AND
    central_enquiry.enquiry_time < '30-NOV-2013 23:59:59' AND
    site.contract_area_code = 'CW3' ) as CW3,


    (SELECT
    CAST(Count( central_enquiry.enquiry_time) as NVARCHAR(10) ) as Qty
    FROM
    central_enquiry,
    site
    WHERE
    central_enquiry.site_code = site.site_code AND
    central_enquiry.enquiry_time >= '01-DEC-2012 00:00:01' AND
    central_enquiry.enquiry_time < '30-NOV-2013 23:59:59' AND
    site.contract_area_code = 'CW04' ) as CW04,

    (SELECT
    CAST(Count( central_enquiry.enquiry_time) as NVARCHAR(10) ) as Qty
    FROM
    central_enquiry,
    site
    WHERE
    central_enquiry.site_code = site.site_code AND
    central_enquiry.enquiry_time >= '01-DEC-2012 00:00:01' AND
    central_enquiry.enquiry_time < '30-NOV-2013 23:59:59' AND
    site.contract_area_code = 'CW5' ) as CW5

    FROM
    central_enquiry,
    site
    WHERE
    central_enquiry.site_code = site.site_code AND
    site.contract_area_code IN ('CW01', 'CW02', 'CW3', 'CW04', 'CW5') AND
    central_enquiry.enquiry_time >= '01-DEC-2012 00:00:01' AND
    central_enquiry.enquiry_time < '30-NOV-2013 23:59:59'

    UNION SELECT
    ('11 : 1-Nov-2012 to 31-Oct-2013 : ' + CAST(Count( central_enquiry.enquiry_time) as NVARCHAR(10) )) Total,
    (SELECT
    CAST(Count( central_enquiry.enquiry_time) as NVARCHAR(10) ) as Qty
    FROM
    central_enquiry,
    site
    WHERE
    central_enquiry.site_code = site.site_code AND
    central_enquiry.enquiry_time >= '01-NOV-2012 00:00:01' AND
    central_enquiry.enquiry_time < '31-OCT-2013 23:59:59' AND
    site.contract_area_code = 'CW01' ) as CW01,

    (SELECT
    CAST(Count( central_enquiry.enquiry_time) as NVARCHAR(10) ) as Qty
    FROM
    central_enquiry,
    site
    WHERE
    central_enquiry.site_code = site.site_code AND
    central_enquiry.enquiry_time >= '01-NOV-2012 00:00:01' AND
    central_enquiry.enquiry_time < '31-OCT-2013 23:59:59' AND
    site.contract_area_code = 'CW02' ) as CW02,

    (SELECT
    CAST(Count( central_enquiry.enquiry_time) as NVARCHAR(10) ) as Qty
    FROM
    central_enquiry,
    site
    WHERE
    central_enquiry.site_code = site.site_code AND
    central_enquiry.enquiry_time >= '01-NOV-2012 00:00:01' AND
    central_enquiry.enquiry_time < '31-OCT-2013 23:59:59' AND
    site.contract_area_code = 'CW3' ) as CW3,


    (SELECT
    CAST(Count( central_enquiry.enquiry_time) as NVARCHAR(10) ) as Qty
    FROM
    central_enquiry,
    site
    WHERE
    central_enquiry.site_code = site.site_code AND
    central_enquiry.enquiry_time >= '01-NOV-2012 00:00:01' AND
    central_enquiry.enquiry_time < '31-OCT-2013 23:59:59' AND
    site.contract_area_code = 'CW04' ) as CW04,

    (SELECT
    CAST(Count( central_enquiry.enquiry_time) as NVARCHAR(10) ) as Qty
    FROM
    central_enquiry,
    site
    WHERE
    central_enquiry.site_code = site.site_code AND
    central_enquiry.enquiry_time >= '01-NOV-2012 00:00:01' AND
    central_enquiry.enquiry_time < '31-OCT-2013 23:59:59' AND
    site.contract_area_code = 'CW5' ) as CW5




    FROM
    central_enquiry,
    site
    WHERE
    central_enquiry.site_code = site.site_code AND
    site.contract_area_code IN ('CW01', 'CW02', 'CW3', 'CW04', 'CW5') AND
    central_enquiry.enquiry_time >= '01-NOV-2012 00:00:01' AND
    central_enquiry.enquiry_time < '31-OCT-2013 23:59:59'

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    SELECT periods.lbound
         , periods.ubound
         , Count(central_enquiry.enquiry_time) As total
         , Sum(CASE WHEN central_enquiry.enquiry_time = 'CW01' THEN 1 ELSE 0 END) As CW01
         , Sum(CASE WHEN central_enquiry.enquiry_time = 'CW02' THEN 1 ELSE 0 END) As CW02
         , Sum(CASE WHEN central_enquiry.enquiry_time = 'CW3'  THEN 1 ELSE 0 END) As CW3
         , Sum(CASE WHEN central_enquiry.enquiry_time = 'CW04' THEN 1 ELSE 0 END) As CW04
         , Sum(CASE WHEN central_enquiry.enquiry_time = 'CW5'  THEN 1 ELSE 0 END) As CW5
    FROM   central_enquiry
     INNER
      JOIN site
        ON site.site_code = central_enquiry.site_code
     INNER
      JOIN (
            SELECT Cast('2012-12-01' As datetime) As lbound
                 , Cast('2013-12-01' As datetime) As ubound
            UNION ALL
            SELECT Cast('2012-11-01' As datetime) As lbound
                 , Cast('2013-11-01' As datetime) As ubound
           ) As periods
        ON central_enquiry.enquiry_time >= periods.lbound
       AND central_enquiry.enquiry_time < periods.ubound
    WHERE  site.contract_area_code IN ('CW01', 'CW02', 'CW3', 'CW04', 'CW5')
    GROUP
        BY periods.lbound
         , periods.ubound
    ?
    Last edited by gvee; 11-12-13 at 07:23.
    George
    Home | Blog

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    when you say
    I believe to be some highly un-efficient code
    what actually makes you think its inefficient
    what is it that is making you concerned about efficiency

    how do you define efficiency?
    you seem to infer that because
    lots repeated = highly un-efficient code
    , but it may be, it may not be as we don't know your applicaiton

    efficiency is usually described in service times, how long to retrieve data, how much resource the query consumes and so on


    have you used EXPLAIN to try to undsestand the cost of the query plan

    you can make queries more efficient by PREPARING or possibly using a stored procedure

    in terms of neatenss (IE human readability) you could use the BETWEEN syntax instead of column > x and column <y but that will have little or no impact on efficiency no matter how you define it

    without understanding your data model it may be quicker to run a single group by query for the CW1..CW5 codes and then integrate that data into your application elsewhere. there is no loaw that says you MUST do everything inside a query, sometimes its just as efficient to do the final processes in your front end or middleware
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Updated my previous query. Test and see if the results are the same.
    George
    Home | Blog

  5. #5
    Join Date
    Feb 2013
    Posts
    7
    Hi George,
    thanks for your reply. after some minor details, I have managed to get the code you provied to work... Code as below.
    Code:
    SELECT 
     periods.lbound,
     periods.ubound,
    
    CAST (  Count(site.contract_area_code) as NVARCHAR(10) ) total,
          Sum(CASE WHEN site.contract_area_code = 'CW01' THEN 1 ELSE 0 END) as CW01,
          Sum(CASE WHEN site.contract_area_code = 'CW02' THEN 1 ELSE 0 END) as CW02,
          Sum(CASE WHEN site.contract_area_code = 'CW3'  THEN 1 ELSE 0 END) as CW3,
          Sum(CASE WHEN site.contract_area_code = 'CW04' THEN 1 ELSE 0 END) as CW04,
          Sum(CASE WHEN site.contract_area_code = 'CW5'  THEN 1 ELSE 0 END) as CW5
    FROM   central_enquiry
     INNER  JOIN site    ON site.site_code = central_enquiry.site_code
     INNER  JOIN ( SELECT '01-DEC-2012 00:00:01')as lbound, '30-NOV-2013 23:59:59' as ubound
       UNION ALL   SELECT '01-NOV-2012 00:00:01' as lbound, '31-OCT-2013 23:59:59' as ubound
       UNION ALL   SELECT '01-OCT-2012 00:00:01' as lbound, '30-SEP-2013 23:59:59' as ubound
       UNION ALL   SELECT '01-SEP-2012 00:00:01' as lbound, '31-AUG-2013 23:59:59' as ubound
       UNION ALL   SELECT '01-AUG-2012 00:00:01' as lbound, '31-JUL-2013 23:59:59' as ubound
       UNION ALL   SELECT '01-JUL-2012 00:00:01' as lbound, '30-JUN-2013 23:59:59' as ubound
       UNION ALL   SELECT '01-JUN-2012 00:00:01' as lbound, '31-MAY-2013 23:59:59' as ubound
       UNION ALL   SELECT '01-MAY-2012 00:00:01' as lbound, '30-APR-2013 23:59:59' as ubound
       UNION ALL   SELECT '01-APR-2012 00:00:01' as lbound, '31-MAR-2013 23:59:59' as ubound
       UNION ALL   SELECT '01-MAR-2012 00:00:01' as lbound, '28-FEB-2013 23:59:59' as ubound
       UNION ALL   SELECT '01-FEB-2012 00:00:01' as lbound, '31-JAN-2013 23:59:59' as ubound
       UNION ALL   SELECT '01-JAN-2012 00:00:01' as lbound, '31-DEC-2012 23:59:59' as ubound
       ) as periods 
       ON central_enquiry.enquiry_time >= periods.lbound  AND central_enquiry.enquiry_time < periods.ubound
    WHERE  site.contract_area_code IN ('CW01', 'CW02', 'CW3', 'CW04', 'CW5') 
    GROUP BY periods.lbound, periods.ubound
    Thanks for all your help !

    Barry
    Last edited by gvee; 11-12-13 at 09:26. Reason: Added [/code] block

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Hi Barry,

    As a minor note I would drop the time portion off the dates in the period and reflect the change in the join condition. If you want to include all data for a day then that's
    Code:
    >= yyyy-mm-dd 00:00:00
    <  yyyy-mm-dd(+1) 00:00:00
    So that's everything after midnight and before the following midnight i.e. a full day.

    Last up: do you understand the code I posted? Please ask any questions you might have!
    George
    Home | Blog

  7. #7
    Join Date
    Feb 2013
    Posts
    7
    Hi George, as you suggest, I will drop the time portion off. Yes, I do understand the code you produced. Very helpful.

    This produced results as expected below.
    Many thanks. for all your help.
    Code:
    lbound	ubound	total	cw01	cw02	cw3	cw04	cw5
    01-MAR-2012 00:00:01	28-FEB-2013 23:59:59	33180	8530	3749	6469	8367	6065
    01-MAY-2012 00:00:01	30-APR-2013 23:59:59	33440	8602	3796	6374	8614	6054
    01-JUL-2012 00:00:01	30-JUN-2013 23:59:59	32687	8219	3937	6148	8516	5867
    01-DEC-2012 00:00:01	30-NOV-2013 23:59:59	28581	7003	3490	5438	7377	5273
    01-OCT-2012 00:00:01	30-SEP-2013 23:59:59	30267	7500	3752	5654	7950	5411
    01-SEP-2012 00:00:01	31-AUG-2013 23:59:59	30975	7748	3797	5805	8175	5450
    01-JAN-2012 00:00:01	31-DEC-2012 23:59:59	32463	8435	3608	6649	7782	5989
    01-FEB-2012 00:00:01	31-JAN-2013 23:59:59	33059	8613	3689	6567	8097	6093
    01-AUG-2012 00:00:01	31-JUL-2013 23:59:59	31989	8017	3905	6037	8386	5644
    01-APR-2012 00:00:01	31-MAR-2013 23:59:59	33297	8550	3728	6479	8470	6070
    01-JUN-2012 00:00:01	31-MAY-2013 23:59:59	33043	8330	3885	6260	8579	5989
    01-NOV-2012 00:00:01	31-OCT-2013 23:59:59	30165	7400	3671	5704	7881	5509
    Last edited by gvee; 11-12-13 at 09:26. Reason: added [/code] block

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Code:
         , Sum(CASE WHEN central_enquiry.enquiry_time = 'CW01' THEN 1 ELSE 0 END) As CW01
    may be equivalent to
    Code:
         , COUNT(CASE WHEN central_enquiry.enquiry_time = 'CW01' THEN 1 END) As CW01
    or
    Code:
         , COUNT(CASE central_enquiry.enquiry_time WHEN 'CW01' THEN 1 END) As CW01
    or
    Code:
         , COUNT(CASE central_enquiry.enquiry_time WHEN 'CW01' THEN 0 END) As CW01

    I don' know which might be efficient, but the latter was shorter than the former.

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    The reason I chose Sum() over Count() is that I think other people find it easier to understand. Obviously this is completely subjective and the equivalent methods you have shared are just as relevant
    George
    Home | Blog

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The behavior of COUNT(or SUM) funcion and CASE function are defined clearly in manuals.

    For exampe:

    COUNT(expr)

    Returns a count of the number of non-NULL values of expr in the rows retrieved by a SELECT statement. The result is a BIGINT value.
    MySQL :: MySQL 5.6 Reference Manual :: 12.17.1 GROUP BY (Aggregate) Functions

    CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN result ...] [ELSE result] END
    CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END

    The first version returns the result where value=compare_value.
    The second version returns the result for the first condition that is true.
    If there was no matching result value, the result after ELSE is returned, or NULL if there is no ELSE part.
    MySQL :: MySQL 5.6 Reference Manual :: 12.4 Control Flow Functions


    I think that persons who want to learn SQL should understand
    (1) what is the NULL value and
    (2) handling of NULL value in the language(including in functions, in conditions, so on...) and
    (3) effective use of null values.
    (Obviously this is completely subjective.)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •