Re: [firebird-support] MAKE 'EXECUTE STATEMENT' USE INDEX

2015-08-19 Thread 'Mr. John' mr_joh...@yahoo.com [firebird-support] 
Hi,thanks for your answer
I remove pCondition variable and use :F3 insead (:F3=NULL means no F3  field 
condition,else F3 condition)and I simply have
WHERE FIEL1=:F1 AND FIEL2=:F2 AND    (FIEL3=:F3 OR :F3 IS NULL)     INTO .. DO 
..

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)...
very strange,I have to fix it because this query takes about 20 min
thanks!





  From: "setysvar setys...@gmail.com [firebird-support]" 

 To: firebird-support@yahoogroups.com 
 Sent: Tuesday, August 18, 2015 8:03 PM
 Subject: Re: [firebird-support] MAKE 'EXECUTE STATEMENT' USE INDEX
   
 Den 18.08.2015 16:59, skrev 'Mr. John' mr_joh...@yahoo.com 
[firebird-support]:
  


 HI,in SP I have this query  
    FOR EXECUTE STATEMENT 'SELECT SUM(CANT)  FROM TABLE1 WHERE FIEL1='||:F1||' 
AND FIEL2='||:F2|| IIF(:pCondition=1,' AND FIEL3='||:F3,'')  INTO .. DO ..
     TABLE1 PK =FIELD1,FIELD2,FIELD3
  
   running the query gives a NATURAL PLAN 
  How to make it use the index? 
  Index is used if I change to:     FOR SELECT SUM(CANT)  FROM TABLE1 WHERE 
FIEL1=:F1 AND FIEL2=:F2 AND FIEL3=:F3 INTO .. DO ..
  but this way I can't use the IIF condition that I need 
  thanks! 
  
 Why would you need an IIF condition? What about
 
 FOR SELECT SUM(CANT)  FROM TABLE1 WHERE FIEL1=:F1 AND FIEL2=:F2 AND (FIEL3=:F3 
or cast(:pCondition as SmallInt) is distinct from 1) INTO .. DO ..
 
 That could at least use an index for FIEL1 and FIEL2 (if they are the first 
two fields in your PK, that index could be used). Now, in some cases the index 
may have poor selectivity so that it still chooses natural (you didn't say 
whether "FOR SELECT SUM(CANT)  FROM TABLE1 WHERE FIEL1=:F1 AND FIEL2=:F2 INTO 
.. DO .." uses an index or not). If so, the only way to use the index would be 
to use IF in your procedure and two separate queries, the index being used if 
you run the query comparing with FIEL3, but no index used when you run the 
query with only FIEL1 and FIEL2.
 
 I've seen Dmitry Yemanov answer a different question (that may or may not be 
similar to yours) with repeating the first part and that could possibly 
translate to:
 
 FOR SELECT SUM(CANT)  FROM TABLE1 WHERE (FIEL1=:F1 AND FIEL2=:F2 AND 
FIEL3=:F3) or
 (FIEL1=:F1 AND FIEL2=:F2 AND cast(:pCondition as SmallInt) is distinct from 1)
 
 I never quite understood why I saw a similar statement to this, but I assume 
it doesn't hurt trying if my first suggestion fails.
 
 Set
  #yiv3118594155 #yiv3118594155 -- #yiv3118594155ygrp-mkp {border:1px solid 
