SQL Server Performance

DTS package performance issue on sqlserver 2000

Discussion in 'SQL Server DTS-Related Questions' started by lakshmi.aravind@fidelity.co.in, Sep 29, 2007.

  1. 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
  2. satya Moderator

    Have you checked the permission for the SQL SErver agent account on that Oracle data source?
    If it is taking longer time then it depends on the number of resources usage allocated by the SQL engine, try to monitor the activity during this process.
  3. satya and Ranjit.. Thanks for the quick response ..actually i didnt get ur point satya...checking the permissions for sqlserver agent account on oracle data source and monitoring..can u please tel me how to check for the permissions on oracle data source...im very new to sql server world...
  4. satya Moderator

    This is something you have to ask Oracle DBAs by referring the SQLAgent account that is used, sometimes due to incorrect permissions the package might fail or run slow.
  5. ranjitjain New Member

    Hi,
    You dont need to create indexed view where you have only 65k rows, I think it must be taking time on oracle end, just check how long does it take when you fire INSERT...SELECT into dummy sql server table.

Share This Page