What are the strategies for a really fast single table database?

Question

Hello there!

I have a database in SQL Server 2008 that supports various applications. I am now starting the development of a new application and, from that database, I require data that fits in one table (though there is some replication).

The data I way may be extracted to a view (a few inner joins) with 6 columns. I'm developing a web application that must be really fast navigating through the data (about 6 million records). Therefore, I would like to know if it is advisable to query the SQL Server view or to deploy a new, updatable, database indexed by Lucene or similar mechanism (BerkeleyDB?)

Thank in advance!

Answer 1

Are you familiar with the Boyce-Codd Normal Forms?

If you want to keep your current infrastructure, want to constrain your data to a single table, and are concerned with performance, I highly recommend you add additional columns to the table and store de-normalized data in those columns.

For example:

Say you had a table, people. Each person has a manager_id that links to another person in the same table as their manager. You could add a column for the manager called employees_count that stored a counter that tracked how many people were under that manager.

This is a simple but effective example of de-normalized data

Answer 2

views don't make accessing your data any faster, they just make writing queries easier. this means that selecting data from your view will be the equivalent of doing the few joins that comprise the view, and a join on a table involving 6 million records will take awhile.

the question is, what do you want to speed up? if you want fast selects from your complex data set, you could try to build real tables (not views) containing the data you want and update those tables from the main table at preset intervals.

if your data is going to be constantly modified and you need fast updates and for selects to always reflect the latest data, you might want to optimize your table structure. see if you can do away with the joins. this might come at the cost of duplicating your data, which means you'll need heavy use of transactions.

Answer 3

It is probably a difficult question to answer without knowledge of the exact data structure. Indexed views in SQL Server is good for this and could very well meet your performance criteria.

At this point it is advisable to run a test (Perhaps a JMeter jdbc load test) against a view. You could determine the viability of the simplest solution with relatively little effort before going down the route for creating additional indexes with Lucene or BerkeleyDB.