Hi Set,

thank you very much.
The Select-Count version works perfectly for me as now I can easily
define how much records of each type should be returned.

The plan looks good, as it says, he is able to use indices for the
query. No natural scan.

Is it worth submitting my first wrong SQL as a bug?



am Donnerstag, 23. Juni 2005 um 09:07 schrieben Sie:

SET> Hello Bjoern!

SET> First of all, I'd say this to me sounds like a bug (and I think I've
SET> seen similar things on this list before). Secondly, using SELECT FIRST
SET> and ORDER BY in a subselect is something I would avoid if at all
SET> possible (I'm not certain whether I think Firebird should give you the
SET> result you want or whether it should report an error, but ignoring the
SET> IN clause doesn't sound right). Only tables with very few records with
SET> AENDERUNG and DATUM would give acceptable speed with your query if it
SET> worked. The reason being that the subselects would be executed for
SET> every record and the sorting done after having grabbed all the
SET> records. Hence, I would expect a table with 100 records of which 40
SET> were AENDERUNG and 10 DATUM to process 4980 records if things worked
SET> OK (the two records matching AENDERUNG doesn't have to check DATUM,
SET> hence 20 less lookups than retrieving the 50 records 100 times). Now,
SET> this is only my simplified understanding of Firebird which is
SET> approximately as correct as 3-year-old kids' understanding of the
SET> ocean to be big, but it is good enough for warning bells to sound when
SET> seeing queries like yours.

SET> Possible rewrites of your query include

SET> SELECT * FROM DBINFO I
SET> WHERE
SET> (I."Label" = 'AENDERUNG' AND
SET> NOT EXISTS(SELECT * FROM DBINFO I1
SET> JOIN DBINFO I3 ON I1."Label" = I3."Label"
SET> WHERE I1."Label" = I."Label"
SET>   AND I1."CreatedAt" > I."CreatedAt"
SET>   AND I3."CreatedAt" > I1."CreatedAt"))
SET> OR
SET> (I."Label" = 'DATUM' AND
SET> NOT EXISTS(SELECT * FROM DBINFO I2
SET> JOIN DBINFO I4 ON I2."Label" = I4."Label"
SET> WHERE I2."Label" = I."Label"
SET>   AND I2."CreatedAt" > I."CreatedAt"
SET>   AND I4."CreatedAt" > I2."CreatedAt"))

SET> (kids' count: subselects 40*2 + 10*2 - 2 = 98 + 100 checks on "Label"
SET> = 198. Far better than 4980!)

SET> or you could possibly use "subselect count" (I've never tried this, so
SET> I'm not 100% certain it will work). This will be slower than the above
SET> and to a large extent suffer the same speed drawbacks as your original
SET> query (wow, the ocean IS really big):

SET> SELECT * FROM DBINFO I
SET> WHERE
SET> (I."Label" = 'AENDERUNG' AND
SET> (SELECT count(*) FROM DBINFO I1
SET> WHERE I1."Label" = I."Label"
SET>   AND I1."CreatedAt" > I."CreatedAt") < 2)
SET> OR
SET> (I."Label" = 'DATUM' AND
SET> (SELECT count(*) FROM DBINFO I2
SET> WHERE I2."Label" = I."Label"
SET>   AND I2."CreatedAt" > I."CreatedAt") < 2)

SET> HTH,
SET> Set

SET> --- In firebird-support@yahoogroups.com, Bjoern Reimer wrote:

>>
>>   The following query gives me too much records.
>>   I'd expect four records.
>>
>> SELECT * FROM DBINFO I
>> WHERE
>> I."Id" in (SELECT FIRST 2 I1."Id" FROM DBINFO I1
>> WHERE I1."Label" = 'AENDERUNG' ORDER BY I1."CreatedAt" DESC)
>> OR
>> I."Id" in (SELECT FIRST 2 I2."Id" FROM DBINFO I2
>> WHERE I2."Label" = 'DATUM' ORDER BY I2."CreatedAt" DESC)
>>
>>   What I get is
>>  
>> SELECT * FROM DBINFO I
>> WHERE I."Label" = 'AENDERUNG' OR  I."Label" = 'DATUM'
>>
>>   Where is my mistake?

Bjoern

--
SOFTbaer --- reimer@... --- www.softbaer.de