Board index » delphi » Interbase takes 100% CPU-time on joined query

Interbase takes 100% CPU-time on joined query

Hello,

I encountered a strange problem using Delhpi 4.02 and Interbase 5.1:

I have 3 tables: Kunde, AP and Kontakt. AP has an FK to Kunde and
Kontakt has a FK to AP.

I do the following select now:

select Kunde.K_ID, Kunde.KA_ID, Kunde.Bezeichnung,
Kunde.Strasse, Kunde.Postfach, Kunde.PLZ, Kunde.PPLZ, Kunde.Ort,
Kunde.L_ID, Kunde.InVG, Kunde.EW, Kunde.Telefon, Kunde.Telefax,
Kunde.TZone, Kunde.email, Kunde.Bemerkung,
Kunde.LastUpdate, Kunde.Geloescht
from Kunde
where (Geloescht=0 or Geloescht IS NULL)
order by Kunde.Bezeichnung

All works fine.

if I change the select to

select Kunde.K_ID, Kunde.KA_ID, Kunde.Bezeichnung,
Kunde.Strasse, Kunde.Postfach, Kunde.PLZ, Kunde.PPLZ, Kunde.Ort,
Kunde.L_ID, Kunde.InVG, Kunde.EW, Kunde.Telefon, Kunde.Telefax,
Kunde.TZone, Kunde.email, Kunde.Bemerkung,
Kunde.LastUpdate, Kunde.Geloescht
from Kunde, AP, Kontakt
                ^^^^ AP & Kontakt are in the query without no fields
where (Geloescht=0 or Geloescht IS NULL)
order by Kunde.Bezeichnung

the Interbase-Server takes 100% CPU-Time and I have to reboot the
NT-machine.

If I change the query now to

select Kunde.K_ID, Kunde.KA_ID, Kunde.Bezeichnung,
Kunde.Strasse, Kunde.Postfach, Kunde.PLZ, Kunde.PPLZ, Kunde.Ort,
Kunde.L_ID, Kunde.InVG, Kunde.EW, Kunde.Telefon, Kunde.Telefax,
Kunde.TZone, Kunde.email, Kunde.Bemerkung,
Kunde.LastUpdate, Kunde.Geloescht
from Kunde, AP, Kontakt
where (Geloescht=0 or Geloescht IS NULL)
and Kunde.K_ID=AP.K_ID and AP.A_ID=Kontakt.A_ID
^^^^^^^^ now there are a where which on AP and Kontakt
order by Kunde.Bezeichnung

all works fine again!

Does anybody encountered the same problem?

 

Re:Interbase takes 100% CPU-time on joined query


This is the expected behavior.  When you add tables to a FROM clause with no
join criteria, you are forming what is known as a cartesian product.  Each
row in the first table is joined to every row in the second table, etc.
Because the size of the working sets expand exponentially, you are eating up
all resources.  Obviously you almost never want to do this on purpose.

Your third query (w/join criteria) is the right way to go.

V/R
Russell L. Smith

Quote
Stefan Westner wrote in message <368b7295.2690...@forums.borland.com>...
>the Interbase-Server takes 100% CPU-Time and I have to reboot the
>NT-machine.

Re:Interbase takes 100% CPU-time on joined query


In your second query, every row from Kunde is joined to every row from AP
and every row from Kontakt. Depending on the size of your datasets, this can
easily surpass the capacity of any database server. You need a Where clause
that specifies the proper relation between your tables, the way you do it in
your third query.

-- stephan dot patterson at cognicase dot ca

Quote
>I encountered a strange problem using Delhpi 4.02 and Interbase 5.1:

Re:Interbase takes 100% CPU-time on joined query


Hi,

It is not a problem!

Your last SQL works because there is an implied join on AP and Kunde.
Without that, you ask for an Cartesian product, which you probably don't
want.

If table1 has 10 records and table2 has 20 records
Select * from table1, table2
will give you 200 rows!!! 10 x 20 = a Cartesian product.
With 3 tables there is even more rows!!!
For instance 1000 x 20000 x 230 = A LOT!!! You probably want 230 at most :-)
That is the reason for the Pc to hang, he is working very hard to collect
all that rows!

Select * from table1, table2 where table1.Key = table2.Key
Gives at most 10 rows, in the first example of 10 and 20 rows
respectively...

You can also use OUTER JOINS (maybe NOT in Interbase, I use MSSQL), which
can give you 20 rows with 10 rows filled with NULL values for the missing
rows of one of the tables. Handy for determining missing data in one of the
tables.

Advice: READ THE F#$#% MANUAL!     :-)

Fons Claessen
ajcla...@wxs.nl

Quote
Stefan Westner wrote in message <368b7295.2690...@forums.borland.com>...
>Hello,

>I encountered a strange problem using Delhpi 4.02 and Interbase 5.1:

>I have 3 tables: Kunde, AP and Kontakt. AP has an FK to Kunde and
>Kontakt has a FK to AP.

>I do the following select now:

>select Kunde.K_ID, Kunde.KA_ID, Kunde.Bezeichnung,
>Kunde.Strasse, Kunde.Postfach, Kunde.PLZ, Kunde.PPLZ, Kunde.Ort,
>Kunde.L_ID, Kunde.InVG, Kunde.EW, Kunde.Telefon, Kunde.Telefax,
>Kunde.TZone, Kunde.email, Kunde.Bemerkung,
>Kunde.LastUpdate, Kunde.Geloescht
>from Kunde
>where (Geloescht=0 or Geloescht IS NULL)
>order by Kunde.Bezeichnung

>All works fine.

>if I change the select to

>select Kunde.K_ID, Kunde.KA_ID, Kunde.Bezeichnung,
>Kunde.Strasse, Kunde.Postfach, Kunde.PLZ, Kunde.PPLZ, Kunde.Ort,
>Kunde.L_ID, Kunde.InVG, Kunde.EW, Kunde.Telefon, Kunde.Telefax,
>Kunde.TZone, Kunde.email, Kunde.Bemerkung,
>Kunde.LastUpdate, Kunde.Geloescht
>from Kunde, AP, Kontakt
>                ^^^^ AP & Kontakt are in the query without no fields
>where (Geloescht=0 or Geloescht IS NULL)
>order by Kunde.Bezeichnung

>the Interbase-Server takes 100% CPU-Time and I have to reboot the
>NT-machine.

>If I change the query now to

>select Kunde.K_ID, Kunde.KA_ID, Kunde.Bezeichnung,
>Kunde.Strasse, Kunde.Postfach, Kunde.PLZ, Kunde.PPLZ, Kunde.Ort,
>Kunde.L_ID, Kunde.InVG, Kunde.EW, Kunde.Telefon, Kunde.Telefax,
>Kunde.TZone, Kunde.email, Kunde.Bemerkung,
>Kunde.LastUpdate, Kunde.Geloescht
>from Kunde, AP, Kontakt
>where (Geloescht=0 or Geloescht IS NULL)
>and Kunde.K_ID=AP.K_ID and AP.A_ID=Kontakt.A_ID
>^^^^^^^^ now there are a where which on AP and Kontakt
>order by Kunde.Bezeichnung

>all works fine again!

>Does anybody encountered the same problem?

Re:Interbase takes 100% CPU-time on joined query


Good point, and easy to demonstrate (although Stefan does have an ORDER BY
clause).  The DBMS does not need any intermediate result sets in this
case...

V/R
Russell L. Smith

Quote
Alexander Kozlov wrote in message <36919176.B08F5...@zdnetmail.com>...
>without ORDER clause it's not resourse consuming for almost all DBMS
>implementations.

Re:Interbase takes 100% CPU-time on joined query


Only a little remark:
without ORDER clause it's not resourse consuming for almost all DBMS
implementations.
Quote
"Russell L. Smith" wrote:
> This is the expected behavior.  When you add tables to a FROM clause with no
> join criteria, you are forming what is known as a cartesian product.  Each
> row in the first table is joined to every row in the second table, etc.
> Because the size of the working sets expand exponentially, you are eating up
> all resources.  Obviously you almost never want to do this on purpose.

> Your third query (w/join criteria) is the right way to go.

> V/R
> Russell L. Smith

> Stefan Westner wrote in message <368b7295.2690...@forums.borland.com>...
> >the Interbase-Server takes 100% CPU-Time and I have to reboot the
> >NT-machine.

Other Threads