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

x
?
Solved

how to optimise this query

Posted on 2013-01-16
26
Medium Priority
?
166 Views
Last Modified: 2013-02-11
I have a sql query running too much time. I want to optimize it.
Anyone provides me recommendation for the following query to improve performance in general.
0
Comment
Question by:wasabi3689
[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
  • 15
  • 3
  • 3
  • +4
26 Comments
 

Author Comment

by:wasabi3689
ID: 38785006
here is the query
query.txt
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 38785038
can you post the execution plan please
0
 

Author Comment

by:wasabi3689
ID: 38785056
here it is
plan.csv
0
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
LVL 2

Expert Comment

by:jchevali
ID: 38785061
The query's probably fine, you just need to look at how your data is distributed and whether you have the appropriate indexes.  Have you tried profiling the query?  Do you see any scans?
0
 

Author Comment

by:wasabi3689
ID: 38785080
they query structure is fine? Do you think it will improve the query performance by changing following

1. use "=", instead of "Like" operator
2. select specific columns instead using "select *"

what other you think I need to change to improve?
0
 
LVL 3

Expert Comment

by:LordKnightshade
ID: 38785083
I would adjust the sub-query and introduce a join like thus:
SELECT DISTINCT devices.device_id, devices.dvc_type_id 
FROM devices d with (nolock)
	inner join dvc_params_v p with (nolock) ON d.device_id = p.device_id
WHERE p.param_value = '1'
	and p.dvc_params_v.param_name LIKE '%Load Profile Enabled%'
	and d.device_id in (SELECT device_id FROM lgroup_devices WHERE lgroup_id = 8)

Open in new window

0
 

Author Comment

by:wasabi3689
ID: 38785097
I provide you the query plan, how do you see or why you see no problem?

Also, in the query plan, I see EstimateExecution = 14785.92, is this good number?

Is EstimateExecution the indicator to tell the query performance? I mean if this number is big, that means not good, smaller is better??
0
 
LVL 3

Expert Comment

by:LordKnightshade
ID: 38785101
My prior code was in error due to alias conflicts:

SELECT DISTINCT d.device_id, d.dvc_type_id 
FROM devices d with (nolock)
	inner join dvc_params_v p with (nolock) ON d.device_id = p.device_id
WHERE p.param_value = '1'
	and p.param_name LIKE '%Load Profile Enabled%'
	and d.device_id in (SELECT device_id FROM lgroup_devices WHERE lgroup_id = 8)

Open in new window


Sorry for the confusion.

How long was your query taking to execute in reality?
0
 

Author Comment

by:wasabi3689
ID: 38785128
Hi LordKnightshade,

Your query seems missing two records when I compare original one

(32 row(s) affected)

(30 row(s) affected)
0
 
LVL 32

Accepted Solution

by:
Brendt Hess earned 1500 total points
ID: 38785154
There are two table scans in the query that should be addressed.  Personally, I would rewrite the query like this:

SELECT DISTINCT 
	d.device_id, 
	d.dvc_type_id 
FROM devices d
INNER JOIN lgroup_devices ld
	ON ld.device_id = d.device_id
INNER JOIN (
	SELECT DISTINCT
		device_id
	from dvc_params_v
	WHERE dvc_params_v.param_name = 'Load Profile Enabled' 
		and dvc_params_v.param_value  = '1'
	) parms
	ON parms.device_id = d.device_id
WHERE lgroup.group_id = 8

Open in new window

Optimum execution assumes indexes as follows:

Table lgroup
     Index by product_id and group_id in that order.

Table dvc_params_v  (Is this a view?)
     Index by param_name, param_value, device_id in that order
0
 

Author Comment

by:wasabi3689
ID: 38785176
I don't find Optimum execution from query plan I attached. How to read it? Where is it?
0
 

Author Comment

by:wasabi3689
ID: 38785181
hhess1,

your query has error

Msg 4104, Level 16, State 1, Line 15
The multi-part identifier "lgroup.group_id" could not be bound.
0
 

Author Comment

by:wasabi3689
ID: 38785187
LordKnightshade,

Your query takes 00:06:09 execution time

the original one takes 00:03:13

I clean up the buffer pool before each run test
0
 

Author Comment

by:wasabi3689
ID: 38785247
hhess,

I modify the query. The error is gone The query takes 00:02:39 to complete. Any thing you want to recommend to improve performance.

SELECT DISTINCT
      d.device_id,
      d.dvc_type_id
FROM devices d
INNER JOIN lgroup_devices ld
      ON ld.device_id = d.device_id
AND ld.lgroup_id = 8
INNER JOIN (
      SELECT DISTINCT
            device_id
      from dvc_params_v
      WHERE dvc_params_v.param_name = 'Load Profile Enabled'
            and dvc_params_v.param_value  = '1'
      ) parms
      ON parms.device_id = d.device_id
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 38785272
dvc_params_v is definitely a view.  Some of the tables don't appear to be clustered correctly, causing additional overhead as well.

Given the uncertainties, I would first try a "divide and conquer", then if we need to we can focus in on the specific problem query(ies).




IF OBJECT_ID('tempdb..#devices') IS NOT NULL
    DROP TABLE #devices
   
CREATE TABLE #devices (
    device_id int PRIMARY KEY CLUSTERED
)

PRINT 'Starting read of "lgroup_devices" @ ' + CONVERT(varchar(30), GETDATE(), 120)

INSERT INTO #devices
SELECT DISTINCT device_id
FROM dbo.lgroup_devices
WHERE lgroup_id = 8
ORDER BY 1

PRINT 'Starting read of "dvc_params_v" @ ' + CONVERT(varchar(30), GETDATE(), 120)

INSERT INTO #devices
SELECT DISTINCT dvc_params_v.device_id
FROM dbo.dvc_params_v
WHERE
    dvc_params_v.param_name LIKE 'Load Profile Enabled'
AND (dvc_params_v.param_value  = '1')
AND NOT EXISTS (
    SELECT 1
    FROM #devices temp_dvc
    WHERE
        temp_dvc.device_id = dvc_params_v.device_id
)

ALTER INDEX ALL ON #devices REBUILD



PRINT 'Starting read of "devices" @ ' + CONVERT(varchar(30), GETDATE(), 120)

SELECT dvc.device_id, dvc.dvc_type_id
FROM (
    SELECT DISTINCT device_id, dvc_type_id
    FROM dbo.devices
) AS dvc
WHERE EXISTS (
    SELECT 1
    FROM #devices temp_dvc    
    WHERE
        temp_dvc.device_id = dvc.device_id
)
0
 

Author Comment

by:wasabi3689
ID: 38785287
yes, dvc_params_v  is view
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 38785328
you have at least 2 table scans in your execution plan. ensure you create proper indexes. doesn't the execution provides hints for new indexes?
0
 

Author Comment

by:wasabi3689
ID: 38785364
yes, it did provide me a missing index request from the plan. Where to see tables scan from the plan? I don't see it?
0
 

Author Comment

by:wasabi3689
ID: 38785370
never mind. I see the table scan for device and dvc_type_param. The best practice in general, I should have index scan instead table scan, this is better, correct? Also, which is better, index scan and index seek in plan?
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 38786992
Open the plan.csv file you sent us. You will find at least 2 table scans.

Index Seek is the preferred one (you jump immediately to the required row). An Index scan is a bit better then a table scan but not as much as a seek.
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 38786995
>>yes, it did provide me a missing index request from the plan

Review the index details and create them if they make sense.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 38787601
I'm spoiled now: I'm so used to seeing the graphical query plan it's hard for me to find the row numbers in the text one now :-) .

The other potential issue is the "hash matches".  That's what my code attempted to address, but I guess you're not going to try my code to see how fast it is.
0
 

Author Comment

by:wasabi3689
ID: 38788836
Do you mean your code is ?

SELECT DISTINCT d.device_id, d.dvc_type_id
FROM devices d with (nolock)
      inner join dvc_params_v p with (nolock) ON d.device_id = p.device_id
WHERE p.param_value = '1'
      and p.param_name LIKE '%Load Profile Enabled%'
      and d.device_id in (SELECT device_id FROM lgroup_devices WHERE lgroup_id = 8)

I used it and tested it. The execution time is almost the same as original.

Why "hash matches are potential  issue. Can you explain more?
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 38788882
No, my code uses temp tables (#tables) as intermediate storage so that each SELECT can be run, and timed, separately.

Hash matches that can't be done in one pass entirely in RAM usually perform poorly, sometimes very poorly.

From Books Online, "Understanding Hash Joins ":
"Recursive hash joins or hash bailouts cause reduced performance in your server."
 [Recursive/bailout = not in RAM only]

[Hopefully that's enough to convince you: I dont won't to go further into the mechanics of hash join!]
0
 

Author Comment

by:wasabi3689
ID: 38795419
Hi,

Here another modified query for the same

SELECT dvc_params_v.device_id FROM dvc_params_v
WHERE dvc_params_v.param_name  =  'Load Profile Enabled'
  and (dvc_params_v.param_value  = '1')
  and dvc_params_v.device_id in (SELECT device_id FROM lgroup_devices WHERE lgroup_id = 8 )

It's the fastest. It's returned same counts of records.

Someone tells me why? Are they the same as original? if not why?
0
 

Author Closing Comment

by:wasabi3689
ID: 38879003
good
0

Featured Post

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
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.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

715 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