Opinions/Advise on performance of a Query to get Login Information

• vinu512

  SSCoach

  Points: 15729

  More actions

  November 28, 2012 at 10:59 pm

  #393694

  Hi Everyone,

  I'll start with posting DDL and Sample data :

  --DDL

  Create Table Ex

  (

  EmployeeId NVarchar(100),

  EmployeeName NVarchar(100),

  LoginTime DateTime

  )

  --Sample Data

  Insert Into Ex Values('A0001','Vinu','2012-11-02 14:56:11.577');

  Insert Into Ex Values('A0034','Sunil','2012-11-02 15:02:11.513');

  Insert Into Ex Values('B0001','Gaurav','2012-11-03 14:56:11.577');

  Insert Into Ex Values('C0001','Amar','2012-11-03 17:56:11.513');

  Insert Into Ex Values('D0001','Vikash','2012-11-03 21:56:11.443');

  Insert Into Ex Values('A0034','Sunil','2012-11-03 23:21:11.122');

  Insert Into Ex Values('B0012','Rahul','2012-11-04 08:30:11.577');

  Insert Into Ex Values('D0004','Rohan','2012-11-04 11:00:11.333');

  Insert Into Ex Values('C0023','Karan','2012-11-04 19:25:11.234');

  Insert Into Ex Values('A0007','Karthik','2012-11-05 14:56:11.577');

  Insert Into Ex Values('B0009','Sachin','2012-11-06 17:56:11.513');

  Insert Into Ex Values('D0011','Arif','2012-11-09 08:30:11.577');

  Insert Into Ex Values('C0023','Karan','2012-11-09 16:30:11.567');

  Insert Into Ex Values('B0009','Sachin','2012-11-10 15:02:11.513');

  Insert Into Ex Values('D0011','Arif','2012-11-10 19:23:11.765');

  Insert Into Ex Values('A0001','Vinu','2012-11-10 21:30:11.899');

  Insert Into Ex Values('C0023','Karan','2012-11-10 22:45:11.233');

  Insert Into Ex Values('A0007','Karthik','2012-11-12 12:00:11.455');

  Insert Into Ex Values('D0001','Vikash','2012-11-12 15:15:11.155');

  Insert Into Ex Values('B0012','Rahul','2012-11-13 14:56:11.577');

  Insert Into Ex Values('A0007','Karthik','2012-11-14 19:25:11.234');

  Requirement : To get the previous and next Login times for a particular(or a pattern) Login.

  My Attempt : I wrote the query to get previous and next login times for all the EmployeeIds starting with 'C'. ie: 'C0001' and 'C0023'. Following is the Query:

  --Query To Get Previous and Next Login Info

  ;With CTE

  As

  (

  Select *, ROW_NUMBER() Over(Order By LoginTime) As rn From Ex

  )

  Select a.EmployeeId As CurrentLoggerId, a.EmployeeName As CurrentLoggerName, a.LoginTime As CurrentLogin,

  b.EmployeeId As PreviousLoggerId, b.EmployeeName As PreviousLoggerName, b.LoginTime As PreviousLogin,

  c.EmployeeId As NextLoggerId, c.EmployeeName As NextLoggerName, c.LoginTime As NextLogin

  From CTE As a

  LEFT JOIN CTE As b ON b.rn = (a.rn - 1)

  LEFT JOIN CTE As c ON c.rn = (a.rn + 1)

  Where a.EmployeeId LIKE 'C%'

  Order By a.LoginTime

  I did this just as an example for learning purpose.

  The query works ok and gets the desired resultset.

  But, whats bothering me is that if there is a lot of data in the table and since I'm using two self joins with the CTE, would it cause a loss in performance??.......Is there a more efficient and performance friendly way to accomplish what I am trying???

  Looking forward to your opinions and a lot of advise on this.

  Ideas for doing it using any other query are also welcome. 🙂

  Vinu Vijayan

  For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

• David McKinney

  SSChampion

  Points: 10472

  More actions

  November 29, 2012 at 1:11 am

  #1563591

  Take a look at the following article "Linking to the previous row", and perhaps more importantly the subsequent discussion which compares CTE with alternatives for this type of problem.

  http://www.sqlservercentral.com/articles/T-SQL/62159/

  Regards,

  David McKinney.