SQL Server Performance

Can seem to figure a way to optimize this...

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by Lohkay, Nov 13, 2007.

  1. Lohkay New Member

    Hi, I posted this in the general forum before noticing this one which is more appropriate so here goes: We have surveys that gather data and we're trying to find the best way to query this data for reporting. It is not too late to change everything so we're open to any suggestions. Here is my current setup:
    Results table from which each row represents a user that filled in the survey so it contains a unique identifier, a survey identifier and some other data which we use elsewhere.
    Result_Answers table which holds the answers for each result. So it contains the result ID, the question ID and the answer ID
    Questions and Answers table which hold the actual text for each identifier used in result_answers.
    What we're trying to do is retrieve the count for each answers (thats easy) based on a set of conditional answers. For exemple:
    Q1 - Are you male or female
    Q2 - What is your age group
    Q3 - What type of music do you listen to.
    So lets say I want the count of each answers for each question but limited to the results that answered male on Q1 and 18-25 OR 25-35 on Q2. The only way I found to do this is create a cursor to loop trough each questions, select the result IDs that answered Q1 correctly and then remove from that temp table each result ID that didn't answer the subsequent questions correctly. This gives me a page long query with a cursor and multiple IN commands (for when there's more than one answer valid for a single question)
    I'm not saying the results are abysmal, I'm still getting my results inside 2s with a bit over 1 million rows in those tables. But unfortunatly, this is basically only one survey... I can only imagine that it will get longer and longer as we add more surveys.
    So my question is: Is there a better way to query this data or worst case, is my table schema not efficient enough? In both cases, how can I fix/optimize it?
    I'm really hoping you guys can help me out!

Share This Page