3/19/2012 5:56:12 PM
Bhagwan Singh Jatav -
The ideal table design for efficiency is to have one long integer ID column in every table acting as the primary key. This violates one of the design principles of normalisation, but the speed gain is so huge compared to the purely theoretical disadvantage that it’s virtually a no-brainer decision to make.
Less ideal is having a primary key consisting of multiple ID columns. It will still be fairly efficient, but not as good as the single column.
Do not use anything other than ID columns in the primary key. Using real data in the primary key implies that it may be changed by the user. This will violate any foreign key constraints placed on the key and generally cause havoc. Keys work best when the values never change once they are created. If you want to ensure that a particular data column is unique, just define a separate unique constraint on it.
Always define an index to match every foreign key definition on the table. This will virtually guarantee efficient joins in all queries.
If a primary key or unique constraint consists of multiple columns, and you find the need to search on only one of them, then define a separate index on it.
Do not be afraid of defining lots of indexes. More indexes do indeed slow down the speed of inserting into the table, but our databases are mostly used for querying and reporting rather than lots of high-speed transactions, so this is not too much of an issue. On the other hand, do not define indexes on columns that are not searched on – this simply wastes processing time needlessly.
A primary key must always be defined on every table. In the vast majority of cases, this should also be the clustered index. You will see a large performance gain from having a clustered primary key. Note that this is different advice to previous DBMSs.
At the moment in Sirius code there are a wide variety of methods used to extract and process temporary data for running reports. Most of these methods either do not support multiple users or have a large performance impact, so here are two standard methods of building up temporary data which overcome these problems. Everyone should use only these two methods from now on, regardless of the database version you are working on.
This method is suitable for generating, processing, and using data within one stored procedure. If you require a set of temporary data that can be persisted across multiple procedures, see the next section.
This method is suitable for persisting data across multiple procedures, but can also be used inside one procedure if you like.
Bhagwan Singh Jatav (Member since: 10/31/2011 4:24:43 AM)
View Bhagwan Singh Jatav 's profile
Leave a comment