[RESOLVED] Insane query?

[ixalmida]

Maybe it's just me, but I dislike relying on a huge query to do all the math and processing that my code should probably do. I'd like to get opinions on the sanity of the following query (yes, this is just one query I'm trying to parse), versus breaking it up into smaller queries and doing some processing in code:

SELECT Installed.Division Division, Installed.Location, IF(WO.Total_Sell_BAS IS NULL, 0, WO.Total_Sell_BAS) + IF(Installed.Total_Sell_BAS IS NULL, 0, Installed.Total_Sell_BAS) + IF(CSP.Total_Sell_BAS IS NULL, 0, CSP.Total_Sell_BAS) AS Total_Sell_BAS, IF(WO.Total_GM_BAS IS NULL, 0, WO.Total_GM_BAS) + IF(Installed.Total_GM_BAS IS NULL, 0, Installed.Total_GM_BAS) + IF(CSP.Total_GM_BAS IS NULL, 0, CSP.Total_GM_BAS)AS Total_GM_BAS, IF(WO.Total_Sell_Fire IS NULL, 0, WO.Total_Sell_Fire) + IF(Installed.Total_Sell_Fire IS NULL, 0, Installed.Total_Sell_Fire) + IF(CSP.Total_Sell_Fire IS NULL, 0, CSP.Total_Sell_Fire) AS Total_Sell_Fire, IF(WO.Total_GM_Fire IS NULL, 0, WO.Total_GM_Fire) + IF(Installed.Total_GM_Fire IS NULL, 0, Installed.Total_GM_Fire) + IF(CSP.Total_GM_Fire IS NULL, 0, CSP.Total_GM_Fire) AS Total_GM_Fire, IF(WO.Total_Sell_Sec IS NULL, 0, WO.Total_Sell_Sec) + IF(Installed.Total_Sell_Sec IS NULL, 0, Installed.Total_Sell_Sec) + IF(CSP.Total_Sell_Sec IS NULL, 0, CSP.Total_Sell_Sec) AS Total_Sell_Sec, IF(WO.Total_GM_Sec IS NULL, 0, WO.Total_GM_Sec) + IF(Installed.Total_GM_Sec IS NULL, 0, Installed.Total_GM_Sec) + IF(CSP.Total_GM_Sec IS NULL, 0, CSP.Total_GM_Sec) AS Total_GM_Sec, IF(WO.Total_Sell_Other IS NULL, 0, WO.Total_Sell_Other) + IF(Installed.Total_Sell_Other IS NULL, 0, Installed.Total_Sell_Other) + IF(CSP.Total_Sell_Other IS NULL, 0, CSP.Total_Sell_Other) AS Total_Sell_Other, IF(WO.Total_GM_Other IS NULL, 0, WO.Total_GM_Other) + IF(Installed.Total_GM_Other IS NULL, 0, Installed.Total_GM_Other) + IF(CSP.Total_GM_Other IS NULL, 0, CSP.Total_GM_Other) AS Total_GM_Other, IF(WO.Total_Sell IS NULL, 0, WO.Total_Sell) + IF(Installed.Total_Sell IS NULL, 0, Installed.Total_Sell) + IF(CSP.Total_Sell IS NULL, 0, CSP.Total_Sell) AS Total_Sell, IF(WO.Total_GM IS NULL, 0, WO.Total_GM) + IF(Installed.Total_GM IS NULL, 0, Installed.Total_GM) + IF(CSP.Total_GM IS NULL, 0, CSP.Total_GM) AS Total_GM FROM (SELECT SUBSTRING(BR_CSP_Bookings.Contract_Number, 1, 3) Division, SUM(IF(Contract_Type RLIKE '[A-Z]B', Contract_Amount, 0)) Total_Sell_BAS, SUM(IF(Contract_Type RLIKE '[A-Z]B', Contract_Amount - Contract_Estimate, 0)) Total_GM_BAS, SUM(IF(Contract_Type RLIKE '[A-Z]F', Contract_Amount, 0)) Total_Sell_Fire, SUM(IF(Contract_Type RLIKE '[A-Z]F', Contract_Amount - Contract_Estimate, 0)) Total_GM_Fire, SUM(IF(Contract_Type RLIKE '[A-Z]S', Contract_Amount, 0)) Total_Sell_Sec, SUM(IF(Contract_Type RLIKE '[A-Z]S', Contract_Amount - Contract_Estimate, 0)) Total_GM_Sec, SUM(IF(Contract_Type RLIKE '[A-Z]M', Contract_Amount, 0)) Total_Sell_Other, SUM(IF(Contract_Type RLIKE '[A-Z]M', Contract_Amount - Contract_Estimate, 0)) Total_GM_Other, SUM(IF(Contract_Type RLIKE '[A-Z][BFMS]', Contract_Amount, 0)) Total_Sell, SUM(IF(Contract_Type RLIKE '[A-Z][BFMS]', Contract_Amount - Contract_Estimate, 0)) Total_GM FROM BR_CSP_Bookings LEFT JOIN BR_All_CSP USING (Contract_Number, Site_ID) WHERE Book_Date >= '2009-03-01' AND Book_Date <= '2009-03-25' GROUP BY SUBSTRING(BR_CSP_Bookings.Contract_Number, 1, 3))AS CSP RIGHT JOIN ((SELECT SUBSTRING(Division, 2, 3) Division, SUM(IF(Division RLIKE '[0-9]{4}E', Total_Revenue, 0)) Total_Sell_BAS, SUM(IF(Division RLIKE '[0-9]{4}E', Total_Revenue - Total_Cost, 0)) Total_GM_BAS, SUM(IF(Division RLIKE '[0-9]{4}F', Total_Revenue, 0)) Total_Sell_Fire, SUM(IF(Division RLIKE '[0-9]{4}F', Total_Revenue - Total_Cost, 0)) Total_GM_Fire, SUM(IF(Division RLIKE '[0-9]{4}G', Total_Revenue, 0)) Total_Sell_Sec, SUM(IF(Division RLIKE '[0-9]{4}G', Total_Revenue - Total_Cost, 0)) Total_GM_Sec, SUM(IF(Division RLIKE '[0-9]{4}H', Total_Revenue, 0)) Total_Sell_Other, SUM(IF(Division RLIKE '[0-9]{4}H', Total_Revenue - Total_Cost, 0)) Total_GM_Other, SUM(IF(Division RLIKE '[0-9]{4}[EFGH]', Total_Revenue, 0)) Total_Sell, SUM(IF(Division RLIKE '[0-9]{4}[EFGH]', Total_Revenue - Total_Cost, 0)) Total_GM FROM BR_All_WO WHERE Complete_Date >= '2009-03-01' AND Complete_Date <= '2009-03-25' GROUP BY SUBSTRING(Division, 2, 3))AS WO RIGHT JOIN (SELECT BR_Bookings.Division, Plan_Goals.Location, SUM(if(Job_Number RLIKE '[0-9]{3}[BM][a-z][0-9]{4}' AND Book_Date >= '2009-03-01' AND Book_Date <= '2009-03-25', Revised_Contract, 0)) Total_Sell_BAS, SUM(if(Job_Number RLIKE '[0-9]{3}F[a-z][0-9]{4}' AND Book_Date >= '2009-03-01' AND Book_Date <= '2009-03-25', Revised_Contract, 0)) Total_Sell_Fire, SUM(if(Job_Number RLIKE '[0-9]{3}S[a-z][0-9]{4}' AND Book_Date >= '2009-03-01' AND Book_Date <= '2009-03-25', Revised_Contract, 0)) Total_Sell_Sec, SUM(if(Job_Number NOT RLIKE '[0-9]{3}[BMFS][a-z][0-9]{4}' AND Book_Date >= '2009-03-01' AND Book_Date <= '2009-03-25', Revised_Contract, 0)) Total_Sell_Other, SUM(if(Book_Date >= '2009-03-01' AND Book_Date <= '2009-03-25', Revised_Contract, 0)) Total_Sell, SUM(if(Job_Number RLIKE '[0-9]{3}[BM][a-z][0-9]{4}' AND Book_Date >= '2009-03-01' AND Book_Date <= '2009-03-25', Revised_Contract - Total_Est, 0)) Total_GM_BAS, SUM(if(Job_Number RLIKE '[0-9]{3}F[a-z][0-9]{4}' AND Book_Date >= '2009-03-01' AND Book_Date <= '2009-03-25', Revised_Contract - Total_Est, 0)) Total_GM_Fire, SUM(if(Job_Number RLIKE '[0-9]{3}S[a-z][0-9]{4}' AND Book_Date >= '2009-03-01' AND Book_Date <= '2009-03-25', Revised_Contract - Total_Est, 0)) Total_GM_Sec, SUM(if(Job_Number NOT RLIKE '[0-9]{3}[BMFS][a-z][0-9]{4}' AND Book_Date >= '2009-03-01' AND Book_Date <= '2009-03-25', Revised_Contract - Total_Est, 0)) Total_GM_Other, SUM(if(Book_Date >= '2009-03-01' AND Book_Date <= '2009-03-25', Revised_Contract - Total_Est, 0)) Total_GM FROM BR_Bookings LEFT JOIN Plan_Goals ON BR_Bookings.Division = Plan_Goals.Division WHERE BR_Bookings.Division != 0 GROUP BY BR_Bookings.Division) AS Installed ON WO.Division = Installed.Division) ON CSP.Division = Installed.Division

[dagon]

I wouldn't do it that way, to hard to debug

[Krik]

WOW!

I would bet that query returns a monstrous list of data as well. Meaning it could be broke up to retrieve smaller more relevant chunks of data.

On a side note it looks like it maybe accessing data from more than one database (I maybe wrong). Which if that is the case there is bigger mess than just a huge overly cumbersome SQL query.

On a positive note you know someone that is very good with SQL if you every have a really tough question :rolleyes: .

[dagon]

I have read a few articles that say mysql does a lot of things faster than php, so it may run faster than php; haven't really investigated this but do some times move some work from php to mysql (adding days and other date manipulation seems to be easier in sql.

[ixalmida]

On a positive note you know someone that is very good with SQL if you every have a really tough question.

I like your attitude!

Thankfully, this query doesn't span databases, but the whole database is whacked and needs to be redesigned. I don't even have a testing server yet, so I'm stuck working with some very ugly code. I'm frantically working to move the production server to a new/updated VM, but I'm having some python script conversion challenges.

I have read a few articles that say mysql does a lot of things faster than php

I'll Google that. I imagine some things are faster in MySQL, but I have my doubts about some math and conditional functions.

[Weedpacket]

Views? User functions? Some attempt at normalisation or even consistent coding style? Here's a slightly formatted view.

SELECT
	Installed.Division Division, Installed.Location,
	IF(WO.Total_Sell_BAS IS NULL, 0, WO.Total_Sell_BAS) + IF(Installed.Total_Sell_BAS IS NULL, 0, Installed.Total_Sell_BAS) + IF(CSP.Total_Sell_BAS IS NULL, 0, CSP.Total_Sell_BAS) AS Total_Sell_BAS,
	IF(WO.Total_GM_BAS IS NULL, 0, WO.Total_GM_BAS) + IF(Installed.Total_GM_BAS IS NULL, 0, Installed.Total_GM_BAS) + IF(CSP.Total_GM_BAS IS NULL, 0, CSP.Total_GM_BAS)AS Total_GM_BAS, IF(WO.Total_Sell_Fire IS NULL, 0, WO.Total_Sell_Fire) + IF(Installed.Total_Sell_Fire IS NULL, 0, Installed.Total_Sell_Fire) + IF(CSP.Total_Sell_Fire IS NULL, 0, CSP.Total_Sell_Fire) AS Total_Sell_Fire,
	IF(WO.Total_GM_Fire IS NULL, 0, WO.Total_GM_Fire) + IF(Installed.Total_GM_Fire IS NULL, 0, Installed.Total_GM_Fire) + IF(CSP.Total_GM_Fire IS NULL, 0, CSP.Total_GM_Fire) AS Total_GM_Fire,
	IF(WO.Total_Sell_Sec IS NULL, 0, WO.Total_Sell_Sec) + IF(Installed.Total_Sell_Sec IS NULL, 0, Installed.Total_Sell_Sec) + IF(CSP.Total_Sell_Sec IS NULL, 0, CSP.Total_Sell_Sec) AS Total_Sell_Sec,
	IF(WO.Total_GM_Sec IS NULL, 0, WO.Total_GM_Sec) + IF(Installed.Total_GM_Sec IS NULL, 0, Installed.Total_GM_Sec) + IF(CSP.Total_GM_Sec IS NULL, 0, CSP.Total_GM_Sec) AS Total_GM_Sec,
	IF(WO.Total_Sell_Other IS NULL, 0, WO.Total_Sell_Other) + IF(Installed.Total_Sell_Other IS NULL, 0, Installed.Total_Sell_Other) + IF(CSP.Total_Sell_Other IS NULL, 0, CSP.Total_Sell_Other) AS Total_Sell_Other,
	IF(WO.Total_GM_Other IS NULL, 0, WO.Total_GM_Other) + IF(Installed.Total_GM_Other IS NULL, 0, Installed.Total_GM_Other) + IF(CSP.Total_GM_Other IS NULL, 0, CSP.Total_GM_Other) AS Total_GM_Other,
	IF(WO.Total_Sell IS NULL, 0, WO.Total_Sell) + IF(Installed.Total_Sell IS NULL, 0, Installed.Total_Sell) + IF(CSP.Total_Sell IS NULL, 0, CSP.Total_Sell) AS Total_Sell,
	IF(WO.Total_GM IS NULL, 0, WO.Total_GM) + IF(Installed.Total_GM IS NULL, 0, Installed.Total_GM) + IF(CSP.Total_GM IS NULL, 0, CSP.Total_GM) AS Total_GM
FROM
(
	SELECT
		SUBSTRING(BR_CSP_Bookings.Contract_Number, 1, 3) Division,
		SUM(IF(Contract_Type RLIKE '[A-Z]B', Contract_Amount, 0)) Total_Sell_BAS,
		SUM(IF(Contract_Type RLIKE '[A-Z]B', Contract_Amount - Contract_Estimate, 0)) Total_GM_BAS,
		SUM(IF(Contract_Type RLIKE '[A-Z]F', Contract_Amount, 0)) Total_Sell_Fire,
		SUM(IF(Contract_Type RLIKE '[A-Z]F', Contract_Amount - Contract_Estimate, 0)) Total_GM_Fire,
		SUM(IF(Contract_Type RLIKE '[A-Z]S', Contract_Amount, 0)) Total_Sell_Sec,
		SUM(IF(Contract_Type RLIKE '[A-Z]S', Contract_Amount - Contract_Estimate, 0)) Total_GM_Sec,
		SUM(IF(Contract_Type RLIKE '[A-Z]M', Contract_Amount, 0)) Total_Sell_Other,
		SUM(IF(Contract_Type RLIKE '[A-Z]M', Contract_Amount - Contract_Estimate, 0)) Total_GM_Other,
		SUM(IF(Contract_Type RLIKE '[A-Z][BFMS]', Contract_Amount, 0)) Total_Sell,
		SUM(IF(Contract_Type RLIKE '[A-Z][BFMS]', Contract_Amount - Contract_Estimate, 0)) Total_GM
	FROM
		BR_CSP_Bookings
	LEFT JOIN
		BR_All_CSP USING (Contract_Number, Site_ID)
	WHERE
		Book_Date >= '2009-03-01'
	AND Book_Date <= '2009-03-25'
	GROUP BY
		SUBSTRING(BR_CSP_Bookings.Contract_Number, 1, 3)
) AS CSP
RIGHT JOIN
(
	(
		SELECT
			SUBSTRING(Division, 2, 3) Division,
			SUM(IF(Division RLIKE '[0-9]{4}E', Total_Revenue, 0)) Total_Sell_BAS,
			SUM(IF(Division RLIKE '[0-9]{4}E', Total_Revenue - Total_Cost, 0)) Total_GM_BAS,
			SUM(IF(Division RLIKE '[0-9]{4}F', Total_Revenue, 0)) Total_Sell_Fire,
			SUM(IF(Division RLIKE '[0-9]{4}F', Total_Revenue - Total_Cost, 0)) Total_GM_Fire,
			SUM(IF(Division RLIKE '[0-9]{4}G', Total_Revenue, 0)) Total_Sell_Sec,
			SUM(IF(Division RLIKE '[0-9]{4}G', Total_Revenue - Total_Cost, 0)) Total_GM_Sec,
			SUM(IF(Division RLIKE '[0-9]{4}H', Total_Revenue, 0)) Total_Sell_Other,
			SUM(IF(Division RLIKE '[0-9]{4}H', Total_Revenue - Total_Cost, 0)) Total_GM_Other,
			SUM(IF(Division RLIKE '[0-9]{4}[EFGH]', Total_Revenue, 0)) Total_Sell,
			SUM(IF(Division RLIKE '[0-9]{4}[EFGH]', Total_Revenue - Total_Cost, 0)) Total_GM
		FROM
			BR_All_WO
		WHERE
			Complete_Date >= '2009-03-01'
		AND Complete_Date <= '2009-03-25'
		GROUP BY
			SUBSTRING(Division, 2, 3)
	) AS WO
	RIGHT JOIN
	(
		SELECT
			BR_Bookings.Division,
			Plan_Goals.Location,
			SUM(if(Job_Number RLIKE '[0-9]{3}[BM][a-z][0-9]{4}' AND Book_Date >= '2009-03-01' AND Book_Date <= '2009-03-25', Revised_Contract, 0)) Total_Sell_BAS,
			SUM(if(Job_Number RLIKE '[0-9]{3}F[a-z][0-9]{4}' AND Book_Date >= '2009-03-01' AND Book_Date <= '2009-03-25', Revised_Contract, 0)) Total_Sell_Fire,
			SUM(if(Job_Number RLIKE '[0-9]{3}S[a-z][0-9]{4}' AND Book_Date >= '2009-03-01' AND Book_Date <= '2009-03-25', Revised_Contract, 0)) Total_Sell_Sec,
			SUM(if(Job_Number NOT RLIKE '[0-9]{3}[BMFS][a-z][0-9]{4}' AND Book_Date >= '2009-03-01' AND Book_Date <= '2009-03-25', Revised_Contract, 0)) Total_Sell_Other,
			SUM(if(Book_Date >= '2009-03-01' AND Book_Date <= '2009-03-25', Revised_Contract, 0)) Total_Sell,
			SUM(if(Job_Number RLIKE '[0-9]{3}[BM][a-z][0-9]{4}' AND Book_Date >= '2009-03-01' AND Book_Date <= '2009-03-25', Revised_Contract - Total_Est, 0)) Total_GM_BAS,
			SUM(if(Job_Number RLIKE '[0-9]{3}F[a-z][0-9]{4}' AND Book_Date >= '2009-03-01' AND Book_Date <= '2009-03-25', Revised_Contract - Total_Est, 0)) Total_GM_Fire,
			SUM(if(Job_Number RLIKE '[0-9]{3}S[a-z][0-9]{4}' AND Book_Date >= '2009-03-01' AND Book_Date <= '2009-03-25', Revised_Contract - Total_Est, 0)) Total_GM_Sec,
			SUM(if(Job_Number NOT RLIKE '[0-9]{3}[BMFS][a-z][0-9]{4}' AND Book_Date >= '2009-03-01' AND Book_Date <= '2009-03-25', Revised_Contract - Total_Est, 0)) Total_GM_Other,
			SUM(if(Book_Date >= '2009-03-01' AND Book_Date <= '2009-03-25', Revised_Contract - Total_Est, 0)) Total_GM
		FROM
			BR_Bookings
		LEFT JOIN
			Plan_Goals ON BR_Bookings.Division = Plan_Goals.Division
		WHERE
			BR_Bookings.Division != 0
		GROUP BY
			BR_Bookings.Division
	) AS Installed ON WO.Division = Installed.Division
) ON CSP.Division = Installed.Division

Makes it look even hairier, in my opinion

[Roger_Ramjet]

dagon;10908564 wrote:

I have read a few articles that say mysql does a lot of things faster than php, so it may run faster than php; haven't really investigated this but do some times move some work from php to mysql (adding days and other date manipulation seems to be easier in sql.

SQL is documented as 10 - 20 times faster than any programming/scripting language when processing multiple records. This is because database engines have been optimised for just this purpose. In a case like this, using multiple calls to the db would slow things down even further.

However, queries should be optimised themselves. Use Explain to see what is going on if you find your query is v slow. You can also look into query cache and pdo prepared statements if this query is run often.

[dagon]

i wasn't referring directly to processing records but to functions such as string and date functions.

[Roger_Ramjet]

dagon;10908998 wrote:

i wasn't referring directly to processing records but to functions such as string and date functions.

Yep, I see that now in what you said.

[ixalmida]

Agreed. Normalization is definately needed here. It's a major task since this system keeps historical data going back to 2002, but it has to happen. It will happen once I get the testing server up.

Also, great feedback from Roger. Good points all. You've answered my question thoroughly.

[Weedpacket]

ixalmida wrote:

Normalization is definately needed here. It's a major task since this system keeps historical data going back to 2002

Those Contract_Type, Division, and Job_Number fields, for instance. There's clearly a lot of information being packed into them in a database-unfriendly way. That important-looking [BFMS] letter in the Job_Number for instance is crying for its own indexed column in a Job_Number table (keep the full strings around somewhere, possibly for the fkey relationship: that's actually something of a denormalisation, but I'm guessing the full number is used a lot elsewhere already).

sprocs and views would also make it more legible.

[ixalmida]

Tell me about it. The guy who created this database created a salesperson table where multiple office locations are listed in a text field: "Los Angeles, San Francisco, Seattle, (...)" None of the tables ever got past 1NF. The worst part is that there is no validation code on the front end, so if one of the many "admins" enters, "Lahs Anjelas" in the text box, it stays that way until someone takes notice and fixes it.

It is going to take a while, but at least I've got some job security...

---

Just to keep myself humble, I'll admit to making a major design blunder in my own application (separate from the above system). I made the mistake of having a single-tiered security system and thinking I could trust three other people with "admin" rights (CEO, CFO, VP). A month later I noticed there were 30 people around the country that had been given "admin" access and could see and access anything I could. I was forced to implement a second "tier" of special admin rights to limit access to those functions I never intended anyone else to touch.

[Roger_Ramjet]

DB Security

User -> Roles
Roles -> DB Access and Privileges

Separation of roles fromm users allows you to modify permissions on a global scale via roles and also to modify users by changing their role.