Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
 Posted - 2002-02-08 : 19:16:45
|
| I have an "Instant Message System" on a website I am developing. Currently the table ContainsMessageFrom varchar(15)MessageTo varchar(15)message varchar(200)Date smalldatetimeWhen a user is logged in it checks for new messages and returns the new ones. I did not use ID columns in the messageFrom and MessageTo. At the time I thought this was "strategic de-normalization". The reason I did it like this is because I could do the whole "message_check" function with 1 simple query. With the ID I would have to join it to the "tblUserDetails" table to get the "nameOnline" of the user. This table has 20,000 records and will soon grow to over 100,000.Any thoughts on this practice? Performance gain or performance hit?Thanks for the input ..Mike |
|
|
RobWafle
Starting Member
38 Posts |
Posted - 2002-02-09 : 00:32:38
|
| Mike,I'm addicted to using indexes.If you're joining two tables, but the where clause specifies items in your index. e.g. userid (which is probably the primary key )or .. messageid (which is likely a good primary for the messages table)SQL server can use the indexes to avoid using linear table searches (checking ALL of the 10,000 or 1,000,000) rows to learn the name of the user. A book I have says if you use a binary tree you only have to search 20 nodes instead of 1,000,000 when using a binary tree. It sounds like a lot less I/O to me. 6 orders of magnitude less! Have you ever played "guess a number between 1 and 100"? Did you know you can always get the correct answer in 7 or 8 tries? (binary search) I've had a beer can't think too straight..If you take a query and run it in the "Query Analyzer" with the "Show Execution Plan" option turned on (in the query menu), you don't want to see "table scans" being used. You'd rather see "clustered index seek" .. really helps with the performance.. (unless the table is very small) say lookup tables less than 100 rows... (there are ways to calc that .. its not really < 100 rows.. )I've worked with SQL server and tables up to 1.8 millon rows "large". I've not yet strategically de-normalized anything in my carreer, or had any performance issues with any applications I've written. My current appliation will have 4.8 million rows of data in a single table when I am done populating my database. I'm thinking about de-normalizing a single column. and using OLAP to summarize some data. Horizontal table partitioning may be used too.. but thats all smack.. Almost all my techniques use integer primary keys where a better one does not exist. I over-utilize indexing to prevent duplicates in my database, and "cover" queries.Indexes are your friend. However, you can OVER index causing inserts and updates to perform slowly.I hope this helps. |
 |
|
|
|
|
|
|
|
database design issue