Re: [firebird-support] Firebird optmizer cop-out

[Dimitry Sibiryakov s...@ibphoenix.com [firebird-support]]

05.12.2018 19:25, Rudi Feijó rudi.fe...@multidadosti.com.br [firebird-support] 
wrote:
> One last question, does the optimizer actively learns and auto-improves from 
> queries if we 
> set the PLAN manually?

   No. No AI inside.
   Optimizer is using database statistics, including index statistics and you 
must order 
their recalculation manually sometimes, there is no automatic housekeeping.


-- 
   WBR, SD.

ODP: ODP: [firebird-support] substring similar - "Invalid SIMILARTOpattern"

[Karol Bieniaszewski liviusliv...@poczta.onet.pl [firebird-support]]

Thank you once again.

Now it is much clear that sql standard have more functions and much useful.
I will wait for future improvements in Firebird. I stay longer with udfs for 
now.

Regards,
Karol Bieniaszewski

RE: [firebird-support] Firebird optmizer cop-out

[Rudi Feijó rudi.fe...@multidadosti.com.br [firebird-support]]

>Join order can be forced by using outer join.
>Using of indexes can be disabled by changing of simple field usage to an 
>expression.
>Preferences between ORDER and SORT plans (more commonly between "first row" 
>and "all 
>rows" strategies) can be changed by using FIRST/ROWS.
>Index statistics recalculation can be done in "a right moment"..



 

Thanks a lot for the information Dimitry.

One last question, does the optimizer actively learns and auto-improves from 
queries if we set the PLAN manually?

I had a slow query that I fiddled a bit the with PLAN, and the execution was 
faster. 
I noticed that after a while, without needing to set the PLAN explicitly, the 
optimizer began using a different and faster PLAN then it was previously using.

 

Re: [firebird-support] Informing the devs about optimizer issues / alternative plans

[liviuslivius liviusliv...@poczta.onet.pl [firebird-support]]

Hi,
first you can share your query and query plan for it, and what you have changed 
and how your modified query plan looks like.
Also check if you have problem with your query under newest snapshot as it 
contain recent optimizer fixes
Regards,Karol Bieniaszewski
null

Re: ODP: [firebird-support] substring similar - "Invalid SIMILAR TOpattern"

[Mark Rotteveel m...@lawinegevaar.nl [firebird-support]]

On 5-12-2018 18:00, Karol Bieniaszewski liviusliv...@poczta.onet.pl 
[firebird-support] wrote:
> Is this sql standard concept that i must do this in this crap way?

Yes, it is specified in SQL:2016, section 6.32 ", 
"".

> SELECT substring('ab11c' similar '[[:ALPHA:]]+#"[0-9]+#"[[:ALPHA:]]+' 
> escape '#') FROM RDB$DATABASE
> 
> Also strange that i must consume whole string by reg expression not only 
> part of it.

The syntax defines which part you want to obtain in terms of its 
position in the rest of the string.

> This can be as an option but as default it is strange for me.
> 
> Why not simply do:
> 
> SELECT substring('ab11c12bcd' similar '[0-9]+' itemNumber 1) FROM 
> RDB$DATABASE
> 
> Will simply return „11”
> 
> SELECT substring('ab11c22bcd' similar '[0-9]+' itemNumber 2) FROM 
> RDB$DATABASE
> 
> Will simply return 22

"Why not simply" because that is not the behavior defined by the 
standard for this specific function.

The SQL:2016 standard also has "" 
(SUBSTRING_REGEX) and a number of related functions like LIKE_REGEX, 
OCCURRENCES_REGEX, TRANSLATE_REGEX and POSITION_REGEX, which use the 
XQuery fn:matches() regex syntax.

This function allows you to specify the occurrence and capturing group 
to return (and some more things like start position in string), but 
Firebird doesn't provide this yet.

> Now i have 2 udf like this:
> 
> REG_MATCH
> 
> REG_MATCH_COUNT
> 
> and i supposed that i can replace it with built in one, but i see that 
> this is really terrible.
> 
> Above udfs i can use in this way
> 
> SELECT REG_MATCH(‘ab11c22bcd’, ‘[0-9]+’, 1) FROM RDB$DATABASE
> 
> Return 11
> 
> SELECT REG_MATCH(‘ab11c22bcd’, ‘[0-9]+’, 2) FROM RDB$DATABASE
> 
> Return 22
> 
> SELECT REG_MATCH_COUNT(‘ab11c22bcd’, ‘[0-9]+’) FROM RDB$DATABASE
> 
> Return 2
> 
> I can use it in the where clause:
> 
> SELECT * FROM MY_TABLE T WHERE REG_MATCH_COUNT(T.FIELD, ‘[0-9]+’)>2
> 
> Or
> 
> SELECT * FROM MY_TABLE T WHERE REG_MATCH(T.FIELD, ‘[0-9]+’, 1)=’11’

If I understand the SQL:2016 SUBSTRING_REGEX correctly, the equivalent 
for that would be SUBSTRING_REGEX('[0-9]+' IN T.FIELD) or - explicitly 
specifying the occurrence - SUBSTRING_REGEX('[0-9]+' IN T.FIELD 
OCCURRENCE 1).

The equivalent of that REG_MATCH_COUNT would be 
OCCURRENCES_REGEX('[0-9]+' IN T.FIELD)

Unfortunately we don't have that yet in Firebird.

Mark
-- 
Mark Rotteveel

Re: [firebird-support] Informing the devs about optimizer issues / alternative plans

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Rudi Feijó wrote:

> We have been trying to optimize one of our largest databases (firebird
> 3.0.4) , and in doing so, we managed to greatly improve the execution of one
> specific query by manually changing the PLAN.

>  

> I am wondering if the firebird team is interested in receiving detailed
> feedback of such cases for dev purposes, and if that’s the case, what is the
> procedure I should follow to share the database and the query.

1. Create an account at http://tracker.firebirdsql.org and create an
"improvement" ticket in the CORE category. Provide as much information
there as you can.

and

2. Subscribe to firebird-devel list - you can do it from this page:
https://www.firebirdsql.org/en/mailing-lists/ (scroll down) for
discussion of your ideas.

Helen


---
This email has been checked for viruses by AVG.
https://www.avg.com

ODP: [firebird-support] substring similar - "Invalid SIMILAR TOpattern"

[Karol Bieniaszewski liviusliv...@poczta.onet.pl [firebird-support]]

Thank you very much Mark for detailed info but i have more questions.

I am really curious. 
Is this sql standard concept that i must do this in this crap way? 
SELECT substring('ab11c' similar '[[:ALPHA:]]+#"[0-9]+#"[[:ALPHA:]]+' escape 
'#') FROM RDB$DATABASE
Also strange that i must consume whole string by reg expression not only part 
of it. 
This can be as an option but as default it is strange for me.


Why not simply do:
SELECT substring('ab11c12bcd' similar '[0-9]+' itemNumber 1) FROM RDB$DATABASE
Will simply return „11”

SELECT substring('ab11c22bcd' similar '[0-9]+' itemNumber 2) FROM RDB$DATABASE
Will simply return 22


Now i have 2 udf like this: 
REG_MATCH
REG_MATCH_COUNT

and i supposed that i can replace it with built in one, but i see that this is 
really terrible.
Above udfs i can use in this way

SELECT REG_MATCH(‘ab11c22bcd’, ‘[0-9]+’, 1) FROM RDB$DATABASE
Return 11
SELECT REG_MATCH(‘ab11c22bcd’, ‘[0-9]+’, 2) FROM RDB$DATABASE
Return 22

SELECT REG_MATCH_COUNT(‘ab11c22bcd’, ‘[0-9]+’) FROM RDB$DATABASE
Return 2

I can use it in the where clause:
SELECT * FROM MY_TABLE T WHERE REG_MATCH_COUNT(T.FIELD, ‘[0-9]+’)>2
Or
SELECT * FROM MY_TABLE T WHERE REG_MATCH(T.FIELD, ‘[0-9]+’, 1)=’11’

regards,
Karol Bieniaszewski

Re: [firebird-support] Firebird optmizer cop-out

[Dimitry Sibiryakov s...@ibphoenix.com [firebird-support]]

05.12.2018 12:37, Rudi Feijó rudi.fe...@multidadosti.com.br [firebird-support] 
wrote:
> Could anyone explain me further how the user can go about to effectively
> pick or influence the optimizers decision?

   Join order can be forced by using outer join.
   Using of indexes can be disabled by changing of simple field usage to an 
expression.
   Preferences between ORDER and SORT plans (more commonly between "first row" 
and "all 
rows" strategies) can be changed by using FIRST/ROWS.
   Index statistics recalculation can be done in "a right moment".

-- 
   WBR, SD.

[firebird-support] Firebird optmizer cop-out

[Rudi Feijó rudi.fe...@multidadosti.com.br [firebird-support]]

>From the docs about the optmizer, there is a small section :

 

Cop-Out

The user can pick or influence the optimizer’s decision. The Firebird
optimizer actually uses all three


Could anyone explain me further how the user can go about to effectively
pick or influence the optimizers decision?
By “pick” I assume it is to use an explicit manual PLAN statement, but what
about “influence”? I know some rdbs like oracle have a HINT statement, but I
haven’t found anything similar in firebird.
And also, can user action “permanently” influence the optimizer in positive
ways in future queries?

 

Atenciosamente,

Rudi Feijó


Multidados Informática Ltda.
*  (11) 2579-8789

*   rudi.fe...@multidadosti.com.br

*   www.multidadosti.com.br 
*   www.whatsappmailing.com.br

 

 



[Non-text portions of this message have been removed]

[firebird-support] Informing the devs about optimizer issues / alternative plans

[Rudi Feijó rudi.fe...@multidadosti.com.br [firebird-support]]

Hello.

 

We have been trying to optimize one of our largest databases (firebird
3.0.4) , and in doing so, we managed to greatly improve the execution of one
specific query by manually changing the PLAN.

 

I am wondering if the firebird team is interested in receiving detailed
feedback of such cases for dev purposes, and if that’s the case, what is the
procedure I should follow to share the database and the query.

 

 

Atenciosamente,

Rudi Feijó


Multidados Informática Ltda.
*  (11) 2579-8789

*   rudi.fe...@multidadosti.com.br

*   www.multidadosti.com.br 
*   www.whatsappmailing.com.br

 

 



[Non-text portions of this message have been removed]