Query optimization - SQL Server Forums

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)

Query optimization

Author

Topic

sardinka
Posting Yak Master

142 Posts

Posted - 2006-03-07 : 14:07:38

How can I optimazed the following query:
(SELECT e.SID
FROM Students s
JOIN Table1 e ON e.SID= s.SID
JOIN Table2 ed ON ed.Enrollment = e.Enrollment
JOIN Table3 t ON t.TNum = e.TNum
JOIN Table4 bt ON bt.TNum = t.TNum
JOIN Table5 b ON b.Batch = bt.Batch
JOIN IPlans i ON i.IPlan = ed.IPlan
JOIN PGroups g ON g.PGroup = i.PGroup

WHERE t.TStatus = 'ACP'
AND ed.EStatus = 'APR'
AND e.SID= (select distinct SID from Table1 where Enrollment=@DpEnrollment))
AND (ed.EffectiveDate =
(SELECT EffectiveDate
FROM Table2 ed JOIN Table1 e
ON e.enrollment=ed.enrollment
WHERE IPlan = @DpIPlan
AND TCoord = @DpTCoord
AND AGCoord = @DpAGCoord
AND DCoord =@DpDCoord )
AND DSeq = @DpDSeq)
AND e.SID =
(select distinct SID from Table1 where Enrollment=@DpEnrollment))
)
AND ed.TerminationDate =
(SELECT TerminationDate
FROM Table2 ed JOIN Table1 e
ON e.enrollment=ed.enrollment
WHERE IPlan = @DpIPlan
AND TCoord = @DpTCoord
AND AGCoord = @DpAGCoord
AND DCoord = @DpDCoord )
AND DSeq = @DpDSeq)
AND e.SID =
(select distinct SID from Table1 where Enrollment=@DpEnrollment))
)
))

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2006-03-07 : 14:55:54

You can try replacing your sub select commands with this.

AND EXISTS(SELECT * FROM Table1 WHERE e.SID = Table1.SID AND Table1.Enrollment = @DpEnrollment)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-03-07 : 15:24:17

That whole WHERE clause is a mess. Break down your SELECT statement into parts and use derived tables and joins.

Without more info, it is hard to know how it should be re-written.

A good, clean SQL statement is not only shorter but also much clearer.

sardinka
Posting Yak Master

142 Posts

Posted - 2006-03-07 : 16:05:32

I could use a exists, but I have to get effectivedate and termination dates based on the param list.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-03-07 : 16:38:48

Again, instead of doing a subselect TWICE in your WHERE clause, should join to a derived table.

Something like:


(SELECT e.SID
FROM Students s
INNER JOIN 
   (SELECT * FROM Table1
    WHERE Enrollment=@DpEnrollment) e ON e.SID= s.SID
INNER JOIN Table2 ed ON ed.Enrollment = e.Enrollment
INNER JOIN Table3 t ON t.TNum = e.TNum
INNER JOIN Table4 bt ON bt.TNum = t.TNum
INNER JOIN Table5 b ON b.Batch = bt.Batch
INNER JOIN IPlans i ON i.IPlan = ed.IPlan
INNER JOIN PGroups g ON g.PGroup = i.PGroup
INNER JOIN 
    (SELECT ed.EffectiveDate, ed.TerminationDate, e.SID, IPlan
     FROM Table2 ed 
     INNER JOIN Table1 e
       ON e.enrollment=ed.enrollment
     WHERE 
         TCoord = @DpTCoord AND
         GCoord = @DpAGCoord AND
         DCoord =@DpDCoord AND
         DSeq = @DpDSeq
     ) x 
   ON 
      ed.TerminationDate = x.terminationDate AND  
      ed.EffectiveDate = x.EffectiveDate AND 
      e.SID = x.SID
WHERE t.TStatus = 'ACP' AND
      ed.EStatus = 'APR'

But it's hard to tell for sure without more information .. you really should step back and re-think the requirements in steps and re-write more clearly. And/or try to explain those requirements very clearly to us, so that we can help you. Right now you have a big mess of a query.

jhermiz

3564 Posts

Posted - 2006-03-07 : 19:38:27

Table names look kind of scary, did someone forget to normalize :).
That WHERE clause is a bit messy, you may want to post sample data and expected results along with all DDL for your db.

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources