Sql-server – Best approach to get last value inserted

performancequery-performancesql-server-2008-r2

I have several devices that report via Edge network their position (and more data) to a UDP server which directly inserts this data in a SQL 2008 r2. I want to know which is the best approach to retrieve the last position from the db, concerning performance upon insert and lookup of data.

I thought of these ways:

1) Master table & Last_position table, I insert the data in the master which holds all the data in the package, and a trigger in this table that updates the row in last_position table of the corresponding ID of device.

2) same as 1 but no trigger, and I manually make an insert and an update from the UDP server.

3) no last_position table, only master table and an index that allows me to retrieve last inserted row with something like this:

select top 1 pos_x, pos_y from master where device_id = @id  order by date_inserted desc

Currently we have implemented the solution number 1, but I'm not a big fan of triggers, specially in tables with very high insert/seconds rate. This table holds approximately 50 million rows, which migrate periodically to a master_historic table in order to hold only a week old data (in the historic we hold 2 months).

Any other solution is welcome.

Thanks!

Best Answer

No, please don't store this data redundantly - you already have all of the information you need in the base table, without incurring the overhead of triggers or additional writes.

Just create an index that supports your query, somewhat similar to what @Colin suggested, but I'm adding the output columns to avoid key lookups:

CREATE INDEX ix_last_position 
  ON dbo.[master] -- terrible table name btw
  (device_id, date_inserted_desc)
  INCLUDE(pos_x, pos_y);

Note that this assumes the query in your question is the only (or most frequent) query pattern against this data. If you have other queries that perform the same kind of searches but have different output columns or additional filters, a different index might be better.

Related Question