I have a question regarding query efficiency in a bigger database, in this case mySQL but could be Oracle or SQL Server or whatever.
I have a query which to me looks awfull....
Now this is a sample offcourse and only has 4 errors in reality there are 25 of these subqueries
In my brain this would seem more efficient:
However the people that support this query are sure that the initial query is the way to go... despite its seeming inefficient way of doing things. Though they are unable/willing to share exactly why this is true.
Just for FYI the table contains A LOT of records, dont know how many but probably in excess of 100 million and growing.
So I am hoping to find someone that can enlighten me... Which query is more efficient and why?
I have a query which to me looks awfull....
Code:
Select Cust.CustID, Custname, ErrorNoIP.Errors ErrorNoIP, ErrorNoName.Errors ErrorNoName, ErrorNoX.Errors ErrorNoX, ErrorNoY.Errors ErrorNoY
From ( select CustID, CustName from TableX) as Cust
left join (select Custid, Count(*) as Errors from TableX where ErrorID = 10 Group by CustID) ErrorNoIP on Cust.CustID = ErrorNoIP.CustID
left join (select Custid, Count(*) as Errors from TableX where ErrorID = 20 Group by CustID) ErrorNoName on Cust.CustID = ErrorNoName.CustID
left join (select Custid, Count(*) as Errors from TableX where ErrorID = 32 Group by CustID) ErrorNoX on Cust.CustID = ErrorNoX.CustID
left join (select Custid, Count(*) as Errors from TableX where ErrorID = 25 Group by CustID) ErrorNoY on Cust.CustID = ErrorNoY.CustID
In my brain this would seem more efficient:
Code:
select CustID
, CustName
, sum(Case when ErrorID = 10 then 1 Else 0 End ) ErrorNoIP
, sum(Case when ErrorID = 20 then 1 Else 0 End ) ErrorNoName
, sum(Case when ErrorID = 32 then 1 Else 0 End ) ErrorNoX
, sum(Case when ErrorID = 25 then 1 Else 0 End ) ErrorNoY
from TableX
where ErrorID in (10,20,25,32)
Group by CustID, CustName
However the people that support this query are sure that the initial query is the way to go... despite its seeming inefficient way of doing things. Though they are unable/willing to share exactly why this is true.
Just for FYI the table contains A LOT of records, dont know how many but probably in excess of 100 million and growing.
So I am hoping to find someone that can enlighten me... Which query is more efficient and why?