DataNucleus JIRA is now in read-only mode. Raise any new issues in GitHub against the plugin that it applies to. DataNucleus JIRA will remain for the foreseeable future but will eventually be discontinued
Issue Details (XML | Word | Printable)

Type: Improvement Improvement
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Andy Jefferson
Reporter: Yang ZHONG
Votes: 0
Watchers: 0

If you were logged in you would be able to see more operations.
DataNucleus Store RDBMS

"startsWith" should have been compiled as "like" instead of "locate" in order for big performance gain

Created: 12/Aug/10 12:34 AM   Updated: 20/Oct/10 09:30 AM   Resolved: 12/Sep/10 11:19 AM
Component/s: Queries
Affects Version/s: 2.0.4
Fix Version/s: 2.2.0.m2

Environment: Java 5, Linux

Datastore: IBM DB2
Severity: Production

 Description  « Hide
Currently, "field.startsWith( 'beginningWith')" is compiled as

LOCATE(CAST(<'beginningWith'> AS VARCHAR(32672)),THIS.field) = <1>

which scans the whole table!

We work around that by using "field.matches( 'beginningWith.*')" instead, which is currently compiled as

THIS.field LIKE <'beginningWith%'> ESCAPE '\'

"field" can be indexed, and the performance gain is big!

Can "startsWith" also be compiled as "like" please?

Erik Bengtson added a comment - 12/Sep/10 11:19 AM
fixed in [NUCRDBMS-364]

Erik Bengtson made changes - 12/Sep/10 11:19 AM
Field Original Value New Value
Status Open [ 1 ] Resolved [ 5 ]
Assignee Andy Jefferson [ andy ]
Fix Version/s 2.2.0.m2 [ 11023 ]
Resolution Fixed [ 1 ]
Andy Jefferson made changes - 20/Oct/10 09:30 AM
Status Resolved [ 5 ] Closed [ 6 ]