| Subject | RE: [firebird-support] select fieldA >= xxx is very very slow on Big table ? |
|---|---|
| Author | Leyne, Sean |
| Post date | 2010-12-19T22:57:40Z |
Stephane,
Accordingly, the engine prepares a full result set, then only returns the first row to the client.
If you are simply looking to confirm that *any* row exists, then the following SQL is more appropriate/faster to use:
SELECT 1 From RDB$Database where EXISTS(Select 1 from TABLEA where FIELDA >= xxx)
Sean
> why this simple sentence :Because the FIRST syntax is applied to the resulting set handler it is not incorporated into the core engine itself (unlike the EXISTS predicate.
>
> Select
> First 1 skip 0 ID
> from
> TABLEA
> where
> FIELDA >= xxx
> PLAN (TABLEA INDEX (FIELDA_IDX))
>
> is very slow on big table (> 20 000 000 rows) ... it's take more than 10 seconds
> to return :(
Accordingly, the engine prepares a full result set, then only returns the first row to the client.
If you are simply looking to confirm that *any* row exists, then the following SQL is more appropriate/faster to use:
SELECT 1 From RDB$Database where EXISTS(Select 1 from TABLEA where FIELDA >= xxx)
Sean