Post reply

Can't seem to find a way to optimize this...

  • November 14, 2007 at 9:09 am

    #180189

    Hi, 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!

  • November 14, 2007 at 11:29 am

    #751203

    It would help if you posted the DDL for the table so we could see what you are working with along with what you have actually done so far as well.

    😎

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply