There 2 major types of data usually stored in the database.
1) Small tables; static data; usually configuration parameters or list of values; rarely updated.
2) Large tables; Historical records; inserted/ updated often.
For the second t
ype of tables there is a simple and effective method of improving the search performance by 100-200%. This is achieved by creating Descending clustered index (primary key) on the auto increment (identity) column.
This way the last record physically placed as a first record on the hard drive. Any linking mechanism is employing a search engine which always positioning at the first record first. The most searches are going against most recent data. This way the linkage between tables in queries is improved.
The second benefit comes when you need to select the most recent inserted record. This is usually the case to confirm that record is inserted.
The query can be as simple as: “SELECT TOP 1 * FROM [table_name]” and performance can not be better.
Here is the sample select from a table with the descending clustered index (primary key) :
Decsending primary key sample
There are several topics discussing the advantage of using the descending clustered index:
http://www.mssqltips.com/tip.asp?tip=1337
http://msdn.microsoft.com/en-us/library/ms181154.aspx
The very common technique to have 2 similar tables for the transactional data: One table is for the recent transactions (1 day / month/year) and another table for the older data. That table structure supported constant performance of the query execution.
Using Descending primary key that construction becomes less obvious because the latest records searched first keeping the query performance as table grows.
Rich Text AreaToolbarBold (Ctrl / Alt + Shift + B)Italic (Ctrl / Alt + Shift + I)Strikethrough (Alt + Shift + D)Unordered list (Alt + Shift + U)Ordered list (Alt + Shift + O)Blockquote (Alt + Shift + Q)Align Left (Alt + Shift + L)Align Center (Alt + Shift + C)Align Right (Alt + Shift + R)Insert/edit link (Alt + Shift + A)Unlink (Alt + Shift + S)Insert More Tag (Alt + Shift + T)Toggle spellchecker (Alt + Shift + N)▼
Toggle fullscreen mode (Alt + Shift + G)Show/Hide Kitchen Sink (Alt + Shift + Z)
FormatFormat▼
UnderlineAlign Full (Alt + Shift + J)Select text color▼
Paste as Plain TextPaste from WordRemove formattingInsert custom characterOutdentIndentUndo (Ctrl + Z)Redo (Ctrl + Y)Help (Alt + Shift + H)
There 2 major types of data usually stored in the database.
1) Small tables; static data; usually configuration parameters or list of values; rarely updated.
2) Large tables; Historical records; inserted/ updated often.
For the second type of tables there is a simple and effective method of improving the search performance by 100-200%. This is achieved by creating Descending clustered index (primary key) on the auto increment (identity) column.
This way the last record physically placed as a first record on the hard drive. Any linking mechanism is employing a search engine which always positioning at the first record first. The most searches are going against most recent data. This way the linkage between tables in queries is improved.
The second benefit comes when you need to select the most recent inserted record. This is usually the case to confirm that record is inserted.
The query can be as simple as: “SELECT TOP 1 * FROM [table_name]” and performance can not be better.
Here is the sample select from a table with the descending clustered index (primary key) :
Decsending primary key sampleThere are several topics discussing the advantage of using the descending clustered index:
http://www.mssqltips.com/tip.asp?tip=1337
http://msdn.microsoft.com/en-us/library/ms181154.aspx
The very common technique to have 2 similar tables for the transactional data: One table is for the recent transactions (1 day / month/year) and another table for the older data. That table structure supported constant performance of the query execution.
Using Descending primary key that construction becomes less obvious because the latest records searched first keeping the query performance as table grows.
cell phone spywarepirkt viagra
Path: