Excess symbol% within LIKE

Navigation
0

We have a system in GX EV3 u2 generating Java and connecting to Postgresql 9.3. Overall the performance is acceptable, but at certain times we have bottlenecks and I noticed that they happen just when a query with LIKE is executed.

Looking further, I discovered that the problem is when GX generates code for instruction with LIKE, it fills the field size with characters% and then Postgres ends up generating a bad execution plan for the query. If you take (by hand) the excess of%, the query is fast.

Eg: in an address search field; "varchar address (120)", the generated code for a search becomes something like 

select endereco from cliente where endereco like 'rua do brasil%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%'

A query like this runs in 1.35 seconds, but removing the excess of%, leaving only 1, the same query runs in 0.33 sec.

How can I solve this problem? What can / should be done to get around or solve it for good? Is it a GX bug or is it being programmed that it should be improved?

    
asked by anonymous 04.05.2016 / 14:54

1 answer

2

When you have a LIKE, the string with '%' is added to the end because in previous versions of GX it was always necessary to complete the strings with whites, and to prevent the condition from being invalid, in this case it is completed with ' % '. This is no longer necessary in the new versions of GeneXus.

While the subject matter was reported in the SAC # 31034 , so far it has not been reported problem with this behavior. The SAC has been activated again to fix it in the next upgrades.

    
04.05.2016 / 23:26
how to do test with sql injection Send AJAX to a table