Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2002
    Location
    Hong Kong
    Posts
    13

    Unanswered: An idea of performance tuning

    We are now maintaining organizations and users through an intermedium table call user_orgs, which organizations table name is [code_orgstructure] and users table name is [users]. And user_orgs contain both userid and orgid to join code_orgstructure and users

    Just an idea taken from this link: http://www.onlamp.com/lpt/a/5007, not sure if it practical to implement

    As now we already store the leaf_orgid in [users] table:

    | userid | ... | leaf_orgid |

    May be we can add column [ prime_number ] and [ prime_sum ] to code_orgstructure table,

    | orgid | ... | prime_number | prime_total |

    Which, prime_number is an unique prime number id for specific organization; and prime_total is the multiple of all prime_number for specific organization and all the ansectors. Afterward, which generate some report, instead of have query like:

    select XXX from YYY where user_orgs.orgid = ? and user_orgs.userid = users.userid and users.leaf_orgid = code_orgstructure.orgid (assign selected organization's orgid as parameter)

    we can have

    select XXX from YYY where code_orgstructure.prime_sum % ? == 0 and user_orgs.userid = users.userid and users.leaf_orgid = code_orgstructure.orgid ( assigne selected organization's prime_number as parameter, and then check if the remainder is zero or not )

    The former solution make use of index but need to join one table more and the new solution cannot make use of index but join less tables. I am not sure if it work faster or not, would anyonoe comment about this?
    Visit my homepage at http://www.carfield.com.hk

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >would anyonoe comment about this?
    When all else fails, benchmark the alternatives.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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