locked
OLEDBcommand is too slow RRS feed

  • Question

  • Hello,

    I'm using an OLEDB Command in a DataFlow which performs a parametric query to update thousands of rowsets but it is very slow.

    Is there an alternative ?

    Friday, June 15, 2007 8:10 AM

Answers

  • it's not the component's fault it is slow. SQL faster at set based operations.

    You can insert all the rows into a temporary table and then using a SQL task, run the update by joining the two tables together.
    Friday, June 15, 2007 8:30 AM

All replies

  • it's not the component's fault it is slow. SQL faster at set based operations.

    You can insert all the rows into a temporary table and then using a SQL task, run the update by joining the two tables together.
    Friday, June 15, 2007 8:30 AM
  • Thanks!

    I've tested this solution, but it seems to persisted a certains slowness.

    My dataflow uses these components:

     

    OLEDB Source --> Lookup -----

     

     

    Friday, June 15, 2007 9:40 AM
  • As with anything, you have to find out where the bottleneck is. Is it the source? is it the lookup? is it the dest?

    Start simple. How quickly does the source get the records? Dump everything into Trash Destination. Then add the lookup? is the lookup taking a while to cache the rows? Are you selecting the whole table or just the keys that you need? etc etc.

    Finally, having a poor query in SQL source will result in a slow data flow. Are the tables correctly indexed in the source query. The dest? Two many indexes? Lookup? Indexes etc etc.

    Is the final SQL update correctly indexed?

    Listing two components in your data flow and saying they slow is the vaguest statement you could say.


    Many reasons, more possible solutions.
    Friday, June 15, 2007 11:23 AM