Performance of dataflow too slow

I was transfering more that 100,000 records from flat file to sql table

It took about 1 hour.Is this the way it is?i used oledb command.

As the data passes by i got to insert to several table.Like i insert some of incoming data to one table then get the key from that table and insert rest of the data with the key field from previous table to another table.

In this case i felt OLedb would be best as we can use query.

I cannot use oledb destination as it has only error output(to insert some of incoming data and i want to have a look up to get the key but oledb des has only error output)

i cannot use sql destination as the database is sql server 2000.It dosent let me.

How can i increase the performance?Please let me know

So if I understand correctly, you are using an OLE DB Command to insert your new records into Table A, then a Lookup in non-cached mode to get the key you just inserted, so you can load the rows (with the key) into Table B with an OLE DB Destination. So you're doing 100K inserts, 100K selects, and then a fast load of 100K rows. I'm not surprised that's slow.

I think you would get much better performance to break this up into two data flows. The first data flow reads the file and populates Table A using an OLE DB Destination in fast load mode. The second Data Flow reads the file again, uses a full-cache lookup from Table A to get your new keys, and then fast loads into Table B.

If you want to get fancy (I like to get fancy), you can read the file only once, assign the Table A key in script, fast load both tables at the same time, and not have to do any lookups.
|||

That's sound advice from Jay. To re-iterate something he said, do what you can to eliminate the OLE DB Commnd. its a very unperformant component.

|||

The best way to improve performance is to get rid of the OLE DB command. If you need to update multiple tables, maybe you should use multiple data flows.

Updated: Jay and Jamie beat me to the punch. You'll notice a common thread in everyone's comments, though...

|||Does it normally take around an hour to insert or update 100,000 records in ssis?|||

sureshv wrote:

Does it normally take around an hour to insert or update 100,000 records in ssis?

If you aren't using the "fast load" option, yes, I can see how that might be the case.|||

sureshv wrote:

Does it normally take around an hour to insert or update 100,000 records in ssis?

Well it really isn't a matter of SSIS performance, the primary factor in your scenario is the relational engine having to process 100,000 commands. Indexes would play a large role in that; if you have too many indexes your inserts will suffer, if you don't have the right one your selects will suffer. Put a WHERE 1=0 at the end of your statements and the performance should improve significantly.

So, yes, it's certainly possible that it could take an hour to insert 100,000 rows. It could also probably be done in 10 minutes with no changes to SSIS. The difference would be in the database itself. But regardless of the database, I think you can improve your SSIS design.

|||

JayH wrote:

Put a WHERE 1=0 at the end of your statements and the performance should improve significantly. .

Really? By not selecting any data?|||

Phil Brammer wrote:

JayH wrote:

Put a WHERE 1=0 at the end of your statements and the performance should improve significantly. .

Really? By not selecting any data?

I said it would be fast, not that it would work.

(It was a joke)
|||

sureshv wrote:

Does it normally take around an hour to insert or update 100,000 records in ssis?

Its impossible to answer this question with a simple yes or no because there are so many variables. Record width, physical location, network latency, CPU, memory, indexes at the destination, insertion method, driver/provider, destination type, .... I could go on and on.

What I CAN tell you is that under the right circumstances it is possible to insert 100000 into a SQL Server table using SSIS withn a few seconds. As I hope you appreciate though, there are many factors.

-Jamie