wasabi3689
asked on
how to optimise this query
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.
Anyone provides me recommendation for the following query to improve performance in general.
can you post the execution plan please
ASKER
here it is
plan.csv
plan.csv
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?
ASKER
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?
1. use "=", instead of "Like" operator
2. select specific columns instead using "select *"
what other you think I need to change to improve?
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)
ASKER
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??
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??
My prior code was in error due to alias conflicts:
Sorry for the confusion.
How long was your query taking to execute in reality?
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)
Sorry for the confusion.
How long was your query taking to execute in reality?
ASKER
Hi LordKnightshade,
Your query seems missing two records when I compare original one
(32 row(s) affected)
(30 row(s) affected)
Your query seems missing two records when I compare original one
(32 row(s) affected)
(30 row(s) affected)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I don't find Optimum execution from query plan I attached. How to read it? Where is it?
ASKER
hhess1,
your query has error
Msg 4104, Level 16, State 1, Line 15
The multi-part identifier "lgroup.group_id" could not be bound.
your query has error
Msg 4104, Level 16, State 1, Line 15
The multi-part identifier "lgroup.group_id" could not be bound.
ASKER
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
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
ASKER
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
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
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..#device s') 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
)
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..#device
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
)
ASKER
yes, dvc_params_v is view
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?
ASKER
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?
ASKER
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?
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.
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.
>>yes, it did provide me a missing index request from the plan
Review the index details and create them if they make sense.
Review the index details and create them if they make sense.
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.
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.
ASKER
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?
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?
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!]
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!]
ASKER
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?
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?
ASKER
good
ASKER
query.txt