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?
Best Regards
--
Michael Gould, Managing Partner
Intermodal Software Solutions, LLC
904.226.0978
904.592.5250 fax
