Post reply

Matrix multiplication without cursors?

  • May 1, 2009 at 2:19 pm

    #133832

    I could use some design direction here:

    Original design of my db for this client:

    One person has 126 numeric scores from a quiz.

    Each score has a group code and a trait code.

    CREATE TABLE [dbo].[AllScores](

    [ID] [int] IDENTITY(1,1) NOT NULL,

    [RespondentNumber] [int] NOT NULL,

    [AllScoresCollection] [int] NOT NULL,

    [GroupCode] [varchar](3) NOT NULL,

    [Trait] [varchar](2) NOT NULL,

    [Normalized_Score] [float] NULL

    Each analysis group has a varying number of scores to match up to the respondent's scores (e.g. join on groupcode and trait):

    CREATE TABLE [dbo].[KTScores](

    [ID] [int] IDENTITY(1,1) NOT NULL,

    [Key_Trait_Number] [int] NOT NULL,

    [GroupCode] [varchar](3) NOT NULL,

    [Trait] [varchar](2) NOT NULL,

    [TableCode] [smallint] NOT NULL,

    [HighPerfScore] [smallint] NULL,

    [AvgPerfScore] [smallint] NULL,

    [RN] [smallint] NULL,

    [RF] [smallint] NULL

    TableCode is 1, 2 or 3

    Where Table Code is 1, we want the average of all matching scores

    Where TableCode is 2, we want the average of all matching scores rounded up or down to the nearest 5 and have a constant subtracted.

    Where TableCode is 3, we want the average of all matching scores rounded up or down to the nearest 5 and have a different constant subtracted.

    In each case for 1,2 and 3, if RF is 1 the answer is to be subtracted from RN.

    The desired result for the respondent is the sum of the three numbers.

    I've had this working for years.. it was designed for one person to be analysed against a few traits. Then they asked for several people to be analysed against several traits.

    I built it using temporary cursors and RBAR analysis. Not the fastest way.

    Now they want large numbers of people (500 or so) to be analysed against hundreds of traits (500x126 scores against up to 33 scores each).

    This is essentailly a huge matrix multiplication problem. I think.

    Where do I start, to design my rewrite? Is this posted in the right place? (I am using SQL 2000, 2005 and plan to use 2008).

  • May 1, 2009 at 2:22 pm

    #987578

    Please don't double post. You already started a thread here.

    Let's keep everything in one place.

    Edit: I copied the above to the other thread since it appears you added a bit to the description.

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

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