aspose file tools*
The moose likes JDBC and the fly likes Query giving slow performance. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Query giving slow performance." Watch "Query giving slow performance." New topic
Author

Query giving slow performance.

lokesh pattajoshi
Ranch Hand

Joined: Jul 29, 2009
Posts: 130
hi everybody i am using this(below) SQL query for searching some data but as i have used some joins and there are huge number of records it is giving me performance problem so can any body help me how can i optimize this query..every response is highly appreciated..

if i am searching a then this will be the query

select DISTINCT p.PolicyID "Policy ID", p.CustomerRef "Customer Reference", ct.Cover "Cover", p.Start_Date "Start Date", p.End_Date "End Date",p.No_of_Months "Term", p.DateCancelled "Cancelled Date"
from USER p inner join USER_TYPES ct on p.Cover = ct.CoverID, USER_AGENT sa
where sa.AgentID= p.AgentID and sa.BASE_CODE in ( 'ABC001' ) and ( p.PolicyID RLIKE '[[:<:]]a[[:>:]]' or p.Title RLIKE '[[:<:]]a[[:>:]]' or p.CustomerRef RLIKE '[[:<:]]a[[:>:]]' or p.Start_Date RLIKE '[[:<:]]a[[:>:]]' or p.End_Date RLIKE '[[:<:]]a[[:>:]]' order by 'Policy ID' limit ?,?

Thanks in Advnce
Fatih Keles
Ranch Hand

Joined: Sep 01, 2005
Posts: 182
I guess this is a mysql query. A few things at first place:
1) rlike is synonym for regex, if there is a way of replacing it replace it.
2) why do you use regex string search on date columns? (refer to number 1)
3) use explain utility for better understanding and optimization of your query. Yo can refer to mysql manual.

Regards,

Fatih.
lokesh pattajoshi
Ranch Hand

Joined: Jul 29, 2009
Posts: 130
Thanks a lot Fatih.
lokesh pattajoshi
Ranch Hand

Joined: Jul 29, 2009
Posts: 130
2) why do you use regex string search on date columns? (refer to number 1)
thanks fatih for your replay but i did not understood this(above) line ..can i use other things to search instead of string please help.
Fatih Keles
Ranch Hand

Joined: Sep 01, 2005
Posts: 182
p.Start_Date RLIKE '[[:<:]]a[[:>:]]'

what is the above regex searching for? what does [[:<:]]a[[:>:]] regex pattern stand for? If you can explain it literally I am sure the equivalent non-regex expressing can be written with either date or string functions.

Regards,

Fatih.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Query giving slow performance.