As I am a software developer by profession so many times I get some interesting work which I really love to explore and I try to solve it in a different manner. In my current project I am using SQL Server 2008 as database and in past I have even used Sybase and Oracle. I love working in Oracle because it provides very powerful Analytics function which is lacking in SQL Server 2008 e.g. LAG and LEAD but now the same are available in SQL Server 2012. Anyways in my current project I have to write a query which can be done in many ways but I tried 2 ways and from that I felt that Analytics query was good in performance and actually it may really be very useful.

So here goes the problem, I have a tenant table which stores the current tenant of the property with its particular date. And tenant can change in any month since there can be replacement of the new tenant or the older tenant just left. So table is as below

PropertyId Tenant Tapedate
1 A 3/1/2013
1 B 3/1/2013
1 C 2/1/2013
1 D 3/1/2013
1 E 2/1/2013
2 F 1/1/2013
2 G 12/1/2012


Now I need to fetch the latest Tenant for the particular PropertyId. From the table structure its seems very simple but in the actual problem its quite complicated because I need to fetch from three tables for the details and there are millions record in it so performance is very important while writing query for me.

So in the beginning I wrote the below query which is very simple and easy.

   1:  ;WITH CTE AS
   2:  (
   3:      SELECT * FROM tenant WHERE propertyid = @propid
   4:  )
   6:  SELECT * FROM CTE WHERE tapedate = ( SELECT MAX(tapedate) from CTE )

Above simple query satisfies my requirement and returns my record as per my requirement. But since my table is very huge and has many joins query was really making slow and so I have to do some query enhancements. Below is the query I wrote with the help of SQL Server Analytic Functions which is quite powerful and it really increased my Query performance

   1:  ;WITH CTE AS
   2:  (
   3:      SELECT propertyid,tenant,tapedate,
   4:      RANK() OVER (order by tapedate desc) as tapedaterank
   5:  )
   7:  SELECT * FROM CTE where tapedaterank = 1


Above query returns the same accurate record and performance is awesome because of Analytics functions. Here What I do is I give rank to records with tapedate descending so the above table for propertyid one will be as below


PropertyId Tenant Tapedate tapedaterank
1 A 3/1/2013 1
1 B 3/1/2013 1
1 D 3/1/2013 1
1 C 2/1/2013 4
1 E 2/1/2013 5


After that I select only record which has rank 1 so it automatically returns the records of maximum tapedate. Instead of RANK() you even use DENSE_RANK() and it will return the same record. Its really simple and it can really improves the performance. I just love using Analytic Functions in my query.

Hopefully you will like my approach, please do share and comment if you have any other ideas for the same problem.


Related Posts: