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:

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;

Open in new window


Thank you for your help in advance
thomasm1948Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Hamed NasrRetired IT ProfessionalCommented:
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
PortletPaulEE Topic AdvisorCommented:
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.
ZberteocCommented:
Can you explain what is the purpose of this:
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

Open in new window

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
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

ZberteocCommented:
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.
Hamed NasrRetired IT ProfessionalCommented:
@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.
thomasm1948Author Commented:
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.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;

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_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 all of your help
thomasm1948Author Commented:
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
thomasm1948Author Commented:
ok this should better explain what I am trying to do

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);
        
   

Open in new window


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;

Open in new window

ZberteocCommented:
Do you have the indexes i suggested you?
thomasm1948Author Commented:
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
ZberteocCommented:
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?
ZberteocCommented:
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

Open in new window

ZberteocCommented:
I am not worried about FKs, indexes matter here!
thomasm1948Author Commented:
the only ones that I see is:

Royalties Table:
1 rmonth
2 Franchiseno, ryear, rmonth

Hours_log:
no indexes

Franchise:
1. franchise_no
ZberteocCommented:
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
If 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.
thomasm1948Author Commented:
.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

Open in new window

ZberteocCommented:
Hours_log:
no indexes
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...
thomasm1948Author Commented:
Yes that is correct and i agree with you
thomasm1948Author Commented:
Is there a better way write the code that you would recommend
ZberteocCommented:
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.
ZberteocCommented:
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;

Open in new window

Tell me how long does this take.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
thomasm1948Author Commented:
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)
ZberteocCommented:
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;

Open in new window

thomasm1948Author Commented:
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

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

Open in new window


Which i know is incorrect
ZberteocCommented:
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)
ZberteocCommented:
Let's clarify. What period you want to look back?

1. Current year
2. Last year
3. 12 months from current date
thomasm1948Author Commented:
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
Hamed NasrRetired IT ProfessionalCommented:
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.
ZberteocCommented:
I am not sure I understand why the ones from month 1 would not work if the rest do.,
thomasm1948Author Commented:
the table doesn't have a datetime field.  it has only month and year both are int
ZberteocCommented:
I am still puzzled how the interval works and what are you looking for in an interval.
thomasm1948Author Commented:
The interval is (1 = January, 2 = February, 3 = March, 4 = April, etc) and the four digit year ie 2015
ZberteocCommented:
I understood that. So you look at one moth at a time? I don't see that anywhere? Don't you look at an interval like past 6 or 12 months, or the last year data?

What I didn't understand is why is a problem for month=1?
thomasm1948Author Commented:
Thank you for all of your help
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.