Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

Hi All,

Below is a query that I am running and it is kind of slow (30 + secs). Is there a way to optimize it. Also the query will be incorrect for the month of January, if the franchise did not post anything:

Thank you for your help in advance

Below is a query that I am running and it is kind of slow (30 + secs). Is there a way to optimize it. Also the query will be incorrect for the month of January, if the franchise did not post anything:

```
CREATE TEMPORARY TABLE GrossRev1
SELECT c.ID, IFNULL(R.GrossRevenue,0) AS GROSSREVENUE, IFNULL(R.RoyaltiesOwed,0) ROYALTIESOWED,
IFNULL(week1,0) WEEK1_HRS, IFNULL(week2,0) WEEK2_HRS, IFNULL(week3,0) WEEK3_HRS, IFNULL(week4,0) WEEK4_HRS, IFNULL(week5,0) WEEK5_HRS,
IFNULL(Total_hours,0) TOTAL_HRS, c.year,c.month
FROM
(SELECT a.ID, a.franchise_no, a.closeddate, b.year,b.month FROM franchises AS a,
(SELECT DISTINCT year, month FROM royalties WHERE year = 2015
) as b) as c
LEFT JOIN royalties AS R ON c.id = R.franchiseno AND c.year=R.year AND c.month=R.month
LEFT JOIN (SELECT h.franchiseno AS fn, h.Month, h.year,
MAX(CASE WHEN h.reportweek =1 THEN CAST(h.hoursbilled AS DECIMAL(30,2)) ELSE 0 end) week1,
MAX(CASE WHEN h.reportweek =2 THEN CAST(h.hoursbilled AS DECIMAL(30,2)) ELSE 0 end) week2,
MAX(CASE WHEN h.reportweek =3 THEN CAST(h.hoursbilled AS DECIMAL(30,2)) ELSE 0 end) week3,
MAX(CASE WHEN h.reportweek =4 THEN CAST(h.hoursbilled AS DECIMAL(30,2)) ELSE 0 end) week4,
MAX(CASE WHEN h.reportweek =5 THEN CAST(h.hoursbilled AS DECIMAL(30,2)) ELSE 0 end) week5,
CAST(SUM(h.hoursbilled) AS DECIMAL(30,2)) AS Total_hours
FROM
hours_log h
WHERE h.Year = 2015 -- and h.month = 9
GROUP BY
h.franchiseno,h.reportmonth, h.reportyear) h1 ON h1.fn = R.franchiseno AND h1.ReportMonth = R.Month AND h1.year = R.year
WHERE (c.closeddate IS NULL OR c.closeddate = '0000-00-00')
ORDER BY c.year DESC, c.month DESC,c.ID ASC;
SELECT GR.*,
CASE WHEN GR.GROSSREVENUE = '0.00' AND GR.ROYALTIESOWED = '0.00' THEN R.GROSSREVENUE
ELSE GR.GROSSREVENUE END CURRENT_MONTH_FORECAST_AGR,
CASE WHEN GR.GROSSREVENUE = '0.00' AND GR.ROYALTIESOWED = '0.00' THEN R.RoyaltiesOwed
ELSE GR.ROYALTIESOWED END CURRENT_MONTH_FORECAST_TOTAL_ROYALTIES
FROM GrossRev1 GR
LEFT JOIN (SELECT RL.franchiseno, RL.GROSSREVENUE, RL.RoyaltiesOwed, RL.year, RL.month
FROM royalties_log RL
) R ON R.franchiseno = GR.ID AND R.year = GR.year AND R.month = GR.month - 1;
DROP TEMPORARY TABLE GrossRev1;
```

Thank you for your help in advance

Do more with

EXPERT OFFICE^{®} is a registered trademark of EXPERTS EXCHANGE^{®}

I helped in one question involving the franchise, and royalties tables.

If you can rephrase the question using these two existing tables, I might be able to help.

In the meantime, one needs to find out which part is causing the delay

Have you done an EXPLAIN on the slow query?

EXPLAIN select ....

This is your first tool to help, and it helps us too, so you should do one and provide the result here please.

===

please please please use the explicit "CROSS JOIN" when forming a deliberate Cartesian product (which you are doing as alias c) it will help make your queries more maintainable.

```
select *
FROM
(SELECT a.ID, a.franchise_no, a.closeddate, b.year,b.month FROM franchises AS a,
(SELECT DISTINCT year, month FROM royalties WHERE year = 2015
) as b) as c
LEFT JOIN royalties AS R ON c.id = R.franchiseno AND c.year=R.year AND c.month=R.month
```

It seems to me a totally unnecessary complication to cross join distinct month from royalty to franchise and then join again the whole thing to royalty. That has to be simplified to simple joins with ON clauses.Make sure you have the followingg indexes:

franchises - closeddate

royalties - Year, Month

hours_log - Year, franchiseno, reportmonth, reportyear

You build that cross joined table c and then you left join with R(royalties) on c.ID = R.franchiseno. However

c.ID comes from a.ID, which is franchise table, which also has franchise_no. Why do you join royalties.franchiseno with franchise.ID instead of franchise.franchise_no?

That whole query needs refactored. It si very badly written.

The Cross Join and then Left join was a solution to another question by the same author.

http://www.experts-exchange.com/questions/28744465/MySQL-Left-join-question.html

The author is building on that question.

Properly understanding the current question may lead to some modifications.

@thomasm1948

Can you type few records, and list the required output. Supply the new table structures and the insert statements of the test records.

```
SELECT h.FIN_NUM AS fn, h.rMonth, h.ryear,
MAX(CASE WHEN h.reportweek =1 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week1,
MAX(CASE WHEN h.reportweek =2 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week2,
MAX(CASE WHEN h.reportweek =3 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week3,
MAX(CASE WHEN h.reportweek =4 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week4,
MAX(CASE WHEN h.reportweek =5 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week5,
CAST(SUM(h.hours) AS DECIMAL(30,2)) AS Total_hours
FROM
hours_log h
WHERE h.rYear = 2015
GROUP BY
h.FIN_NUM,h.rmonth, h.ryear
```

This is a new company that I am working for and I cannot use SPs as well. So I am trying to work within the parameters that they have given meIf they don't want SPs is one thing but if they want to pull out data from the database in the form of reports there is no way you can bypass index creation. Beside that it doesn't make any sense because it will not affect the functionality but it WILL affect the performance when they will run these queries and it will be felt by the entire system.

Hours_log:That is your big problem because I believe that is the largest table in terms of records. That is why I asked you about that last query...

no indexes

```
SELECT
GR.franchise_no,
GR.GROSSREV,
GR.WEEK1_HRS,
GR.WEEK2_HRS,
GR.WEEK3_HRS,
GR.WEEK4_HRS,
GR.WEEK5_HRS,
GR.TOTAL_HRS,
GR.ryear,
GR.rmonth,
CASE
WHEN GR.GROSSREV = '0.00' THEN R.GROSSREV
ELSE GR.GROSSREV
END FORECAST_GR
FROM
(
SELECT c.FIN_NUM, c.Franchise_no, IFNULL(R.grossrev,0.00) As grossrev, IFNULL(week1,0) WEEK1_HRS, IFNULL(week2,0) WEEK2_HRS,
IFNULL(week3,0) WEEK3_HRS, IFNULL(week4,0) WEEK4_HRS, IFNULL(week5,0) WEEK5_HRS, IFNULL(Total_hours,0) TOTAL_HRS,
c.ryear, c.rmonth
FROM
(SELECT a.FIN_NUM, a.Franchise_no, b.ryear,b.rmonth FROM franchise as a,
(Select distinct ryear, rmonth from royalties) as b) as c
left join royalties as R on c.FIN_NUM = R.FIN_NUM and c.ryear=R.ryear AND c.rmonth=R.rmonth
LEFT JOIN (SELECT h.FIN_NUM AS fn, h.rMonth, h.ryear,
MAX(CASE WHEN h.reportweek =1 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week1,
MAX(CASE WHEN h.reportweek =2 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week2,
MAX(CASE WHEN h.reportweek =3 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week3,
MAX(CASE WHEN h.reportweek =4 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week4,
MAX(CASE WHEN h.reportweek =5 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week5,
CAST(SUM(h.hours) AS DECIMAL(30,2)) AS Total_hours
FROM
hours_log h
WHERE h.rYear = 2015
GROUP BY
h.FIN_NUM,h.rmonth, h.ryear) h1 ON h1.fn = R.FIN_NUM AND h1.RMonth = R.RMonth AND h1.ryear = R.ryear
)
GR
LEFT JOIN (SELECT RL.FIN_NUM fn, RL.GrossRev, RL.ryear, RL.rmonth
FROM royalties RL
) R ON R.fn = GR.FIN_NUM AND R.ryear = GR.ryear AND R.rmonth = GR.rmonth - 1
order by
GR.franchise_no,
GR.ryear asc,
GR.rmonth;
```

Tell me how long does this take.
```
EXPLAIN
SELECT
GR.franchise_no,
GR.GROSSREV,
GR.WEEK1_HRS,
GR.WEEK2_HRS,
GR.WEEK3_HRS,
GR.WEEK4_HRS,
GR.WEEK5_HRS,
GR.TOTAL_HRS,
GR.ryear,
GR.rmonth,
CASE
WHEN GR.GROSSREV = '0.00' THEN R.GROSSREV
ELSE GR.GROSSREV
END FORECAST_GR
FROM
(
SELECT c.FIN_NUM, c.Franchise_no, ISNULL(R.grossrev,0.00) As grossrev, ISNULL(week1,0) WEEK1_HRS, ISNULL(week2,0) WEEK2_HRS,
ISNULL(week3,0) WEEK3_HRS, ISNULL(week4,0) WEEK4_HRS, ISNULL(week5,0) WEEK5_HRS, ISNULL(Total_hours,0) TOTAL_HRS,
c.ryear, c.rmonth
FROM
(SELECT * from franchise as a,
(Select distinct ryear, rmonth from royalties) as b) as c
left join royalties as R on c.FIN_NUM = R.FIN_NUM and c.ryear=R.ryear AND c.rmonth=R.rmonth
LEFT JOIN (SELECT h.FIN_NUM AS fn, h.rMonth, h.ryear,
MAX(CASE WHEN h.reportweek =1 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week1,
MAX(CASE WHEN h.reportweek =2 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week2,
MAX(CASE WHEN h.reportweek =3 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week3,
MAX(CASE WHEN h.reportweek =4 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week4,
MAX(CASE WHEN h.reportweek =5 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week5,
CAST(SUM(h.hours) AS DECIMAL(30,2)) AS Total_hours
FROM
hours_log h
WHERE h.rYear = 2015
GROUP BY
h.FIN_NUM,h.rmonth, h.ryear) h1 ON h1.fn = R.FIN_NUM AND h1.RMonth = R.RMonth AND h1.ryear = R.ryear
)
GR
LEFT JOIN (SELECT RL.FIN_NUM fn, RL.GrossRev, RL.ryear, RL.rmonth
FROM royalties RL
) R ON R.fn = GR.FIN_NUM AND R.ryear = GR.ryear AND R.rmonth = GR.rmonth - 1
order by
GR.franchise_no,
GR.ryear asc,
GR.rmonth;
```

In regards to your question the best way to go is to use the actual datetime columns instead of Year,Month approach, from which you extract 12 months. There are functions for this:

SELECT (NOW(),INTERVAL -12 MONTH)

1. Current year

2. Last year

3. 12 months from current date

## Premium Content

You need an Expert Office subscription to comment.Start Free Trial