SQL query performance issue

Hi Experts,

The attached select statement is taking long time to return output result. Can you please give me some tips to speed up query execution.
Table.txt
SQL-Query.txt
sqldba2013Asked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
You need to cluster the tables on the date column(s):

TX_TASK: ( TECH_MODIFICATION_DATE )

TIMESHEET_UPDATE_HOURS: ( Actual_Date_Entered, Actual_Time_Entered )

For MySQL, you'll need to add a column(s) to make every key value unique, and NULLs are not allowed in the clustering key (really dopey restrictions but you're stuck with them, at least until they upgrade their db engine to the 21st century).
0
 
Brian CroweDatabase AdministratorCommented:
This is going to be strongly dependent on the size of the table since the STR_TO_DATE function is going to require a full table scan to evaluate each record.
0
 
sqldba2013Author Commented:
Thanks. Yes, you're correct..its doing full table scan.

Pls advise how to minimize the query execution time or how to avoid full table scan.

Table Size:
TX_TASK : 88897 rows
TIMESHEET_UPDATE_HOURS : 346828

(Select distinct id from TX_TASK TASKS where STR_TO_DATE(TASKS.TECH_MODIFICATION_DATE,'%Y-%m-%d %H:%i:%s')>STR_TO_DATE('2014-09-15 08:00:02','%Y-%m-%d %H:%i:%s')) UNION (Select distinct task_id from Reflection.TIMESHEET_UPDATE_HOURS where STR_TO_DATE(CONCAT(ifnull(Actual_Date_Entered,'1901-01-01'),CONCAT(' ',ifnull(Actual_Time_Entered,'00:00:00'))),'%Y-%m-%d %H:%i:%s')>STR_TO_DATE('2014-09-15 08:00:02','%Y-%m-%d %H:%i:%s'))

Open in new window

0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Brian CroweConnect With a Mentor Database AdministratorCommented:
Ideally you would change the datatype of the "date" fields to actual dates and then index those columns.  If this is possible you will see dramatic increases in performance.
0
 
sqldba2013Author Commented:
I have changed date fields data type from varchar to datetime. Pls review the attached updated table structure and advise on which columns indexes are required (based on given query)

NOTE: I can't change ID column data type from varchar to int because this columns contains combination of number+string (data).
Table.txt
0
 
sqldba2013Author Commented:
1. Do I need to create cluster index(s) on above table columns?

2. at least until they upgrade their db engine to the 21st century
I am not clear on above point, Can you give me more explanation on this?

3. for above table (s), index usage is showing very high ?
how to reduce index usage?

4. In MS SQL, we are using with(nolock) to avoid object locking. Can we use same feature in MySQL?

5. Can you pls review above select code and let me know if any changes are required to speed up execution time.
0
 
Brian CroweDatabase AdministratorCommented:
I don't understand why high index usage is a bad thing considering the alternative.

MySQL uses the same syntax for setting isolation level.
http://itecsoftware.com/with-nolock-table-hint-equivalent-for-mysql
0
 
Scott PletcherSenior DBACommented:
1. You very likely need to change it. MySQL will automatically create one, but it won't be based on your date column.

2. That was just a bit of a dig against MySQL for not allowing a clus index with duplicate values (and de-duping them itself).  You can just ignore that.

3. High index usage is normal.  The key is whether Seeks or Scans of the index are being done.

4. Sorry, I don't know that.

5. Change the clustered indexes.  Until you do that, any other changes are a waste of time.
0
 
sqldba2013Author Commented:
Thanks Brian Crowe & ScottPletcher for your help on this issue.

I got some improvements using above tips. I'm marking this case as closed.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.