Maybe this is a simple as adding parenthesis. Your query finds all
records that match two QUAL and fields of SACD and MACD, but when you
add an OR that becomes an "outer OR" to the entire clause, so that the
value of QUAL is no longer of importance. Moreover, this can
completely change the selected plan, so if you need more help, please
tell us the plan with and without the OR.

Also, your SQL reveals that you are abusing the use of LEFT OUTER
JOIN. What you are doing is basically an INNER JOIN, so remove LEFT
OUTER. LEFT OUTER JOINs normally makes it harder for the optimiser,
unless you really know what you are doing and find the optimiser not
performing ideally with an inner join. Hence, use LEFT OUTER JOIN when
you really want output whether or not the right table has a match or
you want to find records that doesn't match. Otherwise, use an inner JOIN.

HTH,
Set

--- In firebird-support@yahoogroups.com, "clausbarth" wrote:
>
> Dear all,
>
> I hope you can help me. I use Firebird 1.5 on a database with 205
> tables,
> around 50 million records and a 4 GB database filesize.
>
> With the query below I am trying to do the following:
>
> * Find all records that match the key (PHSHNO, LOSHNO, REPNO, REPAR)
> * of the records that match 2 values of the field QUAL
> * AND some values of the fields SACD and MACD
>
> This runs perfectly. The Firebird generates the result set in 2
> secs.
>
> But if I add the last part of the query:
>
> * OR the values of the fields SACD and MACD
>
> Then the Firebird server runs for hours (10+) with 100% cpu usage
>
>
> Can you suggest another way to construct the query to acomplish the
> same ?
>
> Thank you very much in advance
>
>
> Best
>
> /Claus
>
>
>
> /* ** Here comes the Query ** */
>
> SELECT DISTINCT
> CAST(spc2.TFS AS VARCHAR(10)) AS FFST
> ,CAST(spc2.QUAL AS VARCHAR(10)) AS Quality
> ,CAST(spc2.dftact AS integer) AS DFT
>
> FROM
> SD_PAINTC PC1
> left outer JOIN SD_PAINTC PC2 ON (PC1.PHSHNO = PC2.PHSHNO) AND
> (PC1.LOSHNO = PC2.LOSHNO) AND
> (PC1.REPNO = PC2.REPNO) AND
> (PC1.REPAR = PC2.REPAR)
> left outer JOIN SD_PAINTC sPC2 ON (PC1.PHSHNO = sPC2.PHSHNO) AND
> (PC1.LOSHNO = sPC2.LOSHNO) AND
> (PC1.REPNO = sPC2.REPNO) AND
> (PC1.REPAR = sPC2.REPAR)
>
> WHERE
> (
> pc1.QUAL like "1557%" AND
> (
> PC2.QUAL like "1763%" AND
> PC2.COATNO = PC1.COATNO+1
> )
> )
> AND
> (
> (
> pc1.macd = "VOS" and
> pc2.macd = "VOS"
> )
> and
> (
> (pc1.sacd = "ENT" and pc2.sacd = "ENT") OR
> (pc1.sacd = "OTH" and pc2.sacd = "OTH") OR
> (pc1.sacd = "REP" and pc2.sacd = "REP") OR
> (pc1.sacd = "WLD" and pc2.sacd = "WLD") OR
> (pc1.sacd = "SCJ" and pc2.sacd = "SCJ") OR
> (pc1.sacd = "NWP" and pc1.sacd = "NWP")
> )
> )
> /* The statement below halts the Firebird server with 100% cpu usage
> */
> /*
> OR
> (
> pc1.macd = "MIS" and pc1.sacd = "VSP" and
> pc2.macd = "MIS" and pc2.sacd = "VSP"
> )
> */