Issue Details (XML | Word | Printable)

Key: NUCRDBMS-440
Type: Improvement Improvement
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Andy Jefferson
Reporter: Yang ZHONG
Votes: 0
Watchers: 0
Operations

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 ]