SQL Server Performance Forum – Threads Archive
Vertical or Horizontal Table StructureHi! We have Survey tables hold records for 50K to 100K participants and those tables have 50 response columns. Will we be better to have Vertical Table structure with 50 times * (50K to 100K) records to store the response or have wider record with 50K to 100K records. This is for ASP based Web Survey. Thanks
It really depends on what you plan to do with the data there is no better/worse way without knowing how the data will be manipulated after it has been inserted by the respondents Cheers
Our main concern is improving the Survey performance by table design, After survey data processing is not our primary concern, as that is going to happen in-house we can always convert data into any sturture for processing after the survey is over. I wanted to get opinion on if a Vertical Structure helps or have any advantage over the traditional horizontal structure.
Will the 50 response columns ever change? For example, on the next survey could you have 45 or 60? Will you be asking the questions all at the same time? If it’s a set number of answers, and you’ll be asking them all at the same time, I would go with one wide table. If it can change, I would go with option two. You can always present a flat recordset back to SQL Server and have it insert the records into a reporting structure later. Can you give us more details? MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
The survey is going to be running for 4 months and once into production they will never change. The Survey contains 50 questions asked one after another to all respondent. My main concern is have the table design to help the respone at the Web based survey thru ASP. Please let me know if you need any more information. Thanks
Will the asp collect up all of the answers and then send them off to the database/webserver or are they done a page at a time. If the latter then do you want to save the page’s answer to the database? I’d say that for straight out database performance on insert you can’t beat a single row with 50+ columns, as it will always be faster than 50 inserts… Cheers