Results 1 to 11 of 11
  1. #1
    Join Date
    May 2006
    Posts
    9

    Unanswered: Need help with redesign of code

    Hi.

    I need some help with this, all suggestions greatly appriciated!

    I have a table with three columns.
    Lets call it scores, it has these columns.
    (user id PK, object id PK, score)

    This holds score values for a user on a given object.

    The score for the given object and user is a complicated calculation, and is now done with a single insert into statement.

    Kind of like:
    Code:
    Insert into scores (user id, object id, score)
    select @user id,
             sometable.object id,
             ((sum(calcvalue * (case when someattribute = '-' then this when someattribute ='+' then that end))) / anotherattribute * 10) + somevalue
    from table
    join another table
    on something
    join even more tables
    on something else
    where user id = @user id
    group by columns
    This code is overly simplyfied but I hope you get the picture. The codes does all the calculation and inserts the correct values in the score table.

    Due to some complicated searches we have to do within the score table we had to redesign it to preform better.
    The table is now basicly a flat structure with only one row pr user and all the object ids as columns. We have 100 objects.

    New table scores:
    (user id PK, object 1, object 2 ...... object 100)

    Do anyone have any suggestion to how i can convert my code snip over to adjust to this new table structur?

    I know i can do something like:
    update scores set object 1 = ( select object 1 score the same way as above with all complicated joins etc..), set object 2 = (etc...)
    where user id = @user id

    But this i am sure will go very very slow...
    Anyone have a better idea?

  2. #2
    Join Date
    Jan 2005
    Location
    Green Bay
    Posts
    201
    select user_id, object_id as object1, null as object2 etc., score
    where object_id = 'something'
    union
    select user_id, null as object1, object_id as object2 etc., score
    where object_id = 'somethingelse'
    ETC

    You could pull the data out and and do something in a pivot table in ugh EXCEL then make a flat file out of you data from the pivot and import into new structure.
    Never tried this but I think it could be done.

    I have not found any other way to transpose data in SQL Server.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by daheri
    Due to some complicated searches we have to do within the score table we had to redesign it to preform better.
    Maybe THAT is when you should have asked us for help, because THIS:
    Quote Originally Posted by daheri
    The table is now basicly a flat structure with only one row pr user and all the object ids as columns. We have 100 objects.
    is almost certainly a bad solution.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    May 2006
    Posts
    9
    Quote Originally Posted by blindman
    Quote Originally Posted by daheri
    Due to some complicated searches we have to do within the score table we had to redesign it to preform better.
    Maybe THAT is when you should have asked us for help, because THIS:
    Quote Originally Posted by daheri
    The table is now basicly a flat structure with only one row pr user and all the object ids as columns. We have 100 objects.
    is almost certainly a bad solution.
    Thanks for the response guys! After some long days at work i finally figured out some ways of doing it fast and effective.

    Blindman: I understand why you think a flat structure with 100 columns is a bad solution, but in this case its not. The calculation and insertions of scores in this table will preform slower with this design, and its less adapteble to changes in number of objects, BUT the really heavy part will preform MUCH better.
    The heavy part includes that one user has to be compared to all other users and the top 100 is returned. The comparing bit includes that each column is calculated against a selection of columns from the other user and a total comparisasion score is generated. This final score is what we select the top 100 from.

    I have done preformance tests of both designs and there is really no question at all that the flat structure is faster, but I understand your point of view because that was what i first designed too !

  5. #5
    Join Date
    Jul 2003
    Posts
    123
    Wouldn't be possible to have a "Score" column that is updated everytime any of the object columns are updated?

    (user id PK, object 1, object 2 ...... object 100, total_score)

    A trigger on each object column perhaps?

    Maybe this can speed up your top 100 comparison?

    But a design that is not flat would be better I think, more scaleable..

  6. #6
    Join Date
    May 2006
    Posts
    9
    Quote Originally Posted by oneleg_theone
    Wouldn't be possible to have a "Score" column that is updated everytime any of the object columns are updated?

    (user id PK, object 1, object 2 ...... object 100, total_score)

    A trigger on each object column perhaps?

    Maybe this can speed up your top 100 comparison?

    But a design that is not flat would be better I think, more scaleable..
    That will not work because the score is a value that is calculated between two users. Its not a score which is unique for one user.
    So in order to make your suggestion work each user would need one score column for every other user. And thats a lot of score columns. Not to mention that you would have to add new score columns every time a new user is added.

    I belive this is a good example of times where denormalization of data is needed for optimalization.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by daheri
    So in order to make your suggestion work each user would need one score column for every other user. And thats a lot of score columns.
    In SQL we call those joins.

    -PatP

  8. #8
    Join Date
    May 2006
    Posts
    9
    Quote Originally Posted by Pat Phelan
    In SQL we call those joins.

    -PatP
    I dont get what youre trying to say. Lets say one user(A) wants to do a search. Then user A will have to be compared to all the other users resulting in a comparison score for each user that user A is compared to.
    Given a database with 100k + users that will be 100k+ comparison_scores and we'd like top 100 of those.

    I find it hard to see how it is a good solution to do joins returning one extra column for each user. That would give me a dataset with extremly limited order ability.
    The good solution here is not to join users in new columns, but to create a view joining users on new rows with each row returning a comparison score. That way you can easily order by the comparison score and select top 100.

    Anyway, i dont think i can really explain the complexity of the calculations done and the reason why the table ended up flat, without giving you a really really detailed description. But the main point is that even though the normalized way of designing the table is to make one row for each object for each user, it is by far not the fastest when i comes to calculations.

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    So, what solution did you come up with?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    May 2006
    Posts
    9
    I assume you mean solution for my original question.

    Instead of inserting the calculated objectvalues into scores they are now inserted into a table variable.
    Then there is a code snippet kinda like this.
    Code:
    update scores	set object1 = (select score from @fscore where id = 1),
    		object2 = (select score from @fscore where id = 2),
    		..
    		..
    		object100= (select score from @fscore where id = 100)
    This has an acceptable execution time, but i'm sure it can be optimized further.
    I can create an view instead of using the table variable, but im not sure if it will be any faster, so i may be just wasting my time doing it.
    What do you think?

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    If your table variable holds a small subset of your production data (which is apparently very large), then you are probably better off using the table variable rather than repeatedly requerying the production data.

    Is your table variable set to use id as the primary key? If not, you may want to consider using a temporary table instead, which will allow you more flexibility in indexing this sample dataset for repeated querying.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •