Solved

Increase the performance of my SSIS package

Posted on 2010-09-20
4
385 Views
Last Modified: 2013-11-10
Hi experts,
   I am retrieving data from Oracle and importing to sql server. There are 1 million records in the source.
After getting from the source, I need to LookUp in the existing table whether the PK ID exists or not. For this, I am using LookUp transform in full cache mode. But it is slow.

What is the best way to increase the performance instead of Lookup?
0
Comment
Question by:rajvja
  • 2
4 Comments
 
LVL 3

Accepted Solution

by:
novaspoonman earned 250 total points
ID: 33717619
Are you limiting the lookup query to only the columns you need?
Is there a clustered index on the PK?
Have you inspected the actual execution plan for your lookup query?

http://www.ssistalk.com/2009/09/04/ssis-lookup-cache-modes-full-partial-none/
0
 
LVL 30

Assisted Solution

by:Reza Rad
Reza Rad earned 250 total points
ID: 33722195
Lookup is good,
just  you should be careful about choosing appropriate cache mode,
this is great article from SSIS team blog about lookup cache modes:

http://blogs.msdn.com/b/mattm/archive/2008/10/18/lookup-cache-modes.aspx

0
 
LVL 11

Author Comment

by:rajvja
ID: 33724399
t
0
 
LVL 11

Author Comment

by:rajvja
ID: 33724414
Hi

Sorry mates. I clicked Accept Multiple solutions and submit, its sending Close request. Why I am not sure.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

786 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