Why Oracle9i stored procedures are slower than direct Sql Query statements in VB,COM+
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Why Oracle9i stored procedures are slower than direct Sql Query statements in VB,COM+

  1. #1
    Join Date
    Apr 2004
    Posts
    13

    Why Oracle9i stored procedures are slower than direct Sql Query statements in VB,COM+

    We are using VB6.0,ADO,COM,COM+ and Oracle9i Back end

    The normal assumption we made is stored procedures are faster than direct Sql statements passed from connection or command object. After converting Sql statements to pakages and stored procedures and functions our application perform slower than before.

    We have done PINING of packages.Hit ratios are above 99%, have 512MB RAM,SGA instance is 129MB.

    The performance before using SP was 10Transactions per second
    The performance after using SP is 7Transactions per second.

    Can anyone explain how to improve performance,why the SPs are slower in COM,COM+ environment?

    Thanks in Advance....

  2. #2
    Join Date
    May 2003
    Location
    Pretoria, Rep of South Africa
    Posts
    191
    Maybe bad pl/sql....or the fact that your using VB
    Able was I ere I saw Elba

  3. #3
    Join Date
    Apr 2004
    Posts
    13

    Re: Why Oracle9i stored procedures are slower than direct Sql Query statements in VB,COM+

    Originally posted by kiranakula76
    We are using VB6.0,ADO,COM,COM+ and Oracle9i Back end

    The normal assumption we made is stored procedures are faster than direct Sql statements passed from connection or command object. After converting Sql statements to pakages and stored procedures and functions our application perform slower than before.

    We have done PINING of packages.Hit ratios are above 99%, have 512MB RAM,SGA instance is 129MB.

    The performance before using SP was 10Transactions per second
    The performance after using SP is 7Transactions per second.

    Can anyone explain how to improve performance,why the SPs are slower in COM,COM+ environment?

    Thanks in Advance....
    here we r using reference cursors.It may effect the performance??

  4. #4
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Btw, who said you direct SQL is bad over Procs?
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  5. #5
    Join Date
    May 2003
    Location
    Pretoria, Rep of South Africa
    Posts
    191
    reference cursor is better/effective, as it is used as a pointer in the cursor.

    Is this server dedicated to Oracle or shared to another service?(Just asking)
    Able was I ere I saw Elba

  6. #6
    Join Date
    Apr 2004
    Posts
    13
    Originally posted by robertbalmer
    reference cursor is better/effective, as it is used as a pointer in the cursor.

    Is this server dedicated to Oracle or shared to another service?(Just asking)
    Dedicated server

  7. #7
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092

    Re: Why Oracle9i stored procedures are slower than direct Sql Query statements in VB,COM+

    Originally posted by kiranakula76
    The normal assumption we made is stored procedures are faster than direct Sql statements passed from connection or command object.
    I love it I am guessing you didn't benchmark that theory, eh?

    I don't work with VB and the associated single-platform-closed-pseudo-technologies, but conceptually there should be no difference. You might tweak your client side fetch arraysize to improve performance, but you would have to profile where the slowdowns are in your code.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width