>>This test was returning 1 record from a 47,000 record table based on the KeyID. My times were 0.05 seconds for SPT and 0.09 seconds via the package.
>
>Yeah, if even the way with more overhead were still less than 1/10 of a second, I'd probably be fine going this way. I'll do more testing with my server and data, but so far it appears my SP overhead is quite a bit more than what you are seeing (more like .1 or less for SPT vs. .25 to .5 or more for SP).
>
>For one execution, even that is not a huge deal, but if a form needs to run 5-10 queries to get related data, the difference would magnify into a big deal.
>
>Thanks,
>Kelly

I did a 100 iteration test using SP vs SPT against a production Oracle server. I queried a 47,000+ record table. The results the total times were: SP = 3.000 seconds, SPT = 1.266 seconds. When I just did 1 iteration, the times were 0.034 and 0.013 seconds. Yes there is a slight penalty, but I can live with it. The SQL returned 5 records. The test code:

clear
lnHandle = SQLSTRINGCONNECT('Driver=Oracle ODBC Driver;' ;
         + 'UID=myuid;PWD=mypwd;DBQ=ORCL')
lcSQL1 = "{call GetPermitsByCity.ReturnCursor('SOME_CITY')}" 
lcSQL2 = "select * from permits where city = 'SOME_CITY'"
?
lnTot = 100
A = 0
B = 0
FOR lnI = 1 TO lnTot
   x = SECONDS()
   lnRetVal = SQLEXEC(lnHandle, lcsql1, 'crsPermits1')
   y = SECONDS()
   lnRetVal = SQLEXEC(lnHandle, lcsql2, 'crsPermits2')
   z = SECONDS()
   A = A + (y - x)
   B = B + (z - y)
ENDFOR
? A, A/lnTot
? B, B/lnTot
SQLDISCONNECT(lnHandle)
RETURN

Here is the Stored Proc:

CREATE OR REPLACE PACKAGE GetPermitsByCity AS
 Type RetCursor Is Ref Cursor;

 Procedure ReturnCursor(pCursor OUT RetCursor, c_city IN permits.city%type);

END;
/
show error
CREATE OR REPLACE PACKAGE BODY GetPermitsByCity AS

 Procedure ReturnCursor(pCursor OUT RetCursor, c_city IN permits.city%type) is
 Begin
  open pCursor for select * from npdes.permits where city = c_city;
 End;
END;
/
show error