Link to home
Start Free TrialLog in
Avatar of acwng
acwng

asked on

Slowly Changing Dimension (Type 3) T-SQL Example

Is there a best way to write a T-SQL (SQL Server 2000) so that I can compare the current records with the old records so to perform slowly changing dimension Type3. I am now using a cursor in T-SQL, however it takes ages to fetch exach records and compare the old records. Other suggestion are welcome, e.g. DTS ... etc.
Avatar of jdlambert1
jdlambert1
Flag of United States of America image

I've had cursors speed up by orders of magnitude just by changing READ_ONLY to FAST_FORWARD:

DECLARE Cur1 CURSOR FAST_FORWARD FOR
 <select_statement, , SELECT au_fname FROM pubs.dbo.authors>


Avatar of arbert
arbert

Post what you're doing--it sounds like something that could be accomodated with a set operation or possibly a join...
Avatar of acwng

ASKER

Let me post the actual situation:

Current_table - all the current records imported from Source Database

Dimension_table - a history of all records from the Current_table

I declare a cursor to fetch the records in Current_table 1 by 1 and then compare the value of each of the field related to the Dimension_table, if found any record changes between the 2 tables, I will do insert or update in Dimension_table.
In general cursors are slow, so try to avoid if possible.
In your case to me it sounds as if those statements can be replaced by 2 SQL-statements, an update and an insert

Eg:

update Dimension_table
set ....
from Dimension_table join Current_table on ..
where ... (your condition for which records to update)


select ....into  Dimension_table
from Current_table join Dimension_Table
where ...

Also to not mix up the updated with inserted you could create 2 temp tables in which to put exactly those records from curent table that need to be updated and inserted
EG:
select * into temp_ToInsert from Current_table join Dimension_table on.. where ...
select * into temp_ToUpdate from Current_table join Dimension_table on.. where ...

--now make the update and insert using the Dimension_table and the 2 temp tables created above...

I hope this will give you some ideas...Because the problem was described prety vaguely, the answer could not be more specific...

good luck,
 xenon
ASKER CERTIFIED SOLUTION
Avatar of xenon_je
xenon_je

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Agree with xenon_je and actually, on the update portion, you might find that it's even faster to do a delete and reinsert the entire record--just depends on your data...
Avatar of acwng

ASKER

Thanks all for the support, I'll try out the performance and will let you know the result soon.