1

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!

  • 1
    Number 3 works well for me. Make sure you have an index on (device_id, date_inserted desc). – Colin 't Hart Dec 17 '14 at 13:34
1

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.

|improve this answer|||||
  • I have mainly 2 querys, one that retrieves last position, and other that retrieves data passing an ID as parameter (we have an autoincremental ID). The query is something like this: select fields FROM master m with (nolock) left join device d with (nolock) on (d.device_id = m.device_id) WHERE m.id > @masterid and device_id= @device_id order by masterid Also I have a query that retrieves the ID from master based on date. In total I have 5 indexes actually, including PK index, where only 1 I think is not crucial. If i'm not clear enough tell me and I edit my main post with more info. – Vallo Dec 17 '14 at 14:18
  • also master is not the actual table name haha – Vallo Dec 17 '14 at 14:18
  • sorry the query was not right. this is the correct one: select fields FROM master m with (nolock) left join device d with (nolock) on (m.device_id = d.device_id ) WHERE m.ID> @ID order by masterid – Vallo Dec 17 '14 at 15:16
  • @Vallo I believe I've answered your question : what is the best way to get the last value inserted, and that is to use the data in the table. For performance optimization you may need different supporting indexes depending on the query. If you want to ask about optimal indexes for a specific query, please ask a new question. – Aaron Bertrand Dec 17 '14 at 15:49
  • yeah I forgot to mark as answered, thanks! – Vallo Dec 17 '14 at 15:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.