February 21, 2011 at 6:20 am
Hi
I have a package with 10 data flows. All the data flows contains OLE DB Command for which are updating my FACT Stagaing table.
for initial load it is taking hugh amount of time. As it is updating 1.5 to 2 Lakh records.
I don't want to write a Direct SQL Update statment for this.
Can anyone suggest any way to increase the execution of the same.
Can we avoide the OLE DB Command.
Thanks
February 21, 2011 at 6:28 am
Send all of the rows you will be updating to a staging area (as INSERTs), then perform a single T-SQL update at the end.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 21, 2011 at 7:42 am
saurabh.deshpande (2/21/2011)
I don't want to write a Direct SQL Update statment for this.Can anyone suggest any way to increase the execution of the same.
Can we avoide the OLE DB Command.
Is there a reason you don't want to write a SQL Update statement?
As Phil said, the staging table with set-based update is the best option. You can also write an INSTEAD OF trigger on the staging table that changes the INSERT statement into an UPDATE statement. That way you can use the staging table as a destination in your dataflow and that's it. On the other hand, you'll have to maintain triggers.
(So Phil's option is still preferred)
On a sidenote: why has an initial load so many updates? What is there to update?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 21, 2011 at 7:48 am
Not sure about that trigger idea - nice thought, but won't it just end up doing the same RBAR updates, albeit on the server?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 21, 2011 at 11:07 am
Phil Parkin (2/21/2011)
Not sure about that trigger idea - nice thought, but won't it just end up doing the same RBAR updates, albeit on the server?
If you implement the INSTEAD OF trigger set-based, there is no problem. It is quite easy with the use of the INSERTED table.
I have never used this approach, I've read it in an article somewhere. Useful when you don't have a staging area (or not the space for it) and you don't want to stress out tempdb.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 21, 2011 at 1:54 pm
Koen Verbeeck (2/21/2011)
Phil Parkin (2/21/2011)
Not sure about that trigger idea - nice thought, but won't it just end up doing the same RBAR updates, albeit on the server?If you implement the INSTEAD OF trigger set-based, there is no problem. It is quite easy with the use of the INSERTED table.
I have never used this approach, I've read it in an article somewhere. Useful when you don't have a staging area (or not the space for it) and you don't want to stress out tempdb.
So the 'real' inserts go directly into the destination table and the 'update' inserts go into the staging table, where an instead of trigger picks them up from 'inserted' and fires them into the destination table via an UPDATE - is that what you mean?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 22, 2011 at 12:18 am
Phil Parkin (2/21/2011)
Koen Verbeeck (2/21/2011)
Phil Parkin (2/21/2011)
Not sure about that trigger idea - nice thought, but won't it just end up doing the same RBAR updates, albeit on the server?If you implement the INSTEAD OF trigger set-based, there is no problem. It is quite easy with the use of the INSERTED table.
I have never used this approach, I've read it in an article somewhere. Useful when you don't have a staging area (or not the space for it) and you don't want to stress out tempdb.
So the 'real' inserts go directly into the destination table and the 'update' inserts go into the staging table, where an instead of trigger picks them up from 'inserted' and fires them into the destination table via an UPDATE - is that what you mean?
Basically. I tracked down the article:
http://sqlserverpedia.com/blog/sql-server-bloggers/ssis-%E2%80%93-performing-an-upsert/
Instead of a staging table, he uses a view on top of the destination table where he puts his "update inserts".
Advantages: no need for a staging table, so less disk I/O. Bulk load and set based updates.
Disadvantages: you'll have to maintain views and triggers for every destination table.
I have never used this before, but I like the idea of using this when there is absolutely no space for staging tables.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 22, 2011 at 12:29 am
That's a good article, thanks! It's always good to have read about this stuff - one day, an ideal scenario will present itself and I'll be able to suggest a great solution.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply