Improvement in SQL statement sought.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Improvement in SQL statement sought.

Author

Topic

LinuxGold
Starting Member

3 Posts

Posted - 2005-05-03 : 15:51:19

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:

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,
(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
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

Power to people, Linux is here.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources