Want to make ""Get Latest Status"" function more efficient

Hey folks, I'm looking at making the following query more efficient
potentially using the ranking functions and I'd like some advice from
the gurus. The purpose of the following is to have a status for a
person, and also have a historical background as to what they've done,
status wise. This was the best way I could come up with to do this a
few years back, but I'm wondering if there's a better way with Sql
2005.

Here's a toned down structure and my query. Any help/critique would
be greatly appreciated.


CREATE TABLE #Status(
StatusID int NOT NULL,
StatusName VARCHAR(50) NOT NULL,
StatusCategoryID int NOT NULL
) ON [PRIMARY]

CREATE TABLE #RegStatus(
[RegistrationID] [uniqueidentifier] NOT NULL,
[StatusID] [int] NOT NULL,
[StatusTimeStamp] [datetime] NOT NULL,
[UniqueRowID] [int] IDENTITY(1,1) NOT NULL
) ON [PRIMARY]

SET NOCOUNT on
INSERT INTO #Status VALUES(200, 'StatusA', 1)
INSERT INTO #Status VALUES(210, 'StatusB', 1)
INSERT INTO #Status VALUES(115, 'StatusC', 1)
INSERT INTO #Status VALUES(112, 'StatusD', 1)
INSERT INTO #Status VALUES(314, 'StatusE', 1)
INSERT INTO #Status VALUES(15, 'StatusF', 1)
INSERT INTO #Status VALUES(22, 'StatusG', 1)

INSERT INTO #Status VALUES(300, 'StatusX', 2)
INSERT INTO #Status VALUES(310, 'StatusY', 2)
INSERT INTO #Status VALUES(320, 'StatusZ', 2)

INSERT INTO #RegStatus VALUES('7A6058D0-06CB-4E83-A8C4-B1AFC74B11F0',
200, GETDATE())
INSERT INTO #RegStatus VALUES('7A6058D0-06CB-4E83-A8C4-B1AFC74B11F0',
210, GETDATE())
INSERT INTO #RegStatus VALUES('7A6058D0-06CB-4E83-A8C4-B1AFC74B11F0',
115, GETDATE())

INSERT INTO #RegStatus VALUES('8B94A666-A3DD-4CB1-89A0-9910047AE7A0',
112, GETDATE())
INSERT INTO #RegStatus VALUES('8B94A666-A3DD-4CB1-89A0-9910047AE7A0',
314, GETDATE())
INSERT INTO #RegStatus VALUES('8B94A666-A3DD-4CB1-89A0-9910047AE7A0',
200, GETDATE())
INSERT INTO #RegStatus VALUES('8B94A666-A3DD-4CB1-89A0-9910047AE7A0',
22, GETDATE())

INSERT INTO #RegStatus VALUES('58AF0FC6-C900-4BD0-B3F7-F9D62701F021',
15, GETDATE())
INSERT INTO #RegStatus VALUES('58AF0FC6-C900-4BD0-B3F7-F9D62701F021',
115, GETDATE())
INSERT INTO #RegStatus VALUES('58AF0FC6-C900-4BD0-B3F7-F9D62701F021',
200, GETDATE())
INSERT INTO #RegStatus VALUES('58AF0FC6-C900-4BD0-B3F7-F9D62701F021',
115, GETDATE())
SET NOCOUNT Off

/*
This is a query from within a function that I use to not only get the
latest
status for one registrant, but I can use it to get the latest status
for everyone as well.
*/


DECLARE @RegStatusCatID int,
@RegID UNIQUEIDENTIFIER

SET @RegStatusCatID = 1
SET @RegID = null

select LS.*, S.StatusName, S.StatusCategoryID
from #Status S
join(
select RS.RegistrationID, RS.StatusID, RS.StatusTimeStamp
from #RegStatus RS
join
(
SELECT RS.RegistrationID , max(RS.UniqueRowID) UniqueRowID
FROM #RegStatus RS
join #Status S
on RS.StatusID = S.StatusID
and S.StatusCategoryID = @RegStatusCatID
and (
@RegID is null
or (@RegID is not null
and RS.RegistrationID = @RegID)
)
group by RS.RegistrationID
)LS
on RS.UniqueRowID = LS.UniqueRowID
) LS
on S.StatusID = LS.StatusID

--SELECT * FROM #RegStatus

DROP TABLE #RegStatus
DROP TABLE #Status

Re: Want to make ""Get Latest Status"" function more efficient

On May 14, 10:02 am, Mark wrote:
> Hey folks, I'm looking at making the following query more efficient
> potentially using the ranking functions and I'd like some advice from
> the gurus. The purpose of the following is to have a status for a
> person, and also have a historical background as to what they've done,
> status wise. This was the best way I could come up with to do this a
> few years back, but I'm wondering if there's a better way with Sql
> 2005.
>
> Here's a toned down structure and my query. Any help/critique would
> be greatly appreciated.
>
> CREATE TABLE #Status(
> StatusID int NOT NULL,
> StatusName VARCHAR(50) NOT NULL,
> StatusCategoryID int NOT NULL
> ) ON [PRIMARY]
>
> CREATE TABLE #RegStatus(
> [RegistrationID] [uniqueidentifier] NOT NULL,
> [StatusID] [int] NOT NULL,
> [StatusTimeStamp] [datetime] NOT NULL,
> [UniqueRowID] [int] IDENTITY(1,1) NOT NULL
> ) ON [PRIMARY]
>
> SET NOCOUNT on
> INSERT INTO #Status VALUES(200, 'StatusA', 1)
> INSERT INTO #Status VALUES(210, 'StatusB', 1)
> INSERT INTO #Status VALUES(115, 'StatusC', 1)
> INSERT INTO #Status VALUES(112, 'StatusD', 1)
> INSERT INTO #Status VALUES(314, 'StatusE', 1)
> INSERT INTO #Status VALUES(15, 'StatusF', 1)
> INSERT INTO #Status VALUES(22, 'StatusG', 1)
>
> INSERT INTO #Status VALUES(300, 'StatusX', 2)
> INSERT INTO #Status VALUES(310, 'StatusY', 2)
> INSERT INTO #Status VALUES(320, 'StatusZ', 2)
>
> INSERT INTO #RegStatus VALUES('7A6058D0-06CB-4E83-A8C4-B1AFC74B11F0',
> 200, GETDATE())
> INSERT INTO #RegStatus VALUES('7A6058D0-06CB-4E83-A8C4-B1AFC74B11F0',
> 210, GETDATE())
> INSERT INTO #RegStatus VALUES('7A6058D0-06CB-4E83-A8C4-B1AFC74B11F0',
> 115, GETDATE())
>
> INSERT INTO #RegStatus VALUES('8B94A666-A3DD-4CB1-89A0-9910047AE7A0',
> 112, GETDATE())
> INSERT INTO #RegStatus VALUES('8B94A666-A3DD-4CB1-89A0-9910047AE7A0',
> 314, GETDATE())
> INSERT INTO #RegStatus VALUES('8B94A666-A3DD-4CB1-89A0-9910047AE7A0',
> 200, GETDATE())
> INSERT INTO #RegStatus VALUES('8B94A666-A3DD-4CB1-89A0-9910047AE7A0',
> 22, GETDATE())
>
> INSERT INTO #RegStatus VALUES('58AF0FC6-C900-4BD0-B3F7-F9D62701F021',
> 15, GETDATE())
> INSERT INTO #RegStatus VALUES('58AF0FC6-C900-4BD0-B3F7-F9D62701F021',
> 115, GETDATE())
> INSERT INTO #RegStatus VALUES('58AF0FC6-C900-4BD0-B3F7-F9D62701F021',
> 200, GETDATE())
> INSERT INTO #RegStatus VALUES('58AF0FC6-C900-4BD0-B3F7-F9D62701F021',
> 115, GETDATE())
> SET NOCOUNT Off
>
> /*
> This is a query from within a function that I use to not only get the
> latest
> status for one registrant, but I can use it to get the latest status
> for everyone as well.
> */
>
> DECLARE @RegStatusCatID int,
> @RegID UNIQUEIDENTIFIER
>
> SET @RegStatusCatID = 1
> SET @RegID = null
>
> select LS.*, S.StatusName, S.StatusCategoryID
> from #Status S
> join(
> select RS.RegistrationID, RS.StatusID, RS.StatusTimeStamp
> from #RegStatus RS
> join
> (
> SELECT RS.RegistrationID , max(RS.UniqueRowID) UniqueRowID
> FROM #RegStatus RS
> join #Status S
> on RS.StatusID = S.StatusID
> and S.StatusCategoryID = @RegStatusCatID
> and (
> @RegID is null
> or (@RegID is not null
> and RS.RegistrationID = @RegID)
> )
> group by RS.RegistrationID
> )LS
> on RS.UniqueRowID = LS.UniqueRowID
> ) LS
> on S.StatusID = LS.StatusID
>
> --SELECT * FROM #RegStatus
>
> DROP TABLE #RegStatus
> DROP TABLE #Status

Read in BOL about
ROW_NUMBER() OVER(PARTITION BY RegistrationID ORDER BY StatusTimeStamp
DESC) = 1

Re: Want to make ""Get Latest Status"" function more efficient

As Alex suggested, you can use the ROW_NUMBER function in SQL Server 2005.
Here is one solution that does that:

WITH Registrations AS
(
SELECT RegistrationID,
StatusID,
StatusTimeStamp,
ROW_NUMBER() OVER(
PARTITION BY RegistrationID
ORDER BY StatusTimeStamp DESC,
StatusID) AS rn
FROM #RegStatus
WHERE RegistrationID = COALESCE(@RegID, RegistrationID)
)
SELECT R.RegistrationID,
S.StatusID,
R.StatusTimeStamp,
S.StatusName,
S.StatusCategoryID
FROM #Status AS S
INNER JOIN Registrations AS R
ON S.StatusID = R.StatusID
WHERE StatusCategoryID = @RegStatusCatID
AND rn = 1;

A few notes:

- If you decide to use this approach, the ROW_NUMBER function can benefit
from index created on the partitioning column and the sort columns. In this
case would be (RegistrationID, StatusTimeStamp, StatusID).
- The way you created your example, it generates duplicate StatusTimeStamp
dates for a registrant. For that reason I added StatusID in the ORDER BY
clause of ROW_NUMBER to return deterministic result. However, the result may
or may not match your original solution. That is because you had the
IDENTITY column which was an indicator of the latest inserted row. Here I
used the StatusTimeStamp as an indicator for the latest status. If you
expect to have duplicate dates and the StatusID is not good for a
tiebreaker, then I guess using the IDENTITY would be the only solution.
- I assumed the goal is to avoid using the IDENTITY column. If not, then
using ROW_NUMBER with ORDER BY UniqueRowID DESC will provide deterministic
result.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Re: Want to make ""Get Latest Status"" function more efficient

Alex and Plamen, thanks so much for the help.

I'm using a UDF to return this data, so I've opted out of using the
Common Table Expression. The Row_Number helped tons. Over the test
data (not much) I've improved efficiency quite a bit, according to the
execution plans. I'm going to run this on live data soon, to see how
it works. I've changed my query to be as follows;

SELECT *
FROM #Status S
JOIN
(
SELECT RegistrationID,
StatusID,
StatusTimeStamp,
[UniqueRowID],
ROW_NUMBER()
OVER(
PARTITION BY RegistrationID
ORDER BY [UniqueRowID] DESC
) AS rn
FROM #RegStatus RS
where (
@RegID is null
or (@RegID is not null
and RS.RegistrationID = @RegID)
)
) RS
ON S.StatusID = RS.StatusID
AND RS.RN = 1
AND S.StatusCategoryID = @RegStatusCatID

Thanks Again!

Mark

Re: Want to make ""Get Latest Status"" function more efficient

Everything you are doing is wrong. Please read any book on data
modeling and the ISO-11179 naming rules. There is no such attribute
as a magical, universal "status" in a valid data model; it has to be
some kind of status -- "shipping_status", "martial_status",
"employment_status", etc.

Monsters postfixes like "_status_cat_id" are really silly! What is
it? A status code, a category? or an identifier? By definition it
cannot be all of those things at once. Hey why not go all out and
have a "_status_cat_id_value_name_time" postfix!

You then load tables with UNIQUEIDENTIFIER and IDENTITY(1,1); but at
least you know that these are for non-relational physical locators,
like sequentially numbering rows to get a fake magnetic tape and not
even pretend that you are doing RDBMS design.

>>. The purpose of the following is to have a status for a person, and also have a historical background as to what they've done, status wise. <<

The usual design error is to have only one time in a row to capture
when an event started, then do horrible self-joins to get the duration
of the status change. Let me use a history table for price changes.
The fact to store is that a price had a duration:

CREATE TABLE PriceHistory
(upc CHAR(13) NOT NULL
REFERENCES Inventory(upc),
start_date DATE NOT NULL,
end_date DATE, -- null means current
CHECK(start_date < end_date),
PRIMARY KEY (upc, start_date),
item_price DECIMAL (12,4) NOT NULL
CHECK (item_price > 0.0000),
etc.);

You actually needs more checks to assure that the start date is at
00:00 and the end dates is at 23:59:59.999 Hrs. You then use a
BETWEEN predicate to get the appropriate price.

SELECT ..
FROM PriceHistory AS H, Orders AS O
WHERE O.sales_date BETWEEN H.start_date
AND COALESCE (end_date, CURRENT_TIMESTAMP);

It is also a good idea to have a VIEW with the current data:

CREATE VIEW CurrentPrices (..)
AS
SELECT ..
FROM PriceHistory
WHERE end_date IS NULL;

There is a common kludge to repair a failure to design a history table
properly that you can put in a VIEW if you are not able to set things
right. Assume that every day we take a short inventory and put it in
a journal. The journal is a clip board paper form that has one line
per item per day, perhaps with gaps in the data. We want to get this
into the proper format, namely periods shown with a (start_date,
end_date) pair for durations where each item had the same quantity on
hand. This is due to Alejandro Mesa

CREATE TABLE InventoryJournal
(journal_date DATETIME NOT NULL,
item_id CHAR(2) NOT NULL,
onhand_qty INTEGER NOT NULL);

WITH ItemGroups
AS
(SELECT journal_date, item_id, onhand_qty,
ROW_NUMBER() OVER(ORDER BY item_id, journal_date, onhand_qty)
- ROW_NUMBER() OVER(PARTITION BY item_id, onhand_qty
ORDER BY journal_date) AS item_grp_nbr
FROM Journal),
QtyByDateRanges
AS
(SELECT MIN(journal_date) AS start_date,
MAX(journal_date) AS end_date,
item_id, onhand_qty
FROM ItemGroups
GROUP BY item_id, onhand_qty, item_grp_nbr)

SELECT start_date, end_date, item_id, onhand_qty
FROM QtyByDateRanges;

This might be easier to see with some data and intermediate steps

INSERT INTO InventoryJournal VALUES('2007-01-01', 'AA', 100);
INSERT INTO InventoryJournal VALUES('2007-01-01', 'BB', 200);
INSERT INTO InventoryJournal VALUES('2007-01-02', 'AA', 100);
INSERT INTO InventoryJournal VALUES('2007-01-02', 'BB', 200);
INSERT INTO InventoryJournal VALUES('2007-01-03', 'AA', 100);
INSERT INTO InventoryJournal VALUES('2007-01-03', 'BB', 300);

start_date end_date item_id onhand_qty
==========================================
'2007-01-01' '2007-01-03' 'AA' 100
'2007-01-01' '2007-01-02' 'BB' 200
'2007-01-03' '2007-01-03' 'BB' 300

Now, download the Rick Snodgrass book on Temporal Queries in SQL from
the University of Arizona website (it is free). And finally Google up
my article at www.DBAzine.com on transition constraints.