June 1, 2009 at 2:42 pm
We are about to partition TWO major tables that are almost always joined together in queries. One table consists of properties and the other table consists of sales of those properties same properties. An analysis was done to define the partitions for the properties table based on state/county (FIPS) codes. This will distribute the load as evenly as possible between the partitions.
The current thinking is that the sales table should be partitioned identically to the properties table, simply because that way one particular partition for properties will always be matched with an identical partition for sales. (The columns used for partitioning exist in both tables.)
However, it is possible that the distribution of sales will not be identical to the distribution of properties, which means that the partioning for sales would not be as easily balanced. So...
Are there any performance advantages to having two often-joined tables using identical partioning, or are we better off balancing the load for each table separately?
Thanks.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
June 1, 2009 at 3:04 pm
I suspect that you're best action would be to keep the tables partioned the same way, so as to avoid extra overhead in querying even more partitions.
What you might do is try three versions:
Optimum partition for properties, same partition for sales
Optimum partition for sales, same partition for properties
Optimum for both
Load test all three and see which works best under a heavy load.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 1, 2009 at 3:08 pm
Thanks, Gus, that makes sense to me, even though I was hoping to avoid it.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
June 1, 2009 at 3:20 pm
Well, the thing you most hope to avoid is the one that's most likely to bite you in the ...
🙂
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 1, 2009 at 4:32 pm
I wanted to avoid this because of the sheer size of the tables. Frankly, we don't have enough disk space to play with... but what else is new.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
June 1, 2009 at 8:34 pm
Seems to me that unless you balance the partitions across separate spindles, the only benefit for performance more than what a proper clustered index with proper maintenance would be is the ease in maintaining such a clustered index.
--Jeff Moden
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply