Partitioning a table
-
I have a table
CREATE TABLE [dbo].[gltrans] (
[trans_no] [int] NULL ,
[trans_type] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[coa_code] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[glowner] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ccy] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[entity] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[deal_no] [int] NULL ,
[dealtype] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[cflow_no] [smallint] NULL ,
[amount] [money] NULL ,
[base_amt] [money] NULL ,
[base_bal] [money] NULL ,
[bal] [money] NULL ,
[matched] [money] NULL ,
[exch_fluc] [money] NULL ,
[exch_type] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[exch_group] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[input_dt] [datetime] NULL ,
[trans_dt] [datetime] NULL ,
[rates_dt] [datetime] NULL ,
[a_reversed] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[reversed] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[base] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[base_ccy] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[base_rate] [float] NULL ,
[deal_leg] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[contingent] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[intercomp] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[type] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[comments] [varchar] (78) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[int_faceno] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[base_set] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[glset_no] [int] NULL ,
[rv_margin] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[stamp] [smallint] NULL
) ON [gltrans2]
NOT MY DESIGN -- 3rd party desing that is not allowed to be changed. In it are some 57 million rows, and with its clustered index is around 30GB in size. What I would like to know, is if there is a way to script out starting from
MIN [trans_dt] I can select all the data for that month, insert it into a table the name of which is dynamically created based on the Month, and then so on for each month and year...
any ideas ??
Just to give you an idea of what I have to work with.
SELECT *
FROM gltrans
WHERE trans_dt >= '2001-12-01' and trans_dt <= '2001-12-31' returns 63156 rows just for this month.
Hope this helps...
Ford Fairlane
Rock and Roll Detective
-
One thing to keep in mind when you start thinking about partitioning is the type of queries that are consistently hitting the table. Do most queries stay within a specific time period (your partioned time buckets that make up a table) or do they typically span months? If you span months often, you'll be rejoining your tables for the solution. I'd test either way. I've done partitioning with check constraints on the partition driver and have had great results.
-
Partitioned views will solve your problem ( assuming you get the partitions right ). Note that SQL2005 supports transparent table partitions. 57 million rows / 30 gb isn't too big - increasing spindles on the data array/drive will increase performance
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
If you are going to use the local partitioned view, the partition column must be part of PK. Based on the schema you gave, all your columns in the table are NULLable. It seems there is no PK defined.
-
Like I said this is a 3rd party design and yes there are no PK's defined in all 720 tables
and to make any changes like that I need there ok or the warranty and support of the app becomes null (as all columns LOL) and void !!! Hope this helps...
Ford Fairlane
Rock and Roll Detective -
No PK's defined in any of the 720 tables? That's unfathomable. I would start taking hostages. I don't envy your spot.
You must be having performance issues, no? I don't know the whole story, but I say void the warranty if you have to. At 57 MM rows, we're beyond rinky dink MSDE. That design is inexcusable. Demand satisfaction.
What's the name of the software you're using so I know to stay miles away?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply