Databases
StackOverflow2013
Postsdbo
POSSIS Data Flow performance slow vs select into
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSSIS Data Flow performance slow vs select into
    primarykey
    data
    text
    <p>so I have a script:</p> <pre><code>select * into my_archive_05302013 from archive_A where my_Date = '05/18/2013' </code></pre> <p>and:</p> <pre><code>insert into archive_B (ID,my_date,field_n ) select ID, my_Date,field_n from my_archive_05302013 </code></pre> <p>where the n in field_n is about 100 or so. so in other words there are more than 100 columns in the table that I am loading. </p> <p>which run pretty fast the query inserts about 200000 records. my_date is a non-clustered index in table archive_A</p> <p>Now when I create a dataflow using SSIS 2008 it takes <strong>HOURS</strong> to complete</p> <p>I have the following in my OLE DB source:</p> <pre><code>SELECT * FROM Archive_A WHERE My_Date = (SELECT MAX(My_Date) from Archive_A) </code></pre> <p>and for OLE DB Destination: </p> <p>Data access mode of: "Table or view - fast load" Name of the table: archive_B Table lock and Check constraints is checked</p> <p>Any one know what the problem could be?</p> <p>Thanks in advance</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USuser2129585
    1. USuser2129585
    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 Data Flow performance slow vs select into
      1. This table or related slice is empty.
      1. VTUpMod
    1. COFirst off, you're not doing the same thing. Your first query is going to a specific date in your archive table and writing that into another table. You then pull 3 columns from that much smaller table into the final destination. Contrast that with your SSIS approach wherein you're grabbing every field on your archive table, which if it's more than those 3 columns, you're going to have to read a lot more data than is required. You'll also have to compute the maximum date from the archive table vs a fixed point in time. Finally, the first does not have to factor in network latency, second might.
      singulars
      1. POSSIS Data Flow performance slow vs select into
      1. USbillinkc
    2. COHmm, Data Flow should beat SELECT INTO? You could use an Execute SQL Task to obtain the MAX(My_date) and store it in a variable, and then use that variable in the OLE DB Source in the Data Flow. (Incidentally, your title should read "SSIS Data Flow..." instead of "SSIS Task Flow...".) That just separates out the subquery. The other thing would be to check the metadata on the arrow from the Source to the Destination. Do all the 100 columns have the correct data types? You can also add a [Data Viewer](http://msdn.microsoft.com/en-us/library/ms140318.aspx) and watch the data as it goes by.
      singulars
      1. POSSIS Data Flow performance slow vs select into
      1. UScriticalfix
    3. COI thought you were transferring all 100+ columns in both cases, and you were just showing an abbreviated version of the script. If you only need a subset of the columns, then you definitely need to tell SSIS about that before it goes and moves gigabytes ...
      singulars
      1. POSSIS Data Flow performance slow vs select into
      1. UScriticalfix
 

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