Why performancediff package--direct query?

From: Göran K <goranNOgoSPAM_at_champion.se.invalid>
Date: 2000/03/14
Message-ID: <153c7f86.00c0d539@usw-ex0106-048.remarq.com>#1/1

We have a fairly complicated view 'vy_leveranstid', but I don't think it's specification is relevant to my question.

If we execute a select count(*) in SQL*Worksheet it takes less than a second.

If we execute it as a packaged function it takes about 35 seconds.

Why? Anyone have a clue?

The function (part of leveranstabell package):

function har_transportor(cbutik_id varchar2 default NULL, clev_satt_id varchar2 default NULL, clevadress_id varchar2 default NULL) return boolean
is

cantal_tider integer;
begin

   select count(*)
   into cantal_tider
   from vy_leveranstid
   where (butik_id=cbutik_id or cbutik_id is NULL)    and (lev_satt_id=clev_satt_id or clev_satt_id is NULL)    and (lev_adress_id=clevadress_id or clevadress_id is NULL);    return cantal_tider>0;
end har_transportor;

If we take the select, removes the into-clause and replaces the cbutik_id,
clev_satt_id and clevadress_id with values and executes the query, it takes less than a second, if we do execute:

declare
i boolean;
begin
i := leveranstabell.har_leverantor(1,2,4711); end;

it takes 35 seconds

Why?

regards
Göran

Sent from RemarQ http://www.remarq.com The Internet's Discussion Network * The fastest and easiest way to search and participate in Usenet - Free!

Received on Tue Mar 14 2000 - 00:00:00 CST