DTS Tuning

Question

DTS Tuning

karthik M

One Orange Chip

Points: 29139
More actions
May 28, 2007 at 10:33 am

#69821

Hi,

Recently i have created one package to move sybase data to sqlserver2000.I have used ODBC connection to connect sybase database from sqlserver.
Problem :
Package is taking long time to complete their execution.
Total Records : 700,000
Time Taken : 1/2 hour
I have tried the following methods to tune package.

1. Setting Priority
2.Using FastLoad
3.Assigining Task Priority

Anybody help me to reduce the execution time please.

Thanks and Regards
Karthik

karthik

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

philcart SSC-Forever Points: 47796 More actions · Answer 1

Is this a straight datapump copying data directly into the destination table? If not, eliminate any processing during the datapump phase. Do any data manipulation via stored procedures after the data has been loaded.

Does the ODBC driver allow you to specify how many rows to retrieve? If so, then experiment with different values and set the fetch buffer size in the datapump task to the same value (it has a max of 9999).

What is the structure of the destination table? Are there a lot of constraints that are being checked while data is loaded?

--------------------
Colt 45 - the original point and click interface

karthik M One Orange Chip Points: 29139 More actions · Answer 2

Hi Phill,

1. can you explain me about 'straight datapump copying' ?

2. There is no data manipulation via stored procedures after the data has been loaded.

3. Current ODBC drive setup is as follows:

Can i change 'Fetch Array Size' value to 1000 or some thing else. Which one is the best value ? i.e Assigning low values or high values to 'Fetch Array Size' and what is the maximuim value given to 'Fetch Array Size' ?

is 'Fetch Array Size' affect the odbc performance or not ?

4. Transform Data Task setup is as follows.

Can i change 'FetchBuffer Size' value to 1000 or some thing else. Which one is the best value ? i.e Assigning low values or high values to 'FetchBuffer Size'.

5.Actually i disabled the 'check constraint' check box.

Table Structure :-

Composite PrimaryKey : 2

Total No of Columns : 15

No foreign keys are available.

Please clarify me.

Thanks and Regards

Karthik

karthik

philcart SSC-Forever Points: 47796 More actions · Answer 3

Firstly, posting images doesn't work in this forum so try to list the dialog setting in text.

1) A 'straight datapump' is basically a table to table copy. No transformation of data, just pumping straight through matching column for column.

2) I was meaning that if you were doing data manipulation in the datapump task then remove it and perform the manipulation after the data is in SQL Server.

3) You can change the Fetch Array Size to whatever gives you the best throughput. Only you can say what the best setting is because you can test the settings in your environment. I'm not 100% certain with this particular driver, but in other ODBC drivers I've used this 'Fetch Array Size' setting determines how many rows to retrieve from the source system. A setting of 1000 will mean you will process 1000 rows at a time in the datapump.

4) Given that this is the rate that rows will be arriving, orior experience tells me to keep the 'FetchBuffer Size' set at the same value as the 'Fetch Array Size'. This way you'll process all rows in the buffer in one hit.

5) Is the destination table a staging table? If so, remove the primary key. If the data is being inserted in a pre-sorted fashion, create a clustered index on the sort key. If the destination table is the final resting place for the data and you can't change the primary key, etc... then create a copy of the table and use that as the destination.

--------------------
Colt 45 - the original point and click interface

karthik M One Orange Chip Points: 29139 More actions · Answer 4

Hi Phill,

Thanks for your explaination.

1.when we perform DTS through ODBC instead of OLEDB ,is it true,it will take much time to copy data from source to destination ?

Because

2.I am working from offshore ,but server is placed on US.If the same package is executed from client place,can we found substantial DTS execution time difference ?

3.I am using Convert function within DTS package.

for example,

sybase table :

create table emp

(

eno int,

EmpSalary int

)

insert into emp values (1,5000)

insert into emp values (2,7000)

insert into emp values (3,2000)

insert into emp values (4,8000)

insert into emp values (5,6000)

DTS Package contains --> (source - sybase(emp table))

select eno,convert(varchar(255),EmpSalary) from emp

---> (destination - sqlserver (emp table))

sqlserver emp table get records in this way. will it affect DTS Performance ?

This is the only manipulation used within the DTS package.

karthik

philcart SSC-Forever Points: 47796 More actions · Answer 5

1. Yes there will be a slight performance degradation as ODBC sends a lot more messages back and forth between source and destination. There's nothing you can do about that, it's just the way ODBC is. If you have an OLEDB driver that works, then it would be better to it instead.

2. DTS is a client application. That means it executes where its started from. eg: when you start it from your workstation that's were it executes. If it's started from the server (via SQL Agent for example) then it will execute on the server. Naturally this means that whe you execute the package there will be an increase in time due to network traffic.

3. If that is all you are transferring, just those two columns, then there won't be that much of an impact on the performance.

--------------------
Colt 45 - the original point and click interface

karthik M One Orange Chip Points: 29139 More actions · Answer 6

Hi Phill,

1. Any oledb provider for sybase 12.5.3 available in market ? I have searched in many websites,but i am not able to find out it.

2. shall we change any sqlserver setting to increase DTS package execution time ,like changing protocol setting. By default ,TCP /IP protocol is assigned.

will it give significance different in execution time ?

3.Tables which is using convert() function contains 20 to 30 columns.

Basically my Team head asked me to reduce overall DTS package execution time from 2 hours to 5 minutes.

Because we are running some batch jobs parallelly.

Thanks and Regards

Karthik

karthik

philcart SSC-Forever Points: 47796 More actions · Answer 7

I work with SQL Server and don't know anything about Sybase. Maybe you should ask on a Sybase forum, or Google it http://www.google.com/search?q=Sybase+OLEDB

The TCP/IP protocol will give you the best performance. As I posted earlier the other things you can do to improve performance are adjusting the FetchArray Size and Fetch Buffer Size settings.

Ok, so for #3, the example you provided was not an example of what you are actually transferring. I would help if you provided a more realisitic example

--------------------
Colt 45 - the original point and click interface

karthik M One Orange Chip Points: 29139 More actions · Answer 8

Hi Phill,

Regarding #3, I am using the below query to copy sybase data to sqlserver.This table contain 750,000 records.It is taking 1/2 hrs to complete its execution.

select CompanyInstitNbr,ProjectId,TrancheNbr,ParticInstitNbr,TransNbr,AcctNbr,SubAcctNbr,SubAcctCode,DeptCode,DivCode,CurrencyCode,ueDate,PostingDate,convert(varchar(255),AcctEntryAt)AcctEntryAt,convert(varchar(255),AcctEntryUsd)AcctEntryUsd,AcctEntryQty,DatetimeStamp,UserId

from icase.dbo.AcctEntry

Thanks and Regards

Karthik

karthik