Subject Re: [firebird-support] Re: MAKE 'EXECUTE STATEMENT' USE INDEX
Author Mr. John
Post date 2015-08-19T11:29:26Z
ok,thanks

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

82655 fetches, 1088 marks, 1584 reads, 50 writes.
197 inserts, 98 updates, 148 deletes, 19166 index, 66 seq.
Delta memory: 11536 bytes.
Total execution time: 0.563s




but this  takes 29.141s
WHERE FIEL1=:F1 AND FIEL2=:F2 AND    (FIEL3=:F3 OR :F3 IS NULL)     INTO .. DO ..

1700290 fetches, 1065 marks, 3694 reads, 77 writes.
198 inserts, 99 updates, 149 deletes, 29422 index, 68 seq.
Delta memory: -71656 bytes.
Total execution time: 29.141s
Script execution finished.




in both cases  :F3  is NOT NULL

thanks



From: "Dmitry Yemanov dimitr@... [firebird-support]" <firebird-support@yahoogroups.com>
To: firebird-support@yahoogroups.com
Sent: Wednesday, August 19, 2015 11:51 AM
Subject: [firebird-support] Re: MAKE 'EXECUTE STATEMENT' USE INDEX

 
19.08.2015 10:26, 'Mr. John' wrote:

> now index is used if I call
> SELECT * FROM MYPROC(...)
> but I use it in other procedures,no join on it just simply SELECT ..
> FROM MYPROC(...) in this case I can see in the plan :
> ...(MYPROC NATURAL)...

That' OK. In complex plans, (MY_PROC NATURAL) just means a select from a
procedure, it says nothing about actual plans inside MY_PROC.

Dmitry