Subject Re: [firebird-support] Re: MAKE 'EXECUTE STATEMENT' USE INDEX
Author Mr. John
Post date 2015-08-20T07:19:28Z
not FIELD3 COULD BE NULL, VARIABLE :F3  AND THAT MEANS NO FILTER ON FIELD3


thanks

From: "liviuslivius@... [firebird-support]" <firebird-support@yahoogroups.com>
To: firebird-support@yahoogroups.com
Sent: Wednesday, August 19, 2015 8:00 PM
Subject: Re: [firebird-support] Re: MAKE 'EXECUTE STATEMENT' USE INDEX

 
Hi,
 
i do not know what should happen in your case when FIEL3 is null?
But you can try
 
WHERE FIEL1=:F1 AND FIEL2=:F2 AND   (FIEL3=COALESCE(:F3, FIEL3) )   INTO .. DO ..
 
regards,
Karol Bieniaszewski
 
Sent: Wednesday, August 19, 2015 1:29 PM
Subject: Re: [firebird-support] Re: MAKE 'EXECUTE STATEMENT' USE INDEX
 
 
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                           & nbsp;      )    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&nbs p;29.141s
WHERE FIEL1=:F1 AND FIEL2=:F2 AND   (FIEL3=:F3 OR :F3 IS NULL)    INTO .. DO ..

1700290 fetches, 1065 marks, 3694 reads, 77 w rites.
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