November 18th, 2003, 05:35 PM
SQL query taking forever
I don't understand much about the grouped "AND" and "OR", but here is my query which just does not seem to run:
SELECT distinct s.student_id, classresults_2 t1, classresults_8 t2,schedule c
WHERE ( c."Teacher Last Name"='ADAMS' OR c."Teacher Last Name"='KIRK' ) AND (s.student_id=t1.student_id OR s.student_id=t2.s.student_id) AND (s.student_id=c.student_id);
Basically, I want the student_id from the two tables: classresults_2 and classresults_8, from students who'se teacher last name is "ADAMS" or "KIRK".
It hangs on the (s.student_id=t1.student_id OR s.student_id=t2.student_id)
Here, I'm just asking to find students who have their student_id in classresults_2 OR classresults_8. s.sid is the master student id table, so we're finding students IDs which are also in the master student ID list.
Is there anything wrong with the query?
November 20th, 2003, 12:23 AM
Um... I don't see a FROM clause in your query. Also, you are using the alias "s" for a tablename, but nowhere are you creating this alias. Or is "s" an actual tablename? Also, what is "s.student_id=t2.s.student_id"? (I'm assuming that's a typo, but I'm still surprised your query didn't produce a large error message.)
Maybe you want something like this (I'm guessing you have a table called something like "students"):
Although, I'm not sure about "s.student_id=t1.student_id OR s.student_id=t2.student_id". I would think the join condition needs to be specified for both, not one OR the other. To simplify things, you might want to just do a natural join between these tables, since they all use the same key (student_id). Maybe you could rewrite the query this way:
SELECT distinct s.student_id FROM students s, classresults_2 t1, classresults_8 t2,schedule c
WHERE ( c."Teacher Last Name"='ADAMS' OR c."Teacher Last Name"='KIRK' )
AND (s.student_id=t1.student_id OR s.student_id=t2.student_id)
(OR in the WHERE clause won't work, because "Teacher Last Name" cannot be two values at once anyway. Again, I'm assuming here, but it looks like you want all rows that match both of those conditions, otherwise, just put the one you want to match)
SELECT distinct student_id FROM students
NATURAL JOIN classresults_2
NATURAL JOIN classresults_8
NATURAL JOIN schedule
WHERE schedule."Teacher Last Name"='ADAMS' AND schedule."Teacher Last Name"='KIRK'
I love using natural join syntax when possible, because it expresses relationships so well, without pushing them into the WHERE clause. This leaves your WHERE clause clear for the specific item you are searching.
Again, this is all just speculation because I don't know your exact table definitions, but give it a try, and then let us know if you have any other problems. If there is a specific performance problem, it will probably just be a question of indexing, or re-thinking database design.
November 20th, 2003, 02:24 AM
Thank you for your reply and suggestion. I tried it but it did not work for what I wanted to do. It always returned 0 rows...
What I want to find is: all student_ids who are in classresults_2 OR classresults_8 and from those student_ids, find out if they are taught by ADAMS or KIRK (and this is within the schedule table).
All tables do have the student_id and there exists a student_id index in each table, and I have done vacuum analyze.
I have devised this:
SELECT distinct s.student_id, s."Building Code" from students s, schedule c
WHERE ( c."Teacher Last Name"='ADAMS'
OR c."Teacher Last Name"='KIRK' )
AND s.student_id=c.sid AND
(SELECT student_id FROM classresults_2
UNION ALL SELECT student_id from classresults_8);
However, this takes a long time to query and the load on the server does spike.
Is there any way to make that query more efficient?
Thank you very much for all your help.
Last edited by onefix; November 20th, 2003 at 02:53 AM.