• Post Reply Bookmark Topic Watch Topic
  • New Topic

Long runtime with PS and LIKE keyword  RSS feed

 
Allen Bandela
Ranch Hand
Posts: 128
Eclipse IDE MS IE Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi all,

I have a query that runs using a PreparedStatement (PS). It searches for a customer by lastname. However, it runs for more than 3 minutes. I changed the sql into a Statement and it runs in 0.3 seconds.

I dont have problems with other queries that use PS's. But this query uses the LIKE keyword such as
select customerid from customers where lastname LIKE UPPER(?);
PS.setString(1, value + "%");

So, the question is : Does anyone know why the long runtime with bind variables and the LIKE keyword.

Thank you
 
Peter Johnson
author
Bartender
Posts: 5856
7
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You did not state how you measured the time so I am assuming you timed it from the Java app. Have you used used database tools to determine how long this particular query ran within the database? That would at least point to (or eliminate) the database as the issue.

Also, what database (and version) are you using and which JDBC driver (and version)?
 
Tim Holloway
Bartender
Posts: 18531
61
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It's very unusual for an ad-hoc statement to be quicker than a prepared statement. Primarily because one of the favorite performance tricks a database backend will use is to use the character-for-character text of SQL requests as a key to retrieve previously-compiled SQL code, if any is available. While the overhead for most SQL compiles is minor, the frequency of statement use makes it add up. And for really advanced DBMS', the DBA and/or AI logic in the DBMS may have tuned the compiled code for even greater performance benefits.

The LIKE function is basically a limited form of Regular Expression. RE's have been studied for probably 30 years or more and long since reduced to a fine-tuned science. But searching using a RE or other imprecise characteristics is almost always going to be slower, especially compared to exact-match key searches. In fact, in some cases such as IBM mainframe disk subsystems, an ordered key search can be offloaded onto the disk hardware itself.

I generally recommend using prepared statements unless there's a really strong reason not to. Aside from the optimization help, it reduces vulnerability to SQL Injection attacks.
 
Don't get me started about those stupid light bulbs.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!