SQL USER ID (NOT IN) -- Query Help - timeout

I need to speed this query up. No matter what I try, I cannot filter out the UserId's that are in the role 'V2_Corporate'

There are multiple roles assigned to the user, I need to know the logins in the EventLog table where user is not in the 'V2_Corporate' role...

Who can help me speed this query up as it currently takes 28 secs to run...

SELECT UL.LogUserID as [UserId], U.FirstName, COUNT(*) AS LoginCount
            FROM dbo.EventLog UL
                        Inner Join dbo.Users U on U.UserId = UL.LogUserID
            WHERE  ((LogCreateDate >= '9/1/2013')
                        AND (LogCreateDate <= '9/30/2013'))
                        AND (LogTypeKey = 'LOGIN_SUCCESS')
                        AND (UL.LogUserID NOT IN
                                (SELECT UR.UserID
                                    FROM dbo.UserRoles UR
                                    INNER JOIN dbo.Roles R ON UR.RoleID = R.RoleID
                              WHERE      (R.RoleName LIKE 'V2_Corporate%')))
            GROUP BY UL.LogUserID, U.FirstName
            ORDER BY logincount DESC
simonajbartAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
assuming that you have indexes on UserId and loginUserID columns


SELECT UL.LogUserID as [UserId], U.FirstName, COUNT(*) AS LoginCount
FROM dbo.EventLog UL
            Inner Join dbo.Users U on U.UserId = UL.LogUserID
WHERE  ((LogCreateDate >= '9/1/2013')
            AND (LogCreateDate <= '9/30/2013'))
            AND (LogTypeKey = 'LOGIN_SUCCESS')
            AND  NOT EXISTS(
                     SELECT 1
                        FROM dbo.UserRoles UR
                        INNER JOIN dbo.Roles R ON UR.RoleID = R.RoleID and UR.UserID = UL.LogUserID
                    WHERE      (R.RoleName LIKE 'V2_Corporate%'))
GROUP BY UL.LogUserID, U.FirstName
ORDER BY logincount DESC
0
Brendt HessSenior DBACommented:
When returning a list of items, I prefer not to use the NOT EXISTS construct, as it is not as efficient as a LEFT JOIN construct.  Assuming the indexes that aneeshattingfal assumed:

EventLog:  Index on LogUserID
Users:  Index on UserID

And also assuming these additional indexes exist:

UserRole:  Index on RoleID, UserID
Roles:  Index on RoleName, INCLUDE (RoleID)

Then this query should be optimally efficient:

SELECT UL.LogUserID as [UserId], 
	U.FirstName, 
	COUNT(*) AS LoginCount
FROM dbo.EventLog UL
INNER JOIN dbo.Users U 
	ON U.UserId = UL.LogUserID
LEFT JOIN (
	SELECT UR.UserID
	FROM  dbo.Roles R 	
	INNER JOIN dbo.UserRoles UR
		ON UR.RoleID = R.RoleID 
	WHERE R.RoleName LIKE 'V2_Corporate%'
	) roleFilter
	ON U.UserId = RoleFilter.UserId
WHERE LogCreateDate >= '9/1/2013'		-- Consider using the universal format '20130901'
	AND LogCreateDate <= '9/30/2013' 
	AND LogTypeKey = 'LOGIN_SUCCESS'
	AND roleFilter.UserID IS NULL

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
simonajbartAuthor Commented:
Thanks All -- very helpful, timely and efficient... cheer

Final code ::

SELECT TOP 10 UL.LogUserID as [UserId], U.FirstName, COUNT(*) AS LoginCount
FROM dbo.EventLog UL
INNER JOIN dbo.Users U ON U.UserId = UL.LogUserID
LEFT JOIN (
      SELECT UR.UserID
      FROM  dbo.Roles R       
      INNER JOIN dbo.UserRoles UR
            ON UR.RoleID = R.RoleID WHERE (R.RoleName LIKE 'V2_Corporate%')  
      ) roleFilter
      ON U.UserId = RoleFilter.UserId WHERE (LogCreateDate >= @StartDate AND LogCreateDate <= @EndDate)
      AND LogTypeKey = 'LOGIN_SUCCESS'
      AND roleFilter.UserID IS NULL
GROUP BY UL.LogUserID, U.FirstName
ORDER BY LoginCount desc
0
simonajbartAuthor Commented:
SELECT TOP 10 UL.LogUserID as [UserId], U.FirstName, COUNT(*) AS LoginCount
FROM dbo.EventLog UL
INNER JOIN dbo.Users U ON U.UserId = UL.LogUserID
LEFT JOIN (
      SELECT UR.UserID
      FROM  dbo.Roles R       
      INNER JOIN dbo.UserRoles UR
            ON UR.RoleID = R.RoleID WHERE (R.RoleName LIKE 'V2_Corporate%')  
      ) roleFilter
      ON U.UserId = RoleFilter.UserId WHERE (LogCreateDate >= @StartDate AND LogCreateDate <= @EndDate)
      AND LogTypeKey = 'LOGIN_SUCCESS'
      AND roleFilter.UserID IS NULL
GROUP BY UL.LogUserID, U.FirstName
ORDER BY LoginCount desc
0
Aneesh RetnakaranDatabase AdministratorCommented:
@bhess
>I prefer not to use the NOT EXISTS construct, as it is not as efficient as a LEFT JOIN construct.
It is the other way, NOT EXISTS is faster than LEFT JOIN  and NOT IN , and in some cases LEFT JOIN is the worst among these three, the main reason is the filter condition comes at the end (Where column is null ) eliminates the records. while NOT EXISTS and NOT NULL filters out the mismatching records as it comes.

http://weblogs.sqlteam.com/peterl/archive/2009/06/12/Timings-of-different-techniques-for-finding-missing-records.aspx

@Simon,
Can you compare the results using all these three and post the results.

Aneesh
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.