Solved

SQL query performance issue

Posted on 2014-09-15
10
322 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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

839 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