ssis 6M rows - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

ssis 6M rows

Author

Topic

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2010-08-23 : 19:19:38

I have a package that transfer 6M rows in 1m 20sec.
Now anyone know how to tune it to improve the speed to 10 secs?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-08-23 : 19:21:42

Spend a lot of money on hardware, and specifically your disks.

6 million rows in 80 seconds is decent. I doubt you'll get it done in 10 seconds.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

X002548
Not Just a Number

15586 Posts

Posted - 2010-08-23 : 19:26:01

quote:
Originally posted by basicconfiguration

I have a package that transfer 6M rows in 1m 20sec.
Now anyone know how to tune it to improve the speed to 10 secs?

6 Million rows in 10 seconds?

I don't think it's physically possible

However...bail on SSIS and bcp the data into a staging table

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-08-23 : 19:26:49

SSIS is actually faster than bcp, according to what I've read. Bcp is second fastest. First is bulk loader/bulk insert.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2010-08-23 : 19:27:12

It has to be a way to do it.
I'm pulling all the rows in this table that has 6 columns. No indexes. I guess indexes are not going to help much as I'm pulling all the records.

X002548
Not Just a Number

15586 Posts

Posted - 2010-08-23 : 19:27:47

Wait not enough info..where is the data now??? and what form(at) is it in?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-08-23 : 19:30:53

Indexes will only slow it down.

And yes you can probably get it to be faster, but it'll cost money. This is a hardware issue. You are constrained by the speed of the writes of your disks.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

X002548
Not Just a Number

15586 Posts

Posted - 2010-08-23 : 19:52:53

quote:
Originally posted by tkizer

SSIS is actually faster than bcp, according to what I've read. Bcp is second fastest. First is bulk loader/bulk insert.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Really...are you sure? I thought BULK INSERT used the same mechanism...

And SSIS is Faster? That would MAYBE be true until you use the "T" in ETL...then there is NO WAY it's faster....

ETL...man, I wish I could generate new buzzwords that ALL of humanity sucks down like a drunk given a free beer...

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-08-23 : 20:09:32

I've never tested it, only gone by other peoples' tests.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources