0

I'm attempting to query our login log to identify cases where multiple users have logged in to the same IP Address within 30 seconds since July 1, 2017. I'm using datediff on a self join but given the login table has approximately 2.2 million records the query performance is terrible. Can anyone suggest a more efficient query than the sql below.

SELECT TOP 1000 a.[id]
      ,A.[userId]
      ,AName.Firstname + ' ' + AName.LastName as name
      ,A.[username]
      ,A.[site]
      ,A.[loginTime]
      ,A.[ip]
      ,A.[agent]
      ,B.userid
      ,bname.Firstname + ' ' + bname.Lastname
      ,B.username 
      ,B.ip
      ,B.loginTime

  FROM LoginLog as A 
  Inner Join Loginlog As B on a.ip = B.ip and A.userId <> B.userId and DATEDIFF(SECOND, A.loginTime, B.loginTime) < 30 and DATEDIFF(SECOND, A.loginTime, B.loginTime) > 0 
  inner join Employees as AName on A.userId = AName.EmployeeNumber  
  outer join Employees as BName on B.userId = BName.EmployeeNumber 
  where a.userId in (*employee number list*) 
    and a.loginTime > '7/1/2017' 
  order by a.loginTime
Ashley Webb
  • 68
  • 1
  • 8
  • try this https://stackoverflow.com/questions/14504189/datediff-performance – Алексей Мокрев Nov 28 '17 at 19:36
  • 1
    I'm note sure the duplicate applies. It's calculating the difference between a column and a parameter value, not the difference between two columns. – D Stanley Nov 28 '17 at 20:16
  • Do you have indices on `ip`, `userId`, and `loginTime`? If not, add those and see if the performance is acceptable. – D Stanley Nov 28 '17 at 20:17
  • Try `a.LoginTime < b.LoginTime AND a.LoginTime > DATEADD(s,b.LoginTime,-30)`. Inequality joins are generally slow, however. – Bacon Bits Nov 28 '17 at 20:33
  • Stanley - I do have indices on those columns. Thanks for the suggestion though. The outer join is causing the slowness performance issue I think, removing that and it runs in a couple seconds v/s minutes with it. – Ashley Webb Nov 29 '17 at 20:12

0 Answers0