?
Solved

SSIS performance: SSMS Import Data vs BIDS Execute Package

Posted on 2013-06-26
8
Medium Priority
?
580 Views
Last Modified: 2016-02-11
I'm working on an ETL project from SQL 2005 to SQL 2012. I'm starting with a source table that has about 450,000 records. Using the SSMS Import Data Wizard, I specified the source SQL Query as
SELECT Contact_ID, Contact_TS, Sex, Date_Of_Birth FROM Contacts. Contact_TS is a timestamp column which is translated as binary(8) for the target table. The target table is created new with a name of Query. This package runs in just a few seconds.

I then created target tables on the target database for Contacts and other tables including some basic foreign keys, which I disabled using EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all". The target table does have a primary key on Contact_ID but it is not an IDENTITY.

I used BIDS to create a new package which uses the same source query but which inserts to the target Contacts table. When I use the Execute Package Utility to run this package, it takes over 6 minutes to process the exact same number of records. I run it from my development computer, not from the SQL Server machine.

Can someone explain why using Execute Package Utility in this way causes it to take more than 30 times as long? What are my options to make this run more quickly.

For production use, I don't expect to use the Execute Package Utility. I expect it to be scheduled or run from some .NET application or process. But I do need to know that I can get sufficient performance with my packages.

Thanks in advance.
0
Comment
Question by:ZekeLA
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 37

Expert Comment

by:ValentinoV
ID: 39281912
" The target table is created new with a name of Query."

Where was that table created?  In the same database as the source?  On the same server? Or on the same target server as the one you're targeting with your BIDS package?
0
 
LVL 1

Author Comment

by:ZekeLA
ID: 39282348
The target table is created on the target database by the package as expected. Since my original post, I've learned how to create and deploy the package from SSDT (aka BIDS) to the target server's Integration Services Catalogs. When I run the package from there, It takes about 60 seconds which is acceptable. I've also eliminated all primary and foreign keys.

The sixty seconds is probably acceptable for my purposes but I still don't know what the Data Import wizard is doing (or not doing) that makes it an order or two faster than executing my deployed package project.
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 39282613
What you should take into consideration is that when you launch a package from your PC, either through BIDS or Execute Package Utility, it actually runs on your PC!  So if you've got a source and a destination server, data will travel over the network from the source server to your PC and back again to the destination server.  That probably explains some of the additional delay you've been seeing.
0
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
LVL 1

Author Comment

by:ZekeLA
ID: 39282719
OK. That addresses the original problem and we can leave it at that.

But running a package from the target DB's Integration Services Catalog still takes an order of magnitude longer than using the Import Data Wizard:

Package = 92.985 seconds
Wizard = 5 seconds

Any thoughts as to what causes the extreme differences?
0
 
LVL 37

Accepted Solution

by:
ValentinoV earned 2000 total points
ID: 39292265
That's a bit difficult to say, too many possibilities.

Perhaps the following is interesting to try out: the last option screen in the Import Data Wizard has got a checkbox that allows you to save the SSIS package which gets generated.  You could save that and then execute it from BIDS or from the server and compare execution speed.  You can also have a closer look at it in BIDS to see how the generated package works, perhaps that explains some difference as well, compared to the one you've implemented?
0
 
LVL 1

Assisted Solution

by:ZekeLA
ZekeLA earned 0 total points
ID: 39297103
Thanks ValentinoV. I did as you suggested and found that the Wizard creates the package using the Fast Load Data Access Mode. Since that's a bulk insert, it is much faster. I'll have to see if I can use that option for my actual package. I'm only appending records from the source query to the target table so it may work. Even if it doesn't, I know why there's a performance difference.
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 39297237
Glad to hear I could help :)
0
 
LVL 1

Author Closing Comment

by:ZekeLA
ID: 39306589
Expert told me how to find the answer. My response includes the results of that investigation.
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question