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
