| Subject | Re: [firebird-support] Re: MAKE 'EXECUTE STATEMENT' USE INDEX |
|---|---|
| Author | Mr. John |
| Post date | 2015-08-20T07:31:07Z |
in this case I can't replace FOR EXECUTE STATEMENT 'SELECT.. ' with FOR SELECT.. because is much slower
thanks
From: "Dmitry Yemanov dimitr@... [firebird-support]" <firebird-support@yahoogroups.com>
To: firebird-support@yahoogroups.com
Sent: Wednesday, August 19, 2015 4:18 PM
Subject: [firebird-support] Re: MAKE 'EXECUTE STATEMENT' USE INDEX
19.08.2015 14:29, 'Mr. John' wrote:
because all three index segments are used for the retrieval.
because (FIEL3=:F3 OR :F3 IS NULL) prevents the third index segment from
being used, so only first two index segments are used for the retrieval.
This is absolutely expected.
Dmitry
> This time,adding condition this way WHERE FIEL1=:F1 AND FIEL2=:F2 AND
> * (FIEL3=:F3 OR **:F3 IS NULL) * INTO .. DO ..
>
> is causing query to be much slower ...
>
> this WHERE FIEL1=:F1 AND FIEL2=:F2 AND * (FIEL3=:F3 **) * INTO .. DO ..
> takes *0.563s*
because all three index segments are used for the retrieval.
> but this takes *29.141s*
> WHERE FIEL1=:F1 AND FIEL2=:F2 AND * (FIEL3=:F3 OR **:F3 IS NULL) * INTO
> .. DO ..
because (FIEL3=:F3 OR :F3 IS NULL) prevents the third index segment from
being used, so only first two index segments are used for the retrieval.
This is absolutely expected.
Dmitry