March 18, 2009 at 7:17 am
I am using TableDifference custom component.
Before we actually update dimension table in our data warehouse we assign ACT_CD:
I - Insert
C - Close
U - Update
D - Delete
So I run the package. TableDifference works fine
but after that in my data flow "Insert" and "Delete" streams
are hanging (yellow never turns green).
I check [mst.CUSTOMER_DIM] which is being updated
and apparently it is being updated but extremely slowly.
4000 records in 8 min.
Looks like OLE DB Command is slow.
I was wondering if TableDifference is slowing down the flow
or something else?
[mst.CUSOMER_DIM] has 700,000 records.
CREATE TABLE [mst].[CUSTOMER_DIM](
[CUST_KEY] [int] NOT NULL,
[SIC_KEY] [int] NULL,
[LANG_KEY] [int] NULL,
[IG_KEY] [int] NULL,
[IG_SRC_SYS_KEY] [int] NULL,
[CUST_ID] [int] NOT NULL,
[CUST_NAME] [varchar](100) NOT NULL,
[DMCL_TRANSIT_KEY] [int] NULL,
[DMCL_TRANSIT_NUM] [char](5) NOT NULL,
[BRANCH_MGR_CD] [char](3) NULL,
[OPEN_DT] [datetime] NULL,
[CLOSE_DT] [datetime] NULL,
[ESTABLISH_DT] [datetime] NULL,
[ANNUAL_SALE_AMT] [money] NULL,
[EMP_CNT] [int] NULL,
[MKTG_DESC] [varchar](100) NULL,
[CBI_ID] [varchar](20) NULL,
[TRANS_EFF_DT] [datetime] NOT NULL,
[TRANS_END_DT] [datetime] NOT NULL,
[ACTIVE_ROW_FL] [bit] NOT NULL,
[ACT_CD] [char](1) NULL,
[UPDATE_DT] [datetime] NOT NULL,
CONSTRAINT [XPKCUSTOMER_DIM] PRIMARY KEY CLUSTERED
(
[CUST_KEY] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Our staging tables are located under [stg] schema.
What we update is located in [mst] schema.
The actual DW is [dbo].
March 19, 2009 at 9:39 am
I followed the suggestion from:
http://www.sqlbi.eu/Forum/tabid/72/forumid/12/threadid/7/scope/posts/language/en-US/Default.aspx
and it helped.
I removed OLE DB Command. Instead I inserted the results into
Global Temp Table ##CUSTOMER_DIM_ACT_CD in my data flow
and in Control Flow I just executed SQL to update
mst.CUSTOMER_DIM.ACT_CD from ##CUSTOMER_DIM_ACT_CD
Imagine with OLE DB Command it took 8 min to update 4,800 records
Now it takes 10 sec !
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply