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

[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....

[robertbalmer]

Maybe bad pl/sql....or the fact that your using VB

[kiranakula76]

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??

[abhaysk]

Btw, who said you direct SQL is bad over Procs?

[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)

[kiranakula76]

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

[marist89]

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.