May 30, 2013 at 5:04 am
Hi all,
I have pasted a query below and I need your suggestions or comments on same. Is there any improvement possible in it?
Execution plan is attached.
SELECT
wl.work_location_id,
isnull(wld.name, wl.name) + ' (' + wl.code + ')' AS name
FROM
dbo.work_location wl(nolock)
LEFT JOIN site s(nolock) on wl.site_id = s.site_id
AND wl.buyer_code = s.buyer_code
LEFT JOIN dbo.work_location_locale wld(nolock) on wl.work_location_id = wld.work_location_id
AND wld.locale = NULL
WHERE
((wl.site_id IN (
SELECT
site_id
FROM
dbo.person_site ps(nolock)
WHERE
ps.person_id = 'z12080916195260579617a66'
)) OR EXISTS (
SELECT
'x'
FROM
dbo.person(nolock)
WHERE
(role_id = '5')
AND person_id = 'z12080916195260579617a66'
))
AND wl.buyer_code = 'FNMA'
AND wl.site_id = 'z12080916474848364992a66'
AND wl.active_flag = 1
AND (NOT EXISTS (
SELECT
1
FROM
dbo.company_config cc(nolock)
WHERE
cc.company_code = '1037'
AND cc.config_id IN (2002)
AND cc.enabled_flag = 1
) OR EXISTS (
SELECT
1
FROM
dbo.person_division_code pdc(nolock)
WHERE
pdc.person_id = 'z12080916195260579617a66'
AND pdc.division_code_id = s.division_code_id
))
May 30, 2013 at 5:12 am
For a start you better to do following two things:
1. Format your query into more readable form - use indent, otherwise code looks like noodles.
2. Remove NOLOCK hints (http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/16/1345.aspx)
May 30, 2013 at 8:31 pm
Except for the key lookup, nothing jumped out at me. You can try to eliminate that by including the columns needed to satisfy the query in the nonclustered index.
Nothing else lept out as a major issue.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 31, 2013 at 12:56 am
.. use indent, otherwise code looks like noodles.
:hehe:
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply