February 13, 2012 at 6:38 am
In my dataflow i have one OLEDB source and destination connection, i am executing a sp with 2 parameters(fromdate & todate) and inserting the result set to one table using OLEDB destination, it is taking 2 hours to complete.
but when i am using query instead of sp in the OLEDB source it has executed in 2 min.
Why..? what i need to change if it has to execute through SP.?
February 13, 2012 at 6:41 am
That's odd.
Are you sure that nothing was blocking the sp at the database level when you executed it?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 14, 2012 at 11:14 am
Is the sp also a hog when you execute from within SQL console?
February 14, 2012 at 2:46 pm
Have you tried recompiling the execution plan? Compare both the SP / straight query plans and see if there different.
February 15, 2012 at 3:36 pm
Can you check the the Data Flow Destination setting? Is it 'Table or View - Fast load' or 'Table or View'.
My experience has been that if you select the second option then rows will be loaded row by row using a cursor and will be painfully slow. Let's call this slow load.
However the problem with the Fast Load option is that on error the whole batch will fail.
For our overnight processing that loads a data warehouse, we use a combination of both Fast load and Slow load.
Any errors from the Fast load, using the error pipeline, are fed back to the Destination using Slow load row by row . Errors from the slow load are loaded into error tables for Data quality reporting.
From experiment a batch size of 5,000 to 10,000 works well. We have used this method for about 6 years and it has worked perfectly for us.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply