Re: Partition tables

Michael Gould wrote:
> I am considering whether or not to partition a few tables in our system.
> The guide lines I read said that this could help when the size of a table
> exceeds the amount of system memory. I'm wondering if this statement should
> be table or database.
>
> The way our system operates is that each individual location is actually a
> separate operation owned by another party. In most cases the data from one
> location should not be seen by others. There are several tables which are
> global in nature which hold mainly lookup information, but the actual
> processing tables are by location.
>
> I am wondering if partitioning would be a good way to isolate the
> information for each location. Each database would be created by location
> number. Out db servers is a dual quad Intel with 4 Gigs of RAM running
> Windows 2000 Enterprise Server. All Client machines are running Quad core
> servers with 8-16 gig of RAM partitioned using Windows 2003 and accessed by
> Citrix.
>
> The total size of our database with 5 years worth of data is about 3.4 gig.
> In the business we are in, we open about 5-7 new locations each year and
> close 2-3. I was also thinking that if each partition was by location it
> would be easier to disconnect the partion tables to use for historial use
> when we close a location. We probably would get 10 or so queries on the
> closed locations 6 months after closing.
>
> Does this seem like an appropriate use of table partitioning?
>
Hi - partitioning by definition should be used to get your data faster
and, in most of the cases, should be transparent to your client.
Questions I'd suggest to ask first:
1. do you have performance issues reading from your db?
2. can you come up with a pattern you can use to segment your data into
smaller chunks (like weeks, months, ids) to create partitions reasonably
big but not huge
3. how do you populate your db? inserts? copy? if ever you create
partitions, will you write to many or just one (the most recent one)

Yes, it can be useful archiving old 'locations'. In that case I'd
suggest to create dedicated datafile and put it on (slower=cheaper)
disks and move your older partitions there... but again, it's not the
main reason why you could consider partitioning.

> Best Regards
>
>
> --
> Michael Gould, Managing Partner
> Intermodal Software Solutions, LLC
> 904.226.0978
> 904.592.5250 fax
>
>
>
>
Regards,
foo