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
 SQL Server 2005 Forums
 SSIS and Import/Export (2005)
 DTS to SSIS - very slow performance

Author  Topic 

JahDW
Starting Member

2 Posts

Posted - 2008-02-07 : 12:26:05
I have a bunch of packages that take views and create tables from them. Some of the views are rather complex, but the packages themselves are very simple... drop and re-create a table using the data from a view on the same server. We create a new DB for each year, and this year we've upgraded to a new server with SQL 2005, so our DTS packages on the 2000 SQL server had to be recreated in SSIS on the new server. No problem, as I said the packages are really simple. But when I create the packages in SSIS they now take an extremely long time to execute, and I cannot figure out why.

For instance, one DTS package would take approximately 5 minutes to run when the view contained hundreds of thousands of rows and the underlying tables contained millions. But now, even with MUCH smaller tables (since it's the beginning of the year, new DB) the SSIS package I created on the new server takes over an hour, literally. The view that the SSIS package is using to create the table only takes about 15 seconds to execute in management studio (only about 16,000 rows). How can this possibly take so long??

the new server is virtually the same hardware-wise... 4 x 2400mhz, 4gb ram, win2k3 server


Qualis
Posting Yak Master

145 Posts

Posted - 2008-02-07 : 13:11:00
There appears to be a bug (feature) in the OLE DB Source component that causes this problem. When you are in the configuration for the source, are you using the dropdown item "Table or View" and then the dropdown to select your view? If so, the component does some strange validation stuff and when used on Views can produce astronomically high runtimes as well as huge slowdowns in the designer. Instead of using the "Table of View" selection, use "SQL Command" and then put "Select * From MyViewName" or some such select in the command box. This will resolve that particular issue.
Go to Top of Page

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2008-02-07 : 13:12:21
Try read about buffering - http://sqljunkies.com/WebLog/ashvinis/archive/2005/04/06/10467.aspx
Go to Top of Page

JahDW
Starting Member

2 Posts

Posted - 2008-02-07 : 15:58:40
quote:
Originally posted by Qualis

There appears to be a bug (feature) in the OLE DB Source component that causes this problem. When you are in the configuration for the source, are you using the dropdown item "Table or View" and then the dropdown to select your view? If so, the component does some strange validation stuff and when used on Views can produce astronomically high runtimes as well as huge slowdowns in the designer. Instead of using the "Table of View" selection, use "SQL Command" and then put "Select * From MyViewName" or some such select in the command box. This will resolve that particular issue.



WOW. Thank you so much!! runs in seconds!

i owe you a beer
Go to Top of Page

Qualis
Posting Yak Master

145 Posts

Posted - 2008-02-07 : 16:25:27
We had a package that pulled data into a reporting warehouse (yes, the reporting guy wrote it...) and it was accessing 10-15 large views (collating millions of rows) and the execution time was > 6 hours. I jumped in and fixed all of those (which took several hours because the designer was very unhappy) and the execution time went down to 1 hour tops. Not a fun quark to have to discover and fix!
Go to Top of Page

kellyg
Starting Member

1 Post

Posted - 2008-08-13 : 08:49:18
quote:
Originally posted by Qualis

There appears to be a bug (feature) in the OLE DB Source component that causes this problem. When you are in the configuration for the source, are you using the dropdown item "Table or View" and then the dropdown to select your view? If so, the component does some strange validation stuff and when used on Views can produce astronomically high runtimes as well as huge slowdowns in the designer. Instead of using the "Table of View" selection, use "SQL Command" and then put "Select * From MyViewName" or some such select in the command box. This will resolve that particular issue.



Is there a similar "feature" in the OLE Destination component? I am noticing very slow (time is doubled) SSIS performance compared to DTS using an ODBC source, that, under SSIS requires a data conversion task to convert from unicode, to a OLE Destination.
Go to Top of Page

AlisterN
Starting Member

4 Posts

Posted - 2009-02-26 : 23:32:20
I am having the same performance problem under almost the same conditions, but the solution that worked for the original poster (change Table and Views setting to SQL Command) doesn't work for me.

I have a DTS package in SQL Server 2000 that extracts all records (500,000) from an AS400 file to a new table in SQL Server using ODBC, and it takes about 10 minutes.
My SSIS package in SQL Server 2005 uses the same OBDC DSN, on the same server takes about 90 minutes.
I have looked at the link on buffering but haven't fiddled with buffer settings. In my view the default settings should be good enough to give at least a comparable performance.

Any ideas?
Thanks
Go to Top of Page

AlisterN
Starting Member

4 Posts

Posted - 2009-04-17 : 01:46:54
Update...

I have tracked the problem down to the transfer rate. The DTS package transfers at about 700 KB/sec but the SSIS transfers at about 75 KB/sec. They both run off the same box, across the same network to the same AS400 so I am thinking that the difference is in the ODBC connection.

DTS uses an ODBC data source "Client Access ODBC Driver (32-bit)" using CWBODBC.DLL that has a description "iSeries Access ODBC Driver".

SSIS uses a connection created as "ODBC" with the same data source as DTS (selected from a drop-down). However, when the connection is edited, the provider is displayed as ".Net Provider/ODBC Data Provider".

Maybe the ".Net" layer is causing the bottleneck?

Can anyone comment on this?

Incidently, I have fiddled with buffer settings (and everything else) without any performance improvement. See my MSDN post. [url]http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/162e55e5-b64b-423e-94c1-dd764ca1f683[/url]
Go to Top of Page

Qualis
Posting Yak Master

145 Posts

Posted - 2009-04-21 : 10:23:11
Hey,

I haven't been around for a while because I've been pretty busy with my Thesis. Check the OLE DB Destination data access mode. Make sure that it is set to fast load. I wrote several posts on benchmarking SSIS performance and one post in particular addresses the fast load option (http://weblogs.sqlteam.com/jamesn/archive/2008/02/11/60504.aspx)
Go to Top of Page

AlisterN
Starting Member

4 Posts

Posted - 2009-04-22 : 18:31:01
Thanks for the reply.
I have tried OLEDB Destination with the fast-load option, and it performs approx the same as SQL Server destination.

The bit I don't understand is that DTS (using ODBC) runs at about 700 KB/sec and SSIS (using IBM OLEDB) only 240 KB/Sec, both running on the same box across the same network to the same source.

(I have discovered that .Net/ODBC is really just std ODBC with a .Net cover over it - and it runs very slowly. Also, Microsoft's OLEDB driver for the AS400 only runs on Enterprise Edition, and upgrading isn't an option for me.)

Cheers
Alister
Go to Top of Page

Qualis
Posting Yak Master

145 Posts

Posted - 2009-04-23 : 15:27:43
I didn't read your post fully. My apologies. Create a new Connection Manager. When you do, pick New OLEDB Connection... Then New... In the Provider dropdown, pick the same provider that the DTS package is using. This ought to fix your speed issue. If you don't see it then the OLE DB driver isn't installed (or installed correctly).
Go to Top of Page

alexm
Starting Member

1 Post

Posted - 2010-02-27 : 22:26:18
While this is not an elegant solution...

Windows Server 2003
MSSQL 2005 Enterprise
iSeries Access V5R4

Using the iSeries Access ODBC provider, 74K rows of data = 3 minutes.

After backing off .Net2 SP2 from Server 2003, 74k rows of data = 45 seconds.

Still not the sub 20 seconds I'd expect, but I've done it on 3 boxes and .Net 2 SP2 (and 3.5 cumulative, etc...) creates some horrible throughput issues.

Go to Top of Page

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2010-03-08 : 14:16:08
AlisterN,
Just out of curiousity. did you fix your issue?
Go to Top of Page

AlisterN
Starting Member

4 Posts

Posted - 2010-03-09 : 05:11:35
Not really. I ended up sticking with DTS packages. They worked much faster given the limitations I had.
Luckily the project doesn't involve anything too fancy so I'm getting by just fine.
Go to Top of Page

niechen861102
Starting Member

9 Posts

Posted - 2010-08-27 : 04:33:44
I created on the new[url=http://www.thomassaboschmucksale.com/]charm club[/url] server takes over an hour, literally. The view that the SSIS package is using to create the[url=http://www.thomassabocharmemarkt.com/]thomas sabo charms[/url]
table only takes about 15 seconds to execute in management studio (only about 16,000 rows). [url=http://www.thomassabocharmemarkt.com/]schmuck[/url]How can this possibly take so long??

Go to Top of Page
   

- Advertisement -