Re: Sorrry Forgot to attach the query

Queries that include the word LIKE are going to take longer, especially in large tables. But you have SEVERAL like statements. Bummer.

However, if this is an often-executed query, you need to set up some indexes. Also, if this is executed in the VB environment, you probably need to create a command object so that your Oracle server doesn't choke everytime it executes this thing. It would only be slow on the first query, not subsequent queries.

What environment are you working in?

In article <efU06.4627$tk5.334912_at_dfiatx1-snr1.gtei.net>,   "Al Manley" <res0691x_at_gte.net> wrote:
> This thing takes for ever to run. Any suggestions to speed it up.
>
> SELECT YELLOWPR.STATUS AS YPRSTATUS, YELLOWPR.REQNUM,
 V_POLINE.ITEMNUM,
> YELLOWPR.CURRENTDATE, PRSTATUS.STATUS AS PRSTATUS,
 PRSTATUS.CHANGEDATE AS
> PRCHANGEDATE, V_POLINE.PONUM,
> POSTATUS.STATUS, POSTATUS.CHANGEDATE AS POCHANGEDATE,
 V_PO.VENDELIVERYDATE,
> V_MATRECTRANS.ACTUALDATE, YELLOWPR.QUANTITY, V_INVENTORY.LOCATION,
> V_INVENTORY.BINNUM, YELLOWPR.ASSETNUM, YELLOWPR.PARTMODEL,
> YELLOWPR.DESCRIPTION, YELLOWPR.SUPPLIER, YELLOWPR.PARTMFG,
> YELLOWPR.MACHINEMODEL, YELLOWPR.REQDEPT
> FROM MAXIMO.V_PO , MAXIMO.V_POLINE , MAXIMO.POSTATUS , MAXIMO.PRSTATUS
 ,
> MAXIMO.YELLOWPR , MAXIMO.V_INVENTORY , MAXIMO.V_MATRECTRANS
> WHERE ( (YELLOWPR.REQNUM = PRSTATUS.PRNUM(+)) AND
> (YELLOWPR.ITEMNUM = V_POLINE.ITEMNUM(+)) AND
> (V_POLINE.PONUM = V_PO.PONUM(+)) AND
> (V_POLINE.PONUM = POSTATUS.PONUM(+)) AND
> (V_POLINE.ITEMNUM = V_MATRECTRANS.ITEMNUM(+)) AND
> (V_POLINE.ITEMNUM = V_INVENTORY.ITEMNUM(+)) AND
> (PRSTATUS.STATUS IS NULL OR PRSTATUS.STATUS LIKE 'APPR' OR
 PRSTATUS.STATUS
> LIKE 'CAN') AND (V_POLINE.PONUM IS NULL OR V_POLINE.PONUM LIKE '00%')
 AND
> (POSTATUS.STATUS IS NULL OR POSTATUS.STATUS LIKE 'CAN' OR
 POSTATUS.STATUS
> LIKE 'APPR') AND (V_INVENTORY.LOCATION IS NULL OR
 V_INVENTORY.LOCATION
> LIKE
> 'KG' OR V_INVENTORY.LOCATION LIKE 'KT' OR V_INVENTORY.LOCATION
 LIKE
> 'KW')
> AND (YELLOWPR.MACHINEMODEL LIKE 'NBC%' OR YELLOWPR.MACHINEMODEL LIKE
 'NSL%'
> OR YELLOWPR.MACHINEMODEL LIKE '300N%' OR YELLOWPR.MACHINEMODEL LIKE
 '300n%'
> OR YELLOWPR.MACHINEMODEL LIKE 'FSM%' OR YELLOWPR.MACHINEMODEL LIKE
 'NEW
> FSM%'
> OR YELLOWPR.MACHINEMODEL LIKE 'NFSM%') AND (YELLOWPR.REQDEPT LIKE
 'B%') )
>
>

Sent via Deja.com
http://www.deja.com/ Received on Sat Dec 23 2000 - 13:56:53 CST