It's not a secret anymore!*
The moose likes JDBC and the fly likes Delayed result with 'not like' clause in Where clause Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Delayed result with Watch "Delayed result with New topic
Author

Delayed result with 'not like' clause in Where clause

Sajee Joseph
Ranch Hand

Joined: Jan 17, 2001
Posts: 200
Hello all,
I am new to database.I have a problem with my sql query. It takes 20 secs to execute (this return 127 rows). My query has a 'not like "%xyz%" ' clause in its where clause.
When i change this 'not like' clause to 'like' clause, it returns me 137, but does this just in 1 second.
The table has around 50000 records in it.
Can someone tell me how to tune my original query to execute faster?? Has anyone experienced any problems with 'not like' clause.??
Regards,
Sajee
SJ Adnams
Ranch Hand

Joined: Sep 28, 2001
Posts: 925
loads of things you can try.
be a bit more specific about your application, if your doing general word searching take a look at intermedia text searching.
If the 'not like' clause is always the same, then a function based index is the way to go.
50,000 isn't that big a table. how much ram do you have, sga size etc?
maybe ask in the www.dbasupport.com forums also.
 
Don't get me started about those stupid light bulbs.
 
subject: Delayed result with 'not like' clause in Where clause
 
Similar Threads
How to use IN Clause in HQL
Not getting managed Entity as query result
Data truncation while inserting.
Database search using hibernate
Dynamic SQL Injection Prevention.