Win a copy of The Business Blockchain this week in the Cloud forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Slow running query that uses SUBSTR

 
new develoepr
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi - Using DB2 as my DB to execute this query - it execeutes properly when run from command editor. However takes a long time when done drom Java using prepared statement, anyway this can be optimized?

select catpb.partnumber from catentry catpb,catentry catib, catentrel catrel where substr(catib.partnumber,1,8) = ? and catib.catenttype_id = 'ItemBean' and catib.catentry_id = catrel.catentry_id_child and catpb.catentry_id=catrel.catentry_id_parent with
ur


Looks like it is the substr causing the issue. Any better way to do this?

Both tables have around 10M records each
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
May be caused by a lot of things. Besides, the query seems to be incomplete.

Show us how the PreparedStatement is created, parametrized and executed, exact text of the SQL query you run from the other tool (you're not using binds there, are you?), datatypes of all involved columns (or better description of all involved tables) and any indexes these columns are part of (or better all indexes on involved tables).
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic