CODEWITHSUNDEEP
ssis
greg
greg

Reputation: 1873

dataflow performance - update on primary key

I have a dataflow task that updates a record in a table w/ 250k records. The update is being made using the primary key. Is SSIS it is taking 3 seconds.

  1. Is there a way to extract the exact sql that gets evaluated for a record (any record, i just want a solid example)

When i mock up what i reasonably believe to be the correct sql and run it in SSMS , it takes 0.0 seconds (records are getting updated via the SSIS package, so Im pretty sure it is doing what i think it is doing)

  1. What could be taking 3 seconds. It seems to be specific to SSIS. My reasoning is that SSMS executes so fast. Its not the database.

Im fishing, Ill admit, but google isnt helping me. Anyone have any suggestions?

Thanks

Greg

Upvotes: 0

Views: 418

Answers (1)

billinkc
billinkc

Reputation: 61211

The out of the box update component, OLE DB Command component, performs row-by-agonizing-row (RBAR) updates. That usually does not provide good performance. A better pattern for updates is to stage changes to an "normal" destination (OLE DB Destination or ADO .NET Destination). After the Data Flow Task completes, then use an Execute SQL Task to perform a set-based update against those rows. Read the Stairway to Integration Services series, in particular Level 4 to see this pattern.

Beyond the update problem I mention above, a difference between the SSIS update and the one from SSMS is that SSMS is just going to run a script and exit. SSIS doesn't just "go". It has a fairly complex processing model so it needs to validate that the package is in the right state, resources are available, etc. If you're logging events (and you should be), capture OnPreExecute and OnPostExecute for your task. That will show how long SSIS spent on a particular step in the package. Do be aware that for timing purposes, running a package from within Visual Studio (BIDS/SSDT) will be slower than the command-line version as it must spin up and manage the debugging resources (and show your pretty boxes change color/receive a check mark).

Still want to know what's going on? Turn on SQL Profiler and you can capture all the SQL Server activities from the SSIS package. Just be careful of the impact this can have if you capture everything on a production server for a long period of time. Plenty of resources available explaining how to run profiler if you use your favorite search engine.

Upvotes: 3

Related Questions