Hi Sergio!

A table with 5000 records is normally very small (well, unless the table contains a lot of e.g. BLOB fields that contain videos or something). I normally work with tables of a few million records, but these databases still cannot be considered big databases.

However, when doing selects, I normally try to reduce the result set as much as possible. I don't expect Firebird to have any problem if I want to show 50000 records, but I would expect the users to have great problems finding what they're looking for if I give them that many. Seriously consider using the WHERE clause to limit the number of returned rows so that only one or a few records are returned (don't return records the user doesn't need). Of course there are exceptions when there is good reason to return many rows, but I at least want to know why before doing so.

Apart from the 'why' question above, I think a sorted unioned set of 10000 records sounds reasonable (I ran a small test on Firebird 1.5, returning one integer field from 40000 records (10000 + 30000 records, I think) that were unioned and sorted and it was pretty quick although it did use more than 0 seconds).

Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Sergio H. Gonzalez
Sent: 25. november 2008 15:17
To: firebird-support@yahoogroups.com
Subject: [firebird-support] union performance

Hello! Is there any practical limit to use union? I mean may I use union to mix
two tables with 5000 records each and order the result by some date field?

I'm testing it with the biggest tables I have at the moment, and it seems to be
quite fast... Of course, this select has no sence, it's just for a test...

select ID,descripcion from STOCK

union ALL

select id,razon_social from proveedores

order by 1

This are the statics FlameRobin reports, but unfortunatelly I don't know how to
interpret them...


Starting transaction...
Preparing query: select ID,descripcion from STOCK

union ALL

select id,razon_social from proveedores

order by 1


Prepare time: 00:00:00.
Field #01: STOCK.ID Alias:ID Type:INTEGER
Field #02: STOCK.DESCRIPCION Alias:DESCRIPCION Type:STRING(150)
PLAN (STOCK NATURAL)
PLAN (PROVEEDORES NATURAL)


Executing...
Done.
79055 fetches, 4 marks, 0 reads, 4 writes.
0 inserts, 0 updates, 0 deletes, 371 index, 37341 seq.
Delta memory: 9519800 bytes.
Execute time: 00:00:00.