question for h2 which is faster?

Question

now I have two choice. I have the same schema for all the data. the record stand for the connection between to hosts.So one record belongs to two hosts. now I do the action that once I need to get the connection of 1 host. I will insert the record in to h2. So if there is a connection between host1 and host2. Everytime I query the connections of host1, I have already store the connection between host1 and host2. So when I query the info about host2. the record will be stored twice in the table. So I am thinking about to create a table for each host.

Here is the dilemma. Since there are a lots of hosts. So if I create a table for each host,the quantity of the tables will be huge. Is to qurey the huge but only one table faster or to query lots of smaller tables faster?(no join)

Thanks

Answer 1

Indexing the one table with lots of records is the way to go. It can become a maintenance nightmare if you don't, and indexing will take care of your search speed in the table. Plus let's assume that you have a huge amount of records, in Sql Server 2008 (and in 2005) you can partition the table into separate files which will help with speed as well. Even not in sql server, keeping the data in the same table is the way to go, especially if your schema for the table is the same for each host.