#d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv3118594155 
#yiv3118594155ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv3118594155 
#yiv3118594155ygrp-mkp #yiv3118594155hd 
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 
0;}#yiv3118594155 #yiv3118594155ygrp-mkp #yiv3118594155ads 
{margin-bottom:10px;}#yiv3118594155 #yiv3118594155ygrp-mkp .yiv3118594155ad 
{padding:0 0;}#yiv3118594155 #yiv3118594155ygrp-mkp .yiv3118594155ad p 
{margin:0;}#yiv3118594155 #yiv3118594155ygrp-mkp .yiv3118594155ad a 
{color:#ff;text-decoration:none;}#yiv3118594155 #yiv3118594155ygrp-sponsor 
#yiv3118594155ygrp-lc {font-family:Arial;}#yiv3118594155 
#yiv3118594155ygrp-sponsor #yiv3118594155ygrp-lc #yiv3118594155hd {margin:10px 
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv3118594155 
#yiv3118594155ygrp-sponsor #yiv3118594155ygrp-lc .yiv3118594155ad 
{margin-bottom:10px;padding:0 0;}#yiv3118594155 #yiv3118594155actions 
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv3118594155 
#yiv3118594155activity 
{background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv3118594155
 #yiv3118594155activity span {font-weight:700;}#yiv3118594155 
#yiv3118594155activity span:first-child 
{text-transform:uppercase;}#yiv3118594155 #yiv3118594155activity span a 
{color:#5085b6;text-decoration:none;}#yiv3118594155 #yiv3118594155activity span 
span {color:#ff7900;}#yiv3118594155 #yiv3118594155activity span 
.yiv3118594155underline {text-decoration:underline;}#yiv3118594155 
.yiv3118594155attach 
{clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 
0;width:400px;}#yiv3118594155 .yiv3118594155attach div a 
{text-decoration:none;}#yiv3118594155 .yiv3118594155attach img 
{border:none;padding-right:5px;}#yiv3118594155 .yiv3118594155attach label 
{display:block;margin-bottom:5px;}#yiv3118594155 .yiv3118594155attach label a 
{text-decoration:none;}#yiv3118594155 blockquote {margin:0 0 0 
4px;}#yiv3118594155 .yiv3118594155bold 
{font-family:Arial;font-size:13px;font-weight:700;}#yiv3118594155 
.yiv3118594155bold a {text-decoration:none;}#yiv3118594155 dd.yiv3118594155last 
p a {font-family:Verdana;font-weight:700;}#yiv3118594155

Re: [firebird-support] MAKE 'EXECUTE STATEMENT' USE INDEX

2015-08-18 Thread setysvar setys...@gmail.com [firebird-support] 
Den 18.08.2015 16:59, skrev 'Mr. John' mr_joh...@yahoo.com 
[firebird-support]:

HI,in SP I have this query

FOR EXECUTE STATEMENT 'SELECT SUM(CANT)  FROM TABLE1 WHERE 
FIEL1='||:F1||' AND FIEL2='||:F2|| IIF(:pCondition=1,' AND 
FIEL3='||:F3,'')  INTO .. DO ..

 TABLE1 PK =FIELD1,FIELD2,FIELD3

 running the query gives a NATURAL PLAN

How to make it use the index?

Index is used if I change to:
FOR SELECT SUM(CANT)  FROM TABLE1 WHERE FIEL1=:F1 AND FIEL2=:F2 
AND FIEL3=:F3 INTO .. DO ..

but this way I can't use the IIF condition that I need

thanks!


Why would you need an IIF condition? What about

FOR SELECT SUM(CANT)  FROM TABLE1 WHERE FIEL1=:F1 AND FIEL2=:F2 AND 
(FIEL3=:F3 or cast(:pCondition as SmallInt) is distinct from 1) INTO .. 
DO ..


That could at least use an index for FIEL1 and FIEL2 (if they are the 
first two fields in your PK, that index could be used). Now, in some 
cases the index may have poor selectivity so that it still chooses 
natural (you didn't say whether "FOR SELECT SUM(CANT)  FROM TABLE1 WHERE 
FIEL1=:F1 AND FIEL2=:F2 INTO .. DO .." uses an index or not). If so, the 
only way to use the index would be to use IF in your procedure and two 
separate queries, the index being used if you run the query comparing 
with FIEL3, but no index used when you run the query with only FIEL1 and 
FIEL2.


I've seen Dmitry Yemanov answer a different question (that may or may 
not be similar to yours) with repeating the first part and that could 
possibly translate to:


FOR SELECT SUM(CANT)  FROM TABLE1 WHERE (FIEL1=:F1 AND FIEL2=:F2 AND 
FIEL3=:F3) or
(FIEL1=:F1 AND FIEL2=:F2 AND cast(:pCondition as SmallInt) is distinct 
from 1)


I never quite understood why I saw a similar statement to this, but I 
assume it doesn't hurt trying if my first suggestion fails.


Set

[firebird-support] MAKE 'EXECUTE STATEMENT' USE INDEX

2015-08-18 Thread 'Mr. John' mr_joh...@yahoo.com [firebird-support] 
HI,in SP I have this query
  FOR EXECUTE STATEMENT 'SELECT SUM(CANT)  FROM TABLE1 WHERE FIEL1='||:F1||' 
AND FIEL2='||:F2|| IIF(:pCondition=1,' AND FIEL3='||:F3,'')  INTO .. DO ..
  TABLE1 PK =FIELD1,FIELD2,FIELD3

 running the query gives a NATURAL PLAN
How to make it use the index?
Index is used if I change to:    FOR SELECT SUM(CANT)  FROM TABLE1 WHERE 
FIEL1=:F1 AND FIEL2=:F2 AND FIEL3=:F3 INTO .. DO ..
but this way I can't use the IIF condition that I need
thanks!