Thread: SQL-Statement

SQL-Statement

From
Markus Espenhain
Date:
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.ewmanfrdatIS NULL ) ) ) AND (Monatsbeitrag.ID = Daten.Kundennummer ) ) AND (((Monatsbeitrag.Monat + 14 ) =
'2003-10-2700: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.ewmanfrdatIS 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

Re: SQL-Statement

From
banghe
Date:
Hi,

Your query should be written in much simple way:

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

Your 2nd query has
Daten.ewmanfrdat IS NULL

So the result should is different and the cost for query will be
different too.

Bangh

Markus Espenhain wrote:

>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.ewmanfrdatIS NULL ) ) ) AND (Monatsbeitrag.ID = Daten.Kundennummer ) ) AND (((Monatsbeitrag.Monat + 14 ) =
'2003-10-2700: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.ewmanfrdatIS NULL ) ) ) AND (Monatsbeitrag.ID = Daten.Kundennummer ) 
>) AND (((Monatsbeitrag.Monat + 14 ) =  '2003-10-27 00:00:00'::timestamp ) AND (Monatsbeitrag.Bezahlt = 2 ) ) ) ORDER
BYDaten.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
>
>
>