Re: extra SQL in stored procedure?

Hi

have you (or your dba) identified why the performance is poor? If it is only poor for example with certain combinations of conditions? Is it poor compared with the same query in sqlplus?That would be my start point. You might well find that cedtain columns or combinations thereof need to be indexed. My experience is that the performance advantages of stored procedures over asp are not really large enough, and I'd be pretty much willing to bet that they disappear when the procedure effectively executes ad-hoc code.

My other suggestion is for the asp approach - assuming you stick with it. You might want to to try using asp variables in building up your sql. IIRC that results in bind variables being passed to Oracle and thus better performance. It also makes the code easier to understand.

AS far as you code snippet (it is a snippet right?) goes I believe that you would replace

WHERE a.pmwrkord_code IS NOT NULL sCurrWORest

with a concatenation operator.

WHERE a.pmwrkord_code IS NOT NULL ||sCurrWORest;

HTH

--
Niall Litchfield
Oracle DBA
Audit Commission UK
<newguysanfran_at_my-deja.com> wrote in message
news:8vesos$alo$1_at_nnrp1.deja.com...


> hello,

>

>

>    I am connecting to an Oracle 8.1.5 db on Ultrix through ASP. I have

> built up a SQL statement and am firing it off against the database.

> This works but performance is poor. I wanted to use a stored procedure.

> The problem is that in my form I allow the used to select several

> criterion on which to filter and they build up a string as such:

> AND spbldinf_code IN ('1498','9407') AND pmwostatus_code

> = 'Active/Recharge' and a.its_current_date >= to_date

> ('8/21/2000','mm/dd/yy') and a.its_current_date <= to_date

> ('11/21/2000','mm/dd/yy')

> Which I append to my SELECT statement, but when I create my stored

> procedure I can't seem to pass in the extra SQL as a parameter. I

> wanted to know if this is possible. My procedure keeps compiling with

> errors and I don't know how to fix it.

>

> CREATE PROCEDURE getWorkOrder (sCurrWORest IN VARCHAR2(100)) AS

>    BEGIN

>       SELECT a.pmwrkord_code, a.description

>       FROM pmwrkord a

>       WHERE a.pmwrkord_code IS NOT NULL sCurrWORest

>   END;

>

>

> Sent via Deja.com http://www.deja.com/

> Before you buy.