DirtyHairy
Programmer
Hello,
I am trying to run this on SQL Server 2000. I have 5 tables set up with these fields, in addition to others:
[tt]table ug
--------
ugid
table u
-------
uid
fname
lname
table s
-------
uid
cid
total_score
completed
counter
table uga
---------
ugid
uid
table ugl
---------
ugid
uid[/tt]
What I need in the recordset is ug.ugid, u.uid, u.fname, u.lname, s.total_score, s.completed, s.counter, a field indicating if the ugid/uid combo is in the uga table, and a field indicating if the ugid/uid combo is in the ugl table. The constraints are that I need the entire list of ugids (possibly indicating an outer join), and the s.cid will be a constant for all rows (where s.cid='cid'). I also need the recordset ordered by ugid. There might be more then one row for each ugid, so it will need to be ordered by ugid. The only rows I need returned are ones where the ugid/uid combo appears in either the uga or ugl table, and I also need to know which it appears in.
I can do this with 2 queries, I can get the list of ugids first and then the information for each one, but with the size of the data I am working with, that report takes around 30 minutes to complete. So, it's not exactly the best choice.
Does anyone know how to get all of the information with 1 query, or would it be better to rethink the way the database is set up in the first place? There are certain constraints on the database that make a complete redesign impossible, but I would probably be able to redo the way I am storing the ugl information.
Right now this is how I have the code set up, which is written in ASP/VBscript:
Obviously, having the query inside the loop is destroying efficiency.
I am trying to run this on SQL Server 2000. I have 5 tables set up with these fields, in addition to others:
[tt]table ug
--------
ugid
table u
-------
uid
fname
lname
table s
-------
uid
cid
total_score
completed
counter
table uga
---------
ugid
uid
table ugl
---------
ugid
uid[/tt]
What I need in the recordset is ug.ugid, u.uid, u.fname, u.lname, s.total_score, s.completed, s.counter, a field indicating if the ugid/uid combo is in the uga table, and a field indicating if the ugid/uid combo is in the ugl table. The constraints are that I need the entire list of ugids (possibly indicating an outer join), and the s.cid will be a constant for all rows (where s.cid='cid'). I also need the recordset ordered by ugid. There might be more then one row for each ugid, so it will need to be ordered by ugid. The only rows I need returned are ones where the ugid/uid combo appears in either the uga or ugl table, and I also need to know which it appears in.
I can do this with 2 queries, I can get the list of ugids first and then the information for each one, but with the size of the data I am working with, that report takes around 30 minutes to complete. So, it's not exactly the best choice.
Does anyone know how to get all of the information with 1 query, or would it be better to rethink the way the database is set up in the first place? There are certain constraints on the database that make a complete redesign impossible, but I would probably be able to redo the way I am storing the ugl information.
Right now this is how I have the code set up, which is written in ASP/VBscript:
Code:
ugcon.open("SELECT ugid FROM usergroups ORDER BY ugid ASC")
...
for i = LBound(uglist) to UBound(uglist)
if ereg(uglist(i), "[A-Z]{2}[0-9]{3}", true) = true then
s = "SELECT u.uid, u.fname, u.lname, s.total_score, s.completed, s.counter, 'Y' AS ugadmin FROM "
s = s & "(users u LEFT OUTER JOIN content_session s ON "
s = s & "u.uid=s.uid AND s.cid='BOB-MAN') "
s = s & "WHERE u.uid IN (SELECT uid FROM usergroup_admins WHERE ugid='" & uglist(i) & "') "
s = s & "UNION "
s = s & "SELECT u.uid, u.fname, u.lname, s.total_score, s.completed, s.counter, ' ' AS ugadmin FROM "
s = s & "(users u LEFT OUTER JOIN content_session s ON "
s = s & "u.uid=s.uid AND s.cid='BOB-MAN') "
s = s & "WHERE u.uid NOT IN (SELECT uid FROM usergroup_admins WHERE ugid='" & uglist(i) & "') AND u.uid IN (SELECT uid FROM user_grouplist WHERE ugid='" & uglist(i) & "')"
usercon.open(s)
...
Obviously, having the query inside the loop is destroying efficiency.