Suggestions / comments required on a query

Question

Post reply

Suggestions / comments required on a query

T.Ashish

SSCarpal Tunnel

Points: 4248
More actions
May 30, 2013 at 5:04 am

#293937

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
))

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

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)

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 2

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

Phil Parkin SSC Guru Points: 246686 More actions · Answer 3

.. 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.