Sql – 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 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

Best Solution

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!