Solved

Slowly Changing Dimension (Type 3) T-SQL Example

Posted on 2004-08-02
9
1,134 Views
Last Modified: 2012-06-22
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.
0
Comment
Question by:acwng
  • 2
  • 2
  • 2
  • +1
9 Comments
 
LVL 15

Expert Comment

by:jdlambert1
ID: 11700176
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>


0
 
LVL 34

Expert Comment

by:arbert
ID: 11700420
Post what you're doing--it sounds like something that could be accomodated with a set operation or possibly a join...
0
 
LVL 1

Author Comment

by:acwng
ID: 11700499
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.
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 9

Expert Comment

by:xenon_je
ID: 11700618
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
0
 
LVL 9

Accepted Solution

by:
xenon_je earned 120 total points
ID: 11700625
PS:
the 2 statements
select * into temp_ToInsert from Current_table join Dimension_table on.. where ...
select * into temp_ToUpdate from Current_table join Dimension_table on.. where ...


replace them with:

select * into temp_ToInsert from Current_table where --a condition like field1 not in (select fildX from Dimension_table)
select * into temp_ToUpdate from Current_table  where --a condition like field1 not in (select fildX from Dimension_table)

xenon
0
 
LVL 34

Expert Comment

by:arbert
ID: 11700757
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...
0
 
LVL 1

Author Comment

by:acwng
ID: 11711861
Thanks all for the support, I'll try out the performance and will let you know the result soon.
0

Featured Post

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question