asked
SSIS Perfomance in local machine
Hi , I recently created a SSIS package which was previously a stored procedure to load data into several tables. By doing so I was hoping to gain some performance as well as organize the code. When i execute a particular task from my local machine connecting to the dev servers , it takes hours in the OLEDB task. However when i execute the select statement in sql server it takes hardly a minute. How big is the difference between executing a ssis package from desktop against executing from an integration server ?? Kannan
There are many variables at play here. 1. Where is the data coming from? Does it have to cross the network to get to the SQL Server box? 2. What type of source data are you importing? 3. What are the CPU, RAM, disk speed, etc. characteristics of the server vs. the desktop? 4. What do you mean by "OLEDB task"? Are you using a data flow? If so, are you using a "fast load" mode in your data flow? I'll give you one scenario based on hypothetical answers to those questions. If the source is a text file on a drive on the database server, running a bulk insert in T-SQL just pulls it from the local drive into the database. Running the SSIS package on your local machine, however, forces all traffic to be routed through the network to your machine and back.
`+1` So many times have I seen a SSIS package that runs just a single SP, and I have to ask why? SSIS is a good ETL tool for processing data that is outside of, or crosses the boundary between SQL servers, but when it's a simple process on a single server, it's just SSIS for SSIS sake.
Another +1 indeed. See also
http://www.sommarskog.se/query-plan-mysteries.html
Thanks for the explainations. In my scenario .. I have two Steps in the package. step 1 : ETL (Data flow task) from server A to Server B step 2 : ETL (Data flow task) between DB 1 to DB 2 with in Server B In step 2, there is a union of 4 sql query with joins between two database tables fetching around 20 million records. The union sql query when executed from a management studio completes in minutes whereas when I executed it from my SSIS package it takes much much much more time just in the data flow task. My PC configuration: 16 GB RAM , 3.4 GHz i7 processor which is not bad i suppose. I assume the the problem is while the data moves through the network to my local machines buffer for SSIS to perform the ETL .
18 People are following this question.
