Hi,

At March 27, 2004, 04:34, almaier wrote:

> (rehash from firebird-devel)

> The following query is very slow and ultimately fails due to lack of
> disk space. The temp file grows to more than 2GB.
> The relevant metadata is below. TABLE1 has ca. 1000 records, TABLE2
> ca. 150000.
> I am a bit surprised about this because postgres has no problems
> with this query and provides the result in seconds.

> Alois


> Configuration:
> Firebird 1.5 Superserver Version: WI-V1.5.0.4306
> OS: Win98

> Query Plan:
> PLAN SORT (JOIN (TABLE1 NATURAL,TABLE2 NATURAL))

> Query:
> SELECT "TABLE1"."FIELD1", "TABLE1"."N2", "TABLE2"."FIELD2",
> "TABLE2"."FIELD3", SUM( "TABLE2"."FIELD4" ), COUNT( * ) FROM
> "TABLE1"
> "TABLE1", "TABLE2" "TABLE2" GROUP BY "TABLE1"."FIELD1",
> "TABLE1"."N2",
> "TABLE2"."FIELD2", "TABLE2"."FIELD3"

What's the relation between TABLE1 and TABLE2? In this query, I'm not
seeing any joins. As Dmitry stated in Firebird-Devel, this would
result into a cartesian result before the GROUP BY clause is applied
to reduce the number of rows returned, so the temp file might grow up
to approximately 6GB.

> Statement failed, SQLCODE = -902

> I/O error for file "C:\WINDOWS\TEMP\fb_sort_admp3k"
> -Error while trying to write to file
> -Der Drucker hat kein Papier mehr.

> -sort error

This error indicates that it is running out of disk space.


--
Best regards,
Daniel Rail
Senior System Engineer
ACCRA Group Inc. (www.accra.ca)
ACCRA Med Software Inc. (www.filopto.com)