September 20, 2005 at 8:06 am
Ok Gurus:
I may be doing this all wrong but here is an attempt at it .. I am required to select a set of users not referenced in any of the tables and here is how I wrote the query..
Obviously this is not the most optimal way I am looking for suggestions.
I appreciate your advise..
SELECT Users.UserUId,Users.SystemID FROM dbo.Users
where not exists (
--1. Table Systemlog
(SELECT distinct SystemLog.UserUId FROM dbo.SystemLog
where SystemLog.UserUId =Users.UserUId
--2. Table Security
or not exists(select distinct Security.UserUId from Security
where Security.UserUId = users.UserUId
--3. Table Reschedule
or not exists(select distinct Reschedule.UserUId from Reschedule
where dbo.Reschedule.UserUId = dbo.Users.UserUId
--4. Table Notes
or not exists(select distinct Notes.UserUId from Notes
where Notes.UserUId = Users.UserUId
--5. Table ErrorLog
or not exists(select distinct ErrorLog.UserUId from ErrorLog
where ErrorLog.UserUId = Users.UserUId
--6. Table DocumentRequest
or not exists(Select distinct DocumentRequest.UserUId from DocumentRequest
where DocumentRequest.UserUId = Users.UserUId
--7. Table CaseTypes_Drivers
or not exists(select distinct CaseTypes_Drivers.EmployeeUId from CaseTypes_Drivers
where dbo.CaseTypes_Drivers.EmployeeUId =dbo.Users.UserUId
--8. Table CaseData_Steps
or not exists(select distinct CaseData_Steps.EmployeeUId from CaseData_Steps
where CaseData_Steps.EmployeeUId = dbo.Users.UserUId
--9. Table CaseDataBK
or not exists((select distinct dbo.CaseDataBK.CaseManagerUId from CaseDataBK
where dbo.CaseDataBK.AttorneyUId=dbo.Users.UserUId
AND dbo.CaseDataBK.CaseManagerUId =dbo.Users.UserUId
) ) ) ) ) ) ) ) )
))
order by users.SystemID
September 20, 2005 at 8:07 am
Sounds about right... make sure that the keys are all indexed and that'll be pretty all you can do to optimize this.
September 20, 2005 at 8:17 am
This is definately not the correct approach a simple select shows that one of the record does exist in the list of tables. Remember users is the superset and the criteria is to delete those records that are not referenced in any of the tables..
If I change the or not exists to Union it works well?
I guess I need to analyze that
September 21, 2005 at 1:01 am
If you want to find records that have no connection to any single of the other tables, you have to use AND NOT EXISTS (instead of OR). Your query above would show all records that don't have corresponding record in at least one of the tables.
Also, you can achieve the same as with NOT EXISTS using the LEFT JOIN syntax with IS NULL in WHERE condition, like this:
SELECT Users.UserUId,Users.SystemID
FROM Users
LEFT JOIN SystemLog ON SystemLog.UserUId = Users.UserUId
LEFT JOIN Security ON Security.UserUId = Users.UserUId
LEFT JOIN Reschedule ON Reschedule.UserUId = Users.UserUId
WHERE SystemLog.UserUId IS NULL /*no rows in systemlog*/
AND Security.UserUId IS NULL /*no rows in security*/
AND Reschedule.UserUId IS NULL /*no rows in reschedule*/
...... etc. I find this syntax better than multiple EXISTS, because the query is shorter, and - when commented - easily understandable.
Vladan
September 21, 2005 at 1:14 am
Hi.
You could alternatively also try the query as below. The "WHERE" at the bottom will ensure that only records that do not exist in any of the union queries will be returned.
SELECT distinct Users.UserUId,Users.SystemID FROM dbo.Users LEFT OUTER JOIN
(
--1. Table Systemlog
(SELECT distinct SystemLog.UserUId AS UID FROM dbo.SystemLog )
UNION
--2. Table Security
(select distinct Security.UserUId AS UID from Security )
UNION
--3. Table Reschedule
(select distinct Reschedule.UserUId AS UID from Reschedule )
UNION
--4. Table Notes
(select distinct Notes.UserUId AS UID from Notes )
UNION
--5. Table ErrorLog
(select distinct ErrorLog.UserUId AS UID from ErrorLog )
UNION
--6. Table DocumentRequest
(Select distinct DocumentRequest.UserUId AS UID from DocumentRequest )
UNION
--7. Table CaseTypes_Drivers
(select distinct CaseTypes_Drivers.EmployeeUId AS UID from CaseTypes_Drivers )
UNION
--8. Table CaseData_Steps
(select distinct CaseData_Steps.EmployeeUId AS UID from CaseData_Steps )
UNION
--9. Table CaseDataBK
(select distinct dbo.CaseDataBK.CaseManagerUId AS UID from CaseDataBK )
UNION
(select distinct dbo.CaseDataBK.AttorneyUId AS UID from CaseDataBK )
) A
ON
Users.UserUId = A.uid
WHERE
A.UID is null
order by users.SystemID
September 21, 2005 at 1:14 am
Hmmm... I looked once more at your original query, and I think - if I understand correctly what you want to achieve - there are several things that would profit from a change, in case you prefer to use this style... First, the parentheses, and second, in connection with NOT EXISTS it is (AFAIK) best practice to use SELECT *.
SELECT Users.UserUId,Users.SystemID
FROM dbo.Users
WHERE
--1. Table Systemlog
NOT EXISTS (SELECT * FROM dbo.SystemLog where SystemLog.UserUId =Users.UserUId)
--2. Table Security
AND NOT EXISTS (SELECT * FROM Security WHERE Security.UserUId = users.UserUId)
--3. Table Reschedule
AND NOT EXISTS (SELECT * FROM Reschedule WHERE dbo.Reschedule.UserUId = dbo.Users.UserUId)
..... etc.
September 21, 2005 at 6:50 am
The union and the left outer join was the most optimal way.. I had tried the union however the where clause does make it much more efficient ..
Execution time is lessened from 6 min to 30 seconds. Now that surely is a solution..
Thanks..
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply