Thread: An idea of performance tuning
12-11-05, 08:47 #1Registered User
- Join Date
- Sep 2002
- Hong Kong
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
12-11-05, 14:07 #2Registered User
Provided Answers: 1
- Join Date
- Aug 2003
- Where the Surf Meets the Turf @Del Mar, CA
>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.