Optimize SQL Script - 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)

Optimize SQL Script

Author

Topic

digitald
Starting Member

5 Posts

Posted - 2006-05-10 : 15:32:20

Hello,

I have a page that reads information from three tables

Student:
StudentID, FirstName, LastName

Enrollment:
EnrollID, StudentID, RegistrationDate, AcceptedDate, Eligible(Yes,No)

Status:
StatusID, EnrollID, Description(Pending,Accepted,Disqualified), StatusDate

A typical query would be:
"Get the lastest acceptance date of all eligible students that registered between the dates 5/5/06 and 5/8/06 and have been accepted into the program."

Currently, when a student registers, the enrollment (Eligible) field is initially set to 'No'. And there is no status record associated with the enrollment. I tried to combine the student and enrollment tables using a JOIN, but duplicates were created with different registration date.

Any help would be appreciated.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-10 : 15:39:20

Please show us what you have done so far for your homework assignment. You at least need to show that you tried to do it on your own first.

Tara Kizer
aka tduggan

digitald
Starting Member

5 Posts

Posted - 2006-05-10 : 16:09:39

quote:
Originally posted by tkizer

Please show us what you have done so far for your homework assignment. You at least need to show that you tried to do it on your own first.

Tara Kizer
aka tduggan

The following SQL runs first:
SELECT StudentID, LastName, FirstName
FROM Student
WHERE StudentID IN
(SELECT StudentID FROM Enrollment
WHERE RegistrationDate >= '5/5/06'
AND RegistrationDate <= '5/8/06')
AND StudentID NOT IN (SELECT StudentID
FROM Enrollment
WHERE Eligible = 'No')

The above script displays the student's last and first name.

The following script is run next, which accepts a student id as a parameter:
SELECT DISTINCT
StudentID, StatusID, Eligible, RegistrationDate, AcceptedDate
FROM Enrollment
WHERE StudentID = 34
AND AcceptedDate = (SELECT MAX(AcceptedDate)
FROM Enrollment
WHERE StudentID = 34)
AND StudentID NOT IN (SELECT StudentID
FROM Enrollment
WHERE Eligible = 'No'
AND StudentID = 34)
GROUP BY
StudentID, StatusID, Eligible, RegistrationDate, AcceptedDate

This script shows the registration date, the accepted date, and their eligibility. That's it. I am trying to figure out how to combine the two scipts and improve the tme to display the results.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-10 : 16:12:27

I don't understand why you want to combine them. Could you provide sample data to illustrate your problem?

Tara Kizer
aka tduggan

digitald
Starting Member

5 Posts

Posted - 2006-05-10 : 16:33:52

quote:
Originally posted by tkizer

I don't understand why you want to combine them. Could you provide sample data to illustrate your problem?

Tara Kizer
aka tduggan

For instance, a student could have two enrollment records:
EnrolID, StudentID, RegistrationDate, AcceptedDate, Eligible
1, 34, 12/15/05, 01/03/06, Yes
2, 34, 05/06/06, NULL, No

And a student could have two status records as well:
StatusID, EnrollID, Description, StatusDate
1, 1, Pending, 12/17/06
2, 1, Accepted, 01/03/06

Is there a loop command is MSSQL that will allow me to get accepted student records and ignore enrollment record that have no associated status? The script slows down reading 36 student records.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-10 : 16:48:49

Does this work for you:


SELECT 
	s.EnrollID, s.StudentID, s.RegistrationDate, s.AcceptedDate, 
	s.Eligible, e.StatusID, e.[Description], e.StatusDate
FROM Student s
INNER JOIN Enrollment e
ON s.EnrollID = e.EnrollID
WHERE AcceptedDate IS NOT NULL

Tara Kizer
aka tduggan

digitald
Starting Member

5 Posts

Posted - 2006-05-10 : 18:05:09

quote:

Originally posted by tkizer

Does this work for you:


SELECT 
	s.EnrollID, s.StudentID, s.RegistrationDate, s.AcceptedDate, 
	s.Eligible, e.StatusID, e.[Description], e.StatusDate
FROM Student s
INNER JOIN Enrollment e
ON s.EnrollID = e.EnrollID
WHERE AcceptedDate IS NOT NULL

Tara Kizer
aka tduggan

I still needed to get the lastest accepted date, so I modified the second SELECT:

SELECT
e.StudentID, e.EnrollID, e.RegistrationDate, e.AcceptedDate
FROM Enrollment e
INNER JOIN Status s ON s.EnrollID = s.EnrollID
WHERE e.AcceptedDate = (SELECT MAX(AcceptedDate)
FROM Enrollment
WHERE StudentID = 34)
AND e.StudentID = 34

It is running little faster. The JOIN seems to be doing the trick.

Kristen
Test

22859 Posts

Posted - 2006-05-11 : 04:33:45

I think the outer stuff is in scope for the inner stuff, if so you could make it more generic by using (there is also a typo in your code):


SELECT e1.StudentID, e1.EnrollID, e1.RegistrationDate, e1.AcceptedDate
FROM dbo.Enrollment AS e1
     INNER JOIN dbo.Status AS s 
         ON s.EnrollID = se1.EnrollID
WHERE e1.AcceptedDate = 
      (
          SELECT MAX(AcceptedDate) 
          FROM   dbo.Enrollment AS e2
          WHERE  e2.StudentID = e1.StudentID
      )
      AND e1.StudentID = 34

Specifying the "owner" (presumably dbo) may give you a small gain in performance

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources