Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL query performance issue

Posted on 2014-09-15
10
Medium Priority
?
338 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 1000 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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 70

Accepted Solution

by:
Scott Pletcher earned 1000 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 70

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 Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

670 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