Question Local db for thousands of items

[NickM]

Hello,

I'm making an OPC Client in VB 2008 express edition. In this application i need to make a database with groups of items. So its a fairly simple db, but each group can contain thousands of items. Each item has a standard 5 colums with values. Each group should be easily (fast) created or deleted.

My question:
How can i save these groups with the items in a database while the most important thing is speed?
And what kind of database is the best to use for this situation?

Im thinking of a local SQL server with a table for every group. Or a dataset, but that will possible be slower with each item i add.

I hope someone can explain me the best solution!

 

[cjard]

Im thinking of a local SQL server

Good idea

with a table for every group

Not necessary, or sensible. A table of a million items in 1000 distinct groups will perform just as well as, if not better than, a thousand tables of a thousand items each.. Provided that the group id column is indexed. Queryies that can be satisfied out of the index will be, such as SELECT COUNT(DISTINCT group_id) FROM table

This will allow you to build proper parameterized queries rather than having to specify the table name as part of a string concatenation -> this will reduce performance because one query plan cannot be created and the optimiser/compiler has a lot more work to do