Re: [ADMIN] SQL-Statement

-----Original Message-----
From: Godshall Michael
Sent: Thursday, November 13, 2003 11:42 AM
To: 'Markus Espenhain'
Subject: RE: [ADMIN] SQL-Statement

How many records are their total in Daten, Monatsbeitrag? Maybe 99% of the
rows in the table have Daten.ewmanfrdat set to NULL. With all the
conditions joined using an AND if one of those fields doesn't match
postgresql wouldn't need to look at the rest of the fields before going on
to the next row. That would cause the query to run much faster.

-----Original Message-----
From: Markus Espenhain [mailto:espenhain(at)etes(dot)de]
Sent: Thursday, November 13, 2003 11:23 AM
To: Postgres
Subject: [ADMIN] SQL-Statement

Hello,

sorry for my previos mail.
This was a mistake...

I have an problem with the following statement.
This one works fine and give me the results in just a few seconds.

SELECT Monatsbeitrag.lfdid, Daten.Kundennummer FROM Daten, Monatsbeitrag
WHERE ((((Daten.Vetrag widerufen = 2 ) AND NOT((Daten.ewmanfrdat IS NULL ) )
) AND (Monatsbeitrag.ID = Daten.Kundennummer ) ) AND (((Monatsbeitrag.Monat
+ 14 ) = '2003-10-27 00:00:00'::timestamp ) AND (Monatsbeitrag.Bezahlt = 2
) ) ) ORDER BY Daten.Name;

When I edit the statement and delete the "NOT" before "Daten.ewmanfrdat IS
NULL" the Query works over 15 Minutes.

SELECT Monatsbeitrag.lfdid, Daten.Kundennummer FROM Daten, Monatsbeitrag
WHERE ((((Daten.Vetrag widerufen = 2 ) AND ((Daten.ewmanfrdat IS NULL ) ) )
AND (Monatsbeitrag.ID = Daten.Kundennummer )
) AND (((Monatsbeitrag.Monat + 14 ) = '2003-10-27 00:00:00'::timestamp )
AND (Monatsbeitrag.Bezahlt = 2 ) ) ) ORDER BY Daten.Name;

OK, to select a lot of records it works a time, but for only 350 records?

Where is my problem?

Can anybody help me please.

Regards

Markus

--
Markus Espenhain Fon: +49 (7 11) 48 90 83 - 0
ETES - EDV-Systemhaus GbR Fax: +49 (7 11) 48 90 83 - 50
Libanonstrasse 58 A * D-70184 Stuttgart Web: http://www.etes.de

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster