Foreign Keys in Fact Table - performance help

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ray - 02 Mar 2006 23:16 GMT

We are still fairly new to data-warehousing. We have a fact table that has a
fk to the dimension table... and the lookup translation is too SLOW...

We are currently using t-sql and stored procedures for our ETL work. We open
a cursor to read the source (stage table) and do basic insert operations
into the datamart fact table. That means for each source row, we have to
translate the source ID to a destination ID.

For example:
Fact Product Sales has fk to Dimension Product.
source product Id = 99. datamart destination product Id = 1099.

We have a lookup tables that manages the mapping of source & destination for
our dimension tables. We call a stored procedure to retrieve the destination
key from the lookup table during the Fact Table ETL. The problem, of course,
is that this stored procedure is called once per FK column and per record.

Can someone please recommend options to make this faster? Does SSIS have
in-memory data structures to support this? Or are there other tricks I can
employ such as indexes, turning off FKs, using 'NOLOCK' and 'ROWCOUNT' off,
reducing the number of columns retrieved in the cursor, etc. --- I've done
all of these, but it still takes approximately 2500 rows/minute.

Thank you!

Ray

Reply to this Message

Jéjé - 03 Mar 2006 02:32 GMT

to "convert" the source ID to a dimension surrogate key whe you load your
fact table, simply use a view.

select Dim1.Key1, Dim2.Key2, ..., Fact.Sum1...
from Fact
inner join DWDatabase.dbo.Dim1 Dim1
on Dim1.ID = Fact.DimID1
...

use the bulk insert task to use the fast load options.
you can load thousands of rows / sec.
in my case I reach 100 000rows/sec on a small server

doing a row by row loading is the slower option.

SSIS can do a lookup in memory, so there is an advantage:
* the staging database and the DW database can be on 2 different servers
(while a view cause some restriction or performance issues)
* you can identify missing codes during the loading process more easely (the
view will load only matching keys in dimensions using an inner join clause)

> We are still fairly new to data-warehousing. We have a fact table that has
> a fk to the dimension table... and the lookup translation is too SLOW...
[quoted text clipped - 23 lines]
>
> Ray

Reply to this Message

JT - 03 Mar 2006 21:46 GMT

Using a cursor to perform lookups / inserts one row at a time will be very
slow. Instead use a single insert into query that joins to the related
lookup tables.

insert into myfacttable
select
..
from
sales
join ..
join ..
join ..

Insure that all primary and foreign keys are indexed:
http://www.microsoft.com/technet/prodtechnol/sql/70/books/c0618260.mspx
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag/html/Scal
eNetHowTo03.asp

Also, use the Show Execution Plan feature of Query Analyzer to investigate
the retreival method used by SQL Server to execute the query and look for
clues on how to improve it's performance.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/optimsql/odp_tu
n_1_5pde.asp

> We are still fairly new to data-warehousing. We have a fact table that has
> a fk to the dimension table... and the lookup translation is too SLOW...
[quoted text clipped - 23 lines]
>
> Ray

Reply to this Message

bill.robinette@gmail.com - 08 Mar 2006 20:04 GMT

> We are still fairly new to data-warehousing. We have a fact table that has a
> fk to the dimension table... and the lookup translation is too SLOW...
[quoted text clipped - 22 lines]
>
> Ray

Cursor in DSS environment = VERY bad idea. Use SQL and joins.

Reply to this Message