How can I handle the time consuming SQL?

Question

We have a table with 6 million records, and then we have a SQL which need around 7 minutes to query the result. I think the SQL cannot be optimized any more.

The query time causes our weblogic to throw the max stuck thread exception.

Is there any recommendation for me to handle this problem ?

Following is the query, but it's hard for me to change it,

SELECT * FROM  table1 
WHERE trim(StudentID) IN ('354354','0') 
AND concat(concat(substr(table1.LogDate,7,10),'/'),substr(table1.LogDate,1,5)) 
       BETWEEN '2009/02/02' AND '2009/03/02' 
AND TerminalType='1' 
AND RecStatus='0' ORDER BY StudentID, LogDate DESC, LogTime

However, I know it's time consuming for using strings to compare dates, but someone wrote before I can not change the table structure...

LogDate was defined as a string, and the format is mm/dd/yyyy, so we need to substring and concat it than we can use between ... and ... I think it's hard to optimize here.

Answer 1

The odds are that this query is doing a full-file scan, because you're WHERE conditions are unlikely to be able to take advantage of any indexes.

Is LogDate a date field or a text field? If it's a date field, then don't do the substr's and concat's. Just say "LogDate between '2009-02-02' and '2009-02-03' or whatever the date range is. If it's defined as a text field you should seriously consider redefining it to a date field. (If your date really is text and is written mm/dd/yyyy then your ORDER BY ... LOGDATE DESC is not going to give useful results if the dates span more than one year.)

Is it necessary to do the trim on StudentID? It is far better to clean up your data before putting it in the database then to try to clean it up every time you retrieve it.

If LogDate is defined as a date and you can trim studentid on input, then create indexes on one or both fields and the query time should fall dramatically.

Or if you want a quick and dirty solution, create an index on "trim(studentid)".

If that doesn't help, give us more info about your table layouts and indexes.

Answer 2

SELECT * ... WHERE trim(StudentID) IN ('354354','0')

If this is normal construct, then you need a function based index. Because without it you force the DB server to perform full table scan.

As a rule of thumb, you should avoid as much as possible use of functions in the WHERE clause. The trim(StundentID), substr(table1.LogDate,7,10) prevent DB servers from using any index or applying any optimization to the query. Try to use the native data types as much as possible e.g. DATE instead of VARCHAR for the LogDate. StudentID should be also managed properly in the client software by e.g. triming the data before INSERT/UPDATE.