1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to increase speed of procedure?

Discussion in 'SQL PL/SQL' started by awdigrigoli, Feb 15, 2011.

  1. awdigrigoli

    awdigrigoli Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    I have been working with Oracle now for 4 months. We are running .NET applications with Oracle backends. In the past I have had good experience calling stored procedures in SQL 2000 to increase speed.
    Here we have a time consuming process that we are trying to remedy. I have converted a lot of the code from .NET to Oracle, but the time seems to still be a factor.

    Some factors that I think are an issues are:
    1. In test I am using DBLinks to get valid data for my result sets. Are DBLinks considerably slower?

    2. There are two loops that have to be performed to calculate the data. There is an outer and an inner loop each looping 99 times. Is this something that .NET will do faster than Oracle?
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    1. In test I am using DBLinks to get valid data for my result sets. Are DBLinks considerably slower?

    They can be depending upon how much data is passed across them and how robust the network connections are with respect to data transfer. You give no examples or table sizes so this is the best answer you can get.

    2. There are two loops that have to be performed to calculate the data. There is an outer and an inner loop each looping 99 times. Is this something that .NET will do faster than Oracle?

    Possibly; performing such operations in memory rather than using PL/SQL loops is usually considerably faster. This is why Oracle introduced the BULK COLLECT and FORALL constructs to remove the processing overhead of accessing table data a row at a time. You won't know for certain until you test both methods to see which one improves your performance.
     
  3. awdigrigoli

    awdigrigoli Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Thanks David.
    That gave me more to think about and got me started in a slightly different direction. I removed the loops from the main procedure and put them into a new procedure that generates a secondary table that I will use to load the final table. The code that loops does work much faster in .NET, but using it this way should slim down the time.
    As for the dblinks, the completed project will not be using DBLinks and I was only asking to confirm whether or not they perform slower.