Re: extra SQL in stored procedure?

Hey Niall,

   > with a concatenation operator.
>
> WHERE a.pmwrkord_code IS NOT NULL ||sCurrWORest;
  didn't work. But I appreciate you taking the time to respond.

Bryan

In article <8vg6cc$jaq$1_at_soap.pipex.net>,   "Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote:
> 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.
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Nov 22 2000 - 13:31:52 CST