Home » RDBMS Server » Performance Tuning » Caling package
Caling package [message #225799] Wed, 21 March 2007 09:34 Go to next message
danijelv
Messages: 12
Registered: March 2007
Junior Member
Hy,

I have problem with executing my package. I have two tables around each 1,000,000 records. When I join these two tables and use package to equal values execution time is 300% slower if i use statement without using package.

Sample with package:
select
package.equal(x.id,y.id) number
join ...
from ...
duration time: 17000hsec

Sample without package:
select
case when x.id=y.id then 0 else 1 end;
join ...
from ...
duration time: 400hsec

Of course I also tried with empty function in package it only returns 1; and also 300% slower.

How should i tune execution speed of package.

Best regards,
Dany
Re: Caling package [message #225805 is a reply to message #225799] Wed, 21 March 2007 09:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

SQL and PL/SQL engines are two independent engines.
In the first case, you continously switch from one engine to the other.

If we think it as a program, it is the same than calling an external program for each line instead of directly coding it in the main.

The solution is to not use PL/SQL if you can do without it.

Regards
Michel
Re: Caling package [message #225967 is a reply to message #225805] Thu, 22 March 2007 02:13 Go to previous message
danijelv
Messages: 12
Registered: March 2007
Junior Member
Yes by coding i lost meaing of program, same thing should be on one place. Packages are great solution.

I read about native compiling,' i tried with that.

Thanks a lot

Regards

Dany
Previous Topic: Database Space Usage Measurements
Next Topic: Tuning Needed
Goto Forum:
  


Current Time: Wed Dec 07 14:23:46 CST 2016

Total time taken to generate the page: 0.05880 seconds