Databases
StackOverflow2013
Postsdbo
POSSIS DataFlow slows down considerably over time
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSSIS DataFlow slows down considerably over time
    primarykey
    data
    text
    <p>I've got an ADO NET Source component which queries a table for all of it's 700,000 records.</p> <p>This is connected to a SQL Server Destination component.</p> <p>it takes 23 seconds (+ - 1 second) to process the first 100,000 records.</p> <p>The next 100,000 records take an additional 50 seconds. And it only gets worse.</p> <p>Below are the specifics and details</p> <hr> <p>I've already tried the following three different commands on the destination database.</p> <pre><code>ALTER DATABASE AdamDB SET RECOVERY SIMPLE; ALTER DATABASE AdamDB SET RECOVERY BULK_LOGGED ; ALTER DATABASE AdamDB SET RECOVERY FULL ; </code></pre> <p>In all 3 cases, the time remained at 23 seconds for the first 100,000 records.</p> <hr> <p>I've also tried putting in a Conditional Split component based on the ID value</p> <pre><code>Default SKU % 4 &lt; 1 SKU % 4 &lt; 2 SKU % 4 &lt; 3 </code></pre> <p>Each output condition has a different SQL Server Destination, and I've even gone so far as to create a different Connection Manager for each destination.</p> <p><img src="https://i.stack.imgur.com/DczOf.png" alt="Conditional Split"></p> <hr> <p>None of this seems to have had any noticeable affect on performance. </p> <p><strong>Here is the Source Connection Manager</strong></p> <p><img src="https://i.stack.imgur.com/hrpyk.png" alt="Source Connection Manager"></p> <p><strong>Here are my source object properties.</strong></p> <p><img src="https://i.stack.imgur.com/6bGU4.png" alt="Source Object Properties"></p> <p><strong>And here is the connection manager for the destination</strong></p> <p><img src="https://i.stack.imgur.com/BjQC4.png" alt="Destination Connection Manager"></p> <p><strong>and here are my destination object properties.</strong></p> <p><img src="https://i.stack.imgur.com/Hc8iz.png" alt="Destination Connection Properties"></p> <hr> <hr> <p>How can I speed this up?</p> <p><strong>edit</strong></p> <p>From Lamak's suggestion, I've tried using an OLE DB Destination instead of a SQL Server Destination. This seems to have the same speed result and slowdown over time.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USadam
    1. USadam
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSSIS DataFlow slows down considerably over time
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POSSIS DataFlow slows down considerably over time
      1. This table or related slice is empty.
      1. VTUpMod
    1. CODid you try using the "Ole DB Destination" instead?. You can choose "Table or view - fast load" on the "Data access mode" to speed the loading.
      singulars
      1. POSSIS DataFlow slows down considerably over time
      1. USLamak
    2. CO@Lamak Have not, I'll work on that right now to see.
      singulars
      1. POSSIS DataFlow slows down considerably over time
      1. USadam
    3. COLet me know how it goes. And you need to take into account that "Fast load" takes some locks on the table in order to insert rows faster
      singulars
      1. POSSIS DataFlow slows down considerably over time
      1. USLamak
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload