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.