My comments imbedded....
Scott Hamm <[EMAIL PROTECTED]> wrote on 05/03/2005 03:16:31 PM:
> Since I'm learning about Left joins and am trying to figure out a way
> to quickly gather info from Database all at once, here is the SQL
> statement. Please give me feedback how I should improve it for better
> performance. There are around 10 users that will hit database like
> this along with other left joins ever minute. Thanks in advance:
>
First, THANK YOU for formatting your query.
> SELECT
> QA.ID,
> Brands.Brand,
> QA.KeyDate,
> A2.LastName + ', ' + A2.FirstName as Reviewer,
> A1.LastName + ', ' + A1.FirstName as Operator,
> Batch.[order],
> Errortypes.[Description],
> ErrorTypes.points,
> Batch.Comments,
These next two statements have the potential to be evaluated once per row.
However, you are not running a subquery that uses any values from the
outer query so you are in essence computing constants for these next two
columns.
> (SELECT
> sum(Errortypes.points)
> FROM
> ErrorTypes,QAErrors,Batch,QA
> WHERE
> Errortypes.Id=QAErrors.ErrorTypeID
> AND
> QAErrors.ID=Batch.QEID
> AND
> Batch.QAID=QA.ID
> AND
> QA.Batch='464005807') as points,
> (SELECT
> count(*)*100
> FROM
> Batch,QA
> WHERE
> Batch.QAID=QA.ID
> AND
> QA.Batch='464005807') as total
You say you are learning about LEFT joins (which, btw, look just like
INNER joins) but you aren't using INNER JOINS? You are using crappy
Oracle-style comma-separated table lists (ugggh). (It's not "wrong" to do
this, it's just my personal pet peeve)
> FROM
> Brands,Associates A1, Associates A2,QA
> Left Join
> Batch
> ON
> Batch.QAID=QA.ID
> Left Join
> QAErrors
> ON
> QAErrors.ID=Batch.QEID
> Left Join
> Errortypes
> On
> ErrorTypes.id=QAErrors.ErrorTypeID
> WHERE
> QA.Batch='464005807'
> AND
> A1.ID=QA.OperatorID
> AND
> A2.ID=QA.QAID
> AND
> Brands.ID=QA.BrandID;
>
>
> RESULT (delimited by ,):
>
> 100528,Imaged G-5,2005-05-03 00:00:00,Cahall, Margaret,Custis,
> Teresa,244770323,Error in Name,8.0,last name mispelled,24.0,600
> 100528,Imaged G-5,2005-05-03 00:00:00,Cahall, Margaret,Custis,
> Teresa,244770323,Error in Address,8.0,blvd mispelled,24.0,600
> 100528,Imaged G-5,2005-05-03 00:00:00,Cahall, Margaret,Custis,
> Teresa,244770324,Error in Address,8.0,BLVD MISSPELLED,24.0,600
> 100528,Imaged G-5,2005-05-03 00:00:00,Cahall, Margaret,Custis,
> Teresa,244770325,NULL,NULL,NULL,24.0,600
> 100528,Imaged G-5,2005-05-03 00:00:00,Cahall, Margaret,Custis,
> Teresa,244770322,NULL,NULL,NULL,24.0,600
> 100528,Imaged G-5,2005-05-03 00:00:00,Cahall, Margaret,Custis,
> Teresa,244770321,NULL,NULL,NULL,24.0,600
<snip>
See how the last two columns are always the same values. If that is really
what you wanted to compute for those columns, there is an easier way to do
it. Use variables to hold the results of computin those values then just
add them to the SELECT clause of your final query. That way you are
certain that the engine only computes them values once.
I would modify your query to look like this (assuming that the Batch field
on the QA table is really a numeric value and not a string):
SELECT @points := sum(Errortypes.points)
FROM ErrorTypes
INNER JOIN QAErrors
ON Errortypes.Id=QAErrors.ErrorTypeID
INNER JOIN Batch
ON QAErrors.ID=Batch.QEID
INNER JOIN QA
ON Batch.QAID=QA.ID
WHERE QA.Batch=464005807;
SELECT @total := count(1)*100
FROM Batch
INNER JOIN QA
ON Batch.QAID=QA.ID
WHERE QA.Batch=464005807;
SELECT
QA.`ID`,
Brands.`Brand`,
QA.`KeyDate`,
CONCAT(A2.`LastName`,', ',A2.`FirstName`) as Reviewer,
CONCAT(A1.`LastName`,', ',A1.`FirstName`) as Operator,
Batch.`order`,
Errortypes.`Description`,
ErrorTypes.`points`,
Batch.`Comments`,
@points as points,
@total as total
FROM Brands
INNER JOIN QA
ON Brands.ID=QA.BrandID
INNER JOIN Associates A1
ON A1.ID=QA.OperatorID
INNER JOIN Associates A2
ON A2.ID=QA.QAID
Left Join Batch
ON Batch.QAID=QA.ID
Left Join QAErrors
ON QAErrors.ID=Batch.QEID
Left Join Errortypes
ON ErrorTypes.id=QAErrors.ErrorTypeID
WHERE QA.Batch=464005807;
(BTW - I think the original query came from M$ SQL Server. Did it?)
Without knowing more about what it is you are trying to get with this
query, it's hard to make a better guess on how to make it work. The main
issues I saw were:
a) abusing subqueries when variables were more appropriate.
b) using [] instead of `` to delimit reserved field names (not supported
by MySQL).
c) using + as a string concatenator (not supported by MySQL).
d) using the comma-separated form of implicit inner joins and not fully
declared, explicit INNER JOIN clauses.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
