Partition Or Separate Table

I am designing database for fleet management system.

I will be getting n number of records every 3 seconds. Obviously, there will be millions of record in my table where I am going to store current Information of vehicle in the current_location table. Here performance is an BIG issue.

To solve this, I received the following suggestions:

1. Create a separate table for 开发者_StackOverflow社区each vehicle. Here a table will be created at a run time as as soon as I click on create new table.And all the data related to particular table will be inserted and retrieve from that particular table.

2. Go for partition.

Please answer the following questions about these solutions.

1. What is difference between the two?

2. Which is best and why?

3. At what point will the number of rows in the tables cause performance issues?

4. Are there any other solutions?

Now ---if I go for range partition in sql server 2008 what should i do to,

1. partition using varchar(20).

2. i am planning to do partition based on vehicle no. eg MH30 q 1234. Here In vehicle no. lets say mh30 q 1234--only 30 & q going to change....so my question is HOW SHOULD I GO. means how should write the partition function.

***1st this question was asked for my sql..now for sql server ********sorry guys now I shifted from my sql to sql server*****With The same question

definitely use partitioning. why go to all of the hassle to figure out which table to use to answer a question when mysql will do it for you? and good luck find the current location of all of your trucks if you're not using partitioning!

1. partitioning gives you the performance benefits of multiple tables, but with automatic pruning (selection of just the tables needed to answer the query).

2. nothing is ever "best". the question is: what is best for your problem?

3. this is impossible to answer. you will just have to monitor your system for performance issues and adjust server settings or scale as necessary.

4. at least as far as mysql is concerned, none as good as partitioning!

Don't bother with partitioning for 28,800 rows per day.

We don't (yet) with over 5 million per day. (The "yet" means we have no business input on what data retention policy they want)

There should be very little performance difference between making a separate table for each vehicle, and making the vehicle ID the first field in the primary key. You get the same grouping on disk either way, and mysql should have no trouble with millions of rows in a table.

Partitions are only useful if you have multiple disks on your machine and want to spread the load across disks.

So I guess my answer is do neither. Designing this in a priori seems overkill.

One thing I want to point out is that having one table (which you can partition later when you need to) will be much easier to maintain both in the database and in terms of querying the data.