Solved

SQL query performance issue

Posted on 2014-09-15
10
328 Views
Last Modified: 2014-09-19
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
0
Comment
Question by:sqldba2013
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
10 Comments
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 40323541
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
 

Author Comment

by:sqldba2013
ID: 40323566
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
 
LVL 34

Assisted Solution

by:Brian Crowe
Brian Crowe earned 250 total points
ID: 40323574
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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

by:sqldba2013
ID: 40323606
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
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 250 total points
ID: 40323672
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
 

Author Comment

by:sqldba2013
ID: 40324542
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
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 40325596
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40325612
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
 

Author Closing Comment

by:sqldba2013
ID: 40332093
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

Featured Post

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question