Link to home
Avatar of AlexPonnath
AlexPonnathFlag for United States of America

asked on

Most efficient SQL Insert / update

Hi, wondering what the most effective way would be to do the folowing

i have 2 tables, one is current status 2md one is history of a device. I always want to keep the current info in one table and the
history in a second table, that way the current info table is smaller and faster and large querys on the history dont bug it down.
My question now is what is the easyest way to do this since i ultimatly have to either insert or update the current table and then
also insert into history table.

Is it better to go and query the current table and if no match insert, if match update  or just delete the record and always insert a new one ?

As for the history table depending on the above solution i could either query the current one and insert it into history or go and
just always insert into history as well as current table.

Just as a note there will be between 50000 and 100000 in the current table and there will be up to a 1 Mio updates per hr so thats why i am looking
for performance.

Thanks

Alex  
ASKER CERTIFIED SOLUTION
Avatar of Dale Burrell
Dale Burrell
Flag of New Zealand image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Please clarify a couple of things:

Do you intend to capture all changes or just a snapshot at periodic intervals?

How current does the history need to be?

Does the history contain change versions of the same entity over time or just the last version of each entity (especially after it gets "deleted")
Avatar of AlexPonnath

ASKER

As for as DB, for now in the Beta Stage we will use MS Sql Server 2008 but we are thinking going MYSQL down the road because of cost and some of the modules avail for mySQL.

Somehow i am not folowing with the single table and pointing to parent record and the children..
it is gps data which means there is a record in the current table which has the deviceid and the Lat and Long, in the history i want to store all historical values which
are send every 5 min on the position of the device so i can tell where it was the last hr's etc

 
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
that sample would store all data in a singe table which i dont think is good for performance. Specialy if i need to delete
records from history table everynight it would impact the query performance. keep in mind the history could have 10 million or more records

As I said with the correct design e.g.indexes etc I don't think the number of records will be an issue... it will depend on your database of choice, but enterprise database's e.g. SQL Server are designed to handle that quantity of data and more.

And also as I said, its trivial to use the trigger to insert into another table if thats what you prefer.
my question was more down the line what will be more efficient

doing a select row and if count > 0 do an update if not do an insert or do a delete record and a insert ..

What is more effective and faster ?
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Avatar of Anthony Perkins
>>What is more effective and faster ?<<
Perhaps Merge is more efficient than both of those options, unfortuantely as Dale has pointed out, only you are going to be able to determine the best approach to take.
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
In direct answer to your direct question: Of course update is faster than delete+insert, as it's only one operationa dn only updating a record, in your case just the lat/long fields.

And since I assume you seldom will have the need of an insert and could do that in a seperate section of your application ading devices, if you don't want to add that, you could simply do as dgmg suggest, first update, then check @rowcount and if that's 0, then insert. You will only have that slow case in case of a new device once in it's whole lifetime.

Bye, Olaf.
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
I have no knowledge in MySQL. but in SQL2005 and above you can try partition tables. history and current data all will be in the same table but logically data is divided based on formula we define like date month etc.,

check the following articles
http://msdn.microsoft.com/en-us/library/ms345146(SQL.90).aspx
and http://msdn.microsoft.com/en-us/library/aa964122(SQL.90).aspx
we implemented the sliding window partitioning in one of our projects the partition was based on date. total partitions 60(current data + previous 59 days data)
Define Instead .. triggers for update & insert for the Status table
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.