In a previous post I showed how you can use the $PARTITION system function to force your query to only look at one or more partitions and today I have a semi-practical example of how to use this.
We have a very big table that holds all our financial transactions along with an integer indicating what type of transaction it is. I wanted to find out when the last time we recorded a certain transaction type as I think it might have been removed quite some time ago.
This could obviously been done as follows:
SELECT MAX(T.MyDate) FROM dbo.MyTable T (NOLOCK) WHERE T.TransTypeId = X
and depending on how your table is structured and indexes this may be the best way to do it.
Our table is partitioned by month on our date column so I can use $PARTITION to search each partition, starting with the most recent, and search for the last date TransTypeID = X was used. When I find a value we can stop. This can be done as follows:
DECLARE @Counter INT = 200 -- this should be the maximum partition ID on our table DECLARE @MaxDate DATETIME2(0); WHILE @Counter > 0 BEGIN RAISERROR('%d', 0,1,@Counter) WITH NOWAIT; SELECT @MaxDate = MAX(T.MyDate) FROM dbo.MyTable T WHERE T.TransTypeId = X AND $partition.PF_MyTable(MyDate) = @Counter IF @MaxDate IS NOT NULL BEGIN SELECT @MaxDate, @Counter BREAK; END SET @Counter -= 1; END;
If TransTypeID X has occurred recently then this code won’t need to loop through many partitions but if it last occurred in partition 1 then this is probably going to be much slower. It all depends on how your table is set up but I think it’s always nice to have multiple ways to potentially speed up a query.