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"
)
*/