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

Printable View

04-22-2004, 02:14 AM
kiranakula76

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....
04-22-2004, 03:35 AM
robertbalmer

Maybe bad pl/sql....or the fact that your using VB :D
04-22-2004, 03:53 AM
kiranakula76

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

Quote:

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??
04-22-2004, 04:05 AM
abhaysk

Btw, who said you direct SQL is bad over Procs?
04-22-2004, 04:14 AM
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)
04-22-2004, 05:21 AM
kiranakula76

Quote:

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
04-22-2004, 08:25 AM
marist89

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

Quote:

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 :D 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.