Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1289

Re: table design for massive GPS data

Show all headers | View raw

migurus (migurus@yahoo.com) writes:
> First idea is to keep data normalized in one table, one record per GPS
> reading. It looks very simple to implement, but I doubt the server will
> be happy with this high number of small records. 

Why not? As long it is properly indexed, I don't see any problem.

What you should consider though, is to partition the table (which 
requires Enterprise Edition). When five years has passed, and you 
want to purge old data, this is a swift affair with partitioning, 
provided that your partition aligns with what you want to purge.

> I we were to do this, what would be a good candidate for primary key here?

(VEHICLE_ID, GPS_TIME). Although, you should probably have GPS_TIME as the 
first column in the clustered index to avoid fragmentation. GPS_TIME would 
also be your partitioning column.


> So, the table would look:
> create table idea2 (
>      VEHICLE_ID     int     not null
> ,     RUN_DATE     date     not null
> ,     GPS_READINGS     varbinary or whatever appropriate to keep daily 
> portion of readings

You may save some space with this, because you save row overhead. But you 
get a much more complex solution which is more difficult to understand and
maintain. And if the neeeds changes in the future and people want to run
queries like "which vehicles have been in that area at some time?", that 
is hopeless with the above. I strongly recommend against it.


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

table design for massive GPS data migurus <migurus@yahoo.com> - 2012-09-13 14:47 -0700
  Re: table design for massive GPS data bradbury9 <ray.bradbury9@gmail.com> - 2012-09-14 00:06 -0700
  Re: table design for massive GPS data "Bob Barrows" <reb01501@NOSPAMyahoo.com> - 2012-09-14 07:33 -0400
    Re: table design for massive GPS data migurus <migurus@yahoo.com> - 2012-09-14 10:13 -0700
  Re: table design for massive GPS data rja.carnegie@gmail.com - 2012-09-14 05:05 -0700
    Re: table design for massive GPS data migurus <migurus@yahoo.com> - 2012-09-14 10:25 -0700
  Re: table design for massive GPS data Erland Sommarskog <esquel@sommarskog.se> - 2012-09-15 19:21 +0200
    Re: table design for massive GPS data rja.carnegie@gmail.com - 2012-09-17 08:46 -0700

csiph-web