September 29, 2007 at 2:39 am
I have a performance related question about the DTS package in
sqlserver 2000 which i have developed
We have developed a DTS package which will migrate a view 'ATTRITION'
from Sqlserver 2000 to an Oracle database.The design of the package
is as follows
Step 1 : It checks for the existance of the table 'ATTRITION' in
oracle database, if table 'ATTRITION' is not there it will create a
table called 'ATTRITION' in the oracle db.If the table 'ATTRITION' is
already present in the oracle db,then the table is truncated.
Step 2: The view 'ATTRITION' is migrated to Oracle table 'ATTRITION'.
For the migration, i have used a connection object which connects to
sqlserver 2000 and for oracle connection i have used another
connection object 'Microsoft ODBC driver for oracle' and i have joined
both the connection objects with 'Transform data task' task which maps
one to one from sqlserver 2000 where view 'ATTRITION' exists with
oracle database where Table 'ATTRITION' exists.
Roughly i have around 65000 rows in 'ATTRITION' view of sqlserver 2000
which needs to be migrated.When im running the package on my system it
takes around 4 minutes to migrate all the rows but when im running it
on the server it takes a lot lot of time more than 1 hour.
The view definition im using has more than 10 tables joined
together.But if its a problem of query used in the view,and if i run
the view seperately it quickly displays the data hardly takes 1
minute. and even if i run the package on my local pc it doenst take
much time.Now my confusion is why its taking soo much time on
server.If i create a indexed view then will it solve my problem.Please
suggest...
Thanks in advance
Regards
Arvind L
September 29, 2007 at 5:14 am
Can you catch the execution plans of the package? Try to see if there's a difference in the # of rows processed when executed to ship on Oracle. Let us know how this one turns out.
September 29, 2007 at 6:07 pm
You kept checking the SQL Server view, but when you ran the DTS package, you were inserting data into Oracle table. Were you sure it was the view causing the problem and it had nothing to do with Oracle? Was network a problem? I assumed both databases were in different servers.
just curious
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply