Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.
MySQL query optimization help
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®
Looks like the delay comes from the many calculations involved.
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
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
there are 2 queries, is it the first one that takes 30+ seconds??
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.
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.
Can you explain what is the purpose of this:
Make sure you have the followingg indexes:
franchises - closeddate
royalties - Year, Month
hours_log - Year, franchiseno, reportmonth, reportyear
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
Make sure you have the followingg indexes:
franchises - closeddate
royalties - Year, Month
hours_log - Year, franchiseno, reportmonth, reportyear
Another question related to the same pice of code.
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.
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.
@Zberteoc,
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.
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.
Below is a description for the code:
CREATE TEMPORARY TABLE GrossRev1
Dump the query in a temporary table. The reason why I did this is because I need to get the previous month’s royalties and grossrev in the event that the franchisee did not post for a month. They use it for forecasting
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
This portion allows me to do a left join and fill in the year and month that a franchisee did not post anything. If I do not use this then when I run the query for multiple months then the report does not show exactly which month the franchisee did not post to
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
I join on franchises.id because the franchise_no (this is the actual franchise identification) but the royalty table and hours_log table use the id not the franchise_no…I do not know why they designed it this way.
LEFT JOIN royalties AS R ON c.id = R.franchiseno AND c.year=R.year AND c.month=R.month
This is the hours_log. This is where the franchisee post their weekly hours in a single table. Once again the franchiseno is actually the ID field in the franchises table. I need to show all of weeks in a single row for the year and month. Each record in the table list only the year, month and week number.
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.reportmont
WHERE (c.closeddate IS NULL OR c.closeddate = '0000-00-00')
ORDER BY c.year DESC, c.month DESC,c.ID ASC;
The reason why I did this is because I need to get the previous month’s royalties and grossrev in the event that the franchisee did not post for the current month. They use it for forecasting. I could not think of another way to get this info.
I am stuck at this point as well because January’s calculation will be incorrect do to that it must know find the previous year and Decembers (12) grossrev and royalties
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_TOT
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 all of your help
CREATE TEMPORARY TABLE GrossRev1
Dump the query in a temporary table. The reason why I did this is because I need to get the previous month’s royalties and grossrev in the event that the franchisee did not post for a month. They use it for forecasting
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
This portion allows me to do a left join and fill in the year and month that a franchisee did not post anything. If I do not use this then when I run the query for multiple months then the report does not show exactly which month the franchisee did not post to
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
I join on franchises.id because the franchise_no (this is the actual franchise identification) but the royalty table and hours_log table use the id not the franchise_no…I do not know why they designed it this way.
LEFT JOIN royalties AS R ON c.id = R.franchiseno AND c.year=R.year AND c.month=R.month
This is the hours_log. This is where the franchisee post their weekly hours in a single table. Once again the franchiseno is actually the ID field in the franchises table. I need to show all of weeks in a single row for the year and month. Each record in the table list only the year, month and week number.
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.reportmont
WHERE (c.closeddate IS NULL OR c.closeddate = '0000-00-00')
ORDER BY c.year DESC, c.month DESC,c.ID ASC;
The reason why I did this is because I need to get the previous month’s royalties and grossrev in the event that the franchisee did not post for the current month. They use it for forecasting. I could not think of another way to get this info.
I am stuck at this point as well because January’s calculation will be incorrect do to that it must know find the previous year and Decembers (12) grossrev and royalties
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_TOT
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 all of your help
I am redoing the query using temp table and test data real quick to give a n understanding of what is used. This env does not have a test or dev env
ok this should better explain what I am trying to do
test tables and data
report
test tables and data
CREATE TABLE franchise (
FIN_NUM INT,
Franchise_no INT,
Business_Entity varchar (50)
);
CREATE TABLE Royalties (
FIN_NUM INT,
GrossRev DECIMAL(30,2),
RMonth INT,
RYear Int
);
CREATE TABLE hours_log (
FIN_NUM INT,
Hours INT,
RMonth INT,
RYear INT,
reportweek INT
);
-- insert data franchise
INSERT INTO franchise (FIN_NUM, Franchise_no, Business_Entity) VALUES (0001, 101, 'test1');
INSERT INTO franchise (FIN_NUM, Franchise_no, Business_Entity) VALUES (0002, 102, 'test2');
INSERT INTO franchise (FIN_NUM, Franchise_no, Business_Entity) VALUES (0003, 103,'test3');
-- insert data royalty
INSERT INTO Royalties (FIN_NUM, GrossRev, RMonth, RYear) VALUES (0001, 500, 12, 2014);
INSERT INTO Royalties (FIN_NUM, GrossRev, RMonth, RYear) VALUES (0001, 2000, 2, 2015);
INSERT INTO Royalties (FIN_NUM, GrossRev, RMonth, RYear) VALUES (0002, 2500, 2, 2015);
INSERT INTO Royalties (FIN_NUM, GrossRev, RMonth, RYear) VALUES (0002, 4000, 1, 2015);
INSERT INTO Royalties (FIN_NUM, GrossRev, RMonth, RYear) VALUES (0003, 2000, 1, 2015);
INSERT INTO Royalties (FIN_NUM, GrossRev, RMonth, RYear) VALUES (0003, 1000, 3, 2015);
-- insert data hours_log
INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0001, 10, 12, 2014, 1);
INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0001, 20, 12, 2014, 2);
INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0001, 25, 12, 2014, 3);
INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0001, 25, 12, 2014, 4);
INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0002, 60, 1, 2015, 1);
INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0002, 60, 1, 2015, 2);
INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0002, 40, 1, 2015, 3);
INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0002, 40, 1, 2015, 4);
INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0001, 40, 2, 2015, 1);
INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0001, 40, 2, 2015, 2);
INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0001, 45, 2, 2015, 3);
INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0001, 47, 2, 2015, 4);
INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0002, 50, 2, 2015, 1);
INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0002, 65, 2, 2015, 2);
INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0002, 55, 2, 2015, 3);
INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0002, 70, 2, 2015, 4);
INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0003, 25, 1, 2015, 1);
INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0003, 30, 1, 2015, 2);
INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0003, 25, 1, 2015, 3);
INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0003, 25, 1, 2015, 4);
INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0003, 10, 3, 2015, 1);
INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0003, 20, 3, 2015, 2);
INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0003, 25, 3, 2015, 3);
INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0003, 25, 3, 2015, 4);
report
CREATE TEMPORARY TABLE test1
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
order by c.ryear DESC, c.rmonth DESC,c.FIN_NUM ASC;
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
-- R.AdjGrossRevenue, R.RoyaltiesOwed
FROM test1 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;
DROP TEMPORARY TABLE test1;
No the database that I am working does not. I recommended them but I was told do not put them in. There are no FKs as well.
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 me
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 me
Well, that is your answer. If you don't have the right indexes there is no way you can get performance. Can at least they give you a list with the indexes that exits on those 3 tables?
Tell me how long it takes to run only this query:
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
the only ones that I see is:
Royalties Table:
1 rmonth
2 Franchiseno, ryear, rmonth
Hours_log:
no indexes
Franchise:
1. franchise_no
Royalties Table:
1 rmonth
2 Franchiseno, ryear, rmonth
Hours_log:
no indexes
Franchise:
1. franchise_no
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.
.1 secs to run on the real database
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
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
But on th eother hand you said it came up in 1 sec... that is not bad. You said in your question that you waited 30 secs. This would suggest that not the hours_log table is the problem but if it doesn't have indexes is a bit weird.
Ok, let's try this. Instead of using that temporary table we will jut use a full query:
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;
here are the results
with temp table
Affected rows: 6,084 Found rows: 6,084 Warnings: 0 Duration for 3 queries: 4.727 sec. (+ 27.643 sec. network)
without
Affected rows: 0 Found rows: 6,084 Warnings: 0 Duration for 1 query: 31.434 sec. (+ 2.325 sec. network)
with temp table
Affected rows: 6,084 Found rows: 6,084 Warnings: 0 Duration for 3 queries: 4.727 sec. (+ 27.643 sec. network)
without
Affected rows: 0 Found rows: 6,084 Warnings: 0 Duration for 1 query: 31.434 sec. (+ 2.325 sec. network)
You should try the "full" query multiple times not just once. Also run an EXPLAIN on it and see the results:
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;
It is faster after running it about five time. it takes about 3 secs. huge improvement
My last part of the code for selecting the previous grossrev if the franchisee did not post anything. how would I have it select the previous year and 12 (month) if the current month is 1
currently the code is
Which i know is incorrect
My last part of the code for selecting the previous grossrev if the franchisee did not post anything. how would I have it select the previous year and 12 (month) if the current month is 1
currently the code is
EFT 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
Which i know is incorrect
When you run a query like this for the first time is inconclusive, you need to run it few times to actually see the duration. As a general idea, when compare 2 different queries with the original one you will need to run them both same amount of times, 4-5 should be enough.
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)
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)
Let's clarify. What period you want to look back?
1. Current year
2. Last year
3. 12 months from current date
1. Current year
2. Last year
3. 12 months from current date
I would like to have be able to do a date range.
For example let's say we had a date range from 1/2013 to 10/2015 then the current query would be correct for all but 1/2015, 1/1014 and 1/2013 if a franchisee did not post anything for those months
The query will have to look at the month and if its 1 for the month then it will have to look at the previous year and December. For example 1/2015 will have to get 12/2014 Grossrev for its forecast
For example let's say we had a date range from 1/2013 to 10/2015 then the current query would be correct for all but 1/2015, 1/1014 and 1/2013 if a franchisee did not post anything for those months
The query will have to look at the month and if its 1 for the month then it will have to look at the previous year and December. For example 1/2015 will have to get 12/2014 Grossrev for its forecast
Thanks for the response:
I applied your supplied sql and created the current tables and the test data. This includes franchise (3 records), hours_log (24 records), and royalties (6 records).
List the required output.
I applied your supplied sql and created the current tables and the test data. This includes franchise (3 records), hours_log (24 records), and royalties (6 records).
List the required output.
The interval is (1 = January, 2 = February, 3 = March, 4 = April, etc) and the four digit year ie 2015
Premium Content
You need an Expert Office subscription to comment.Start Free Trial