Solved

SQL query performance issue

Posted on 2014-09-15
10
316 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
  • 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
 

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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 69

Accepted Solution

by:
ScottPletcher 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:ScottPletcher
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.‚Äč
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

708 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now