SQL Server Performance

Vertical or Horizontal Table Structure

Discussion in 'T-SQL Performance Tuning for Developers' started by spj, Oct 22, 2004.

  1. spj New Member


    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.

  2. Twan New Member

    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

  3. spj New Member

    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.
  4. derrickleggett New Member

    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?


    When life gives you a lemon, fire the DBA.
  5. spj New Member

    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.

  6. Twan New Member

    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...


Share This Page