SQL Server Performance Forum – Threads Archive

Table Performance

I want to build a table in SQL to store real-time variable changes. The table has only 3 columns: A Date/Time (Table Index), a variable ID and the Value. If this table can reach 10 million records, which option gives me the best performance in a SELECT (searching for a specific time interval and variable ID): A – A table with only 3 columns, with 10,000,000 records.
B – A table with 6 columns, like: Date/Time1, ID1, Value1, Date/Time2, ID2, Value2, with 5,000,000 records. If I choose option B, I would perform a JOIN into the columns to obtain only 3 columns again (Date/Time, ID and Value). Thank you!

My best guess, without trying it, is the option A. An index seek on a column will be faster than two index seeks and a JOIN in a query. This of course assumes that you have appropriate indexes. I would create a clustered index based on the values you intend to use most commonly in the WHERE clause. ——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com

I would say option A, definitely with a clustered index based upon your query. Unless you are planning to distribute the table across multiple servers. (Distributed Partitioned Views). -CR

Imagine now a third scenario with the table format below: C – A table with 60 columns: ID1, DateTime1, Value1, ID2, DateTime2, Value2, ID3, DateTime3, Value3, …, ID20, DateTime20, Value20. This table would have 10,000,000 / 20 = 500,000 records. The whole question is: SELECT performance X DataBase Size. What is the best: To have lots of rows with few columns or few rows with more columns? WhatÂ´s the impact of the 3 scenarios at the DataBase size? I think itÂ´s a general but rather interesting question for most SQL users. MS.

I think its worth remembering that although table size obviously plays a factor in a queries performance, due to the way indices work a 100% increase in table size would not necessarily take a query twice as long to complete. Even with many thousands of records, depending on what criteria you are using to access the data, it can still be fairly snappy. I would always prefer a table to be larger vertically (rows), than horizontally (cols), because if you store data in multiple columns then sure, the number of rows will be proportionally less, but this is often outweighed by the increase in complexity of the query required to get them back into a usable format (in this case boiling the table back into a result set containing three columns). Also it is often difficult to design a good set of covering indices in such cases. Of course, without good indices, neither scenario will perform very well.

I agree with Chappy. It is better to have more rows and fewer columns. The actual database size should not vary much because of its design, assuming each variation is properly normalized. ——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com

]]>