Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Suggestions / comments required on a query Expand / Collapse
Author
Message
Posted Thursday, May 30, 2013 5:04 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 12:05 AM
Points: 718, Visits: 542
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
))


  Post Attachments 
Query.txt (4 views, 1.24 KB)
plan.sqlplan (7 views, 75.19 KB)
Post #1458130
Posted Thursday, May 30, 2013 5:12 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 4, 2014 3:55 AM
Points: 2,836, Visits: 5,062
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1458133
Posted Thursday, May 30, 2013 8:31 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 7:32 PM
Points: 15,517, Visits: 27,895
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1458508
Posted Friday, May 31, 2013 12:56 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, July 21, 2014 11:49 PM
Points: 4,973, Visits: 11,660
.. use indent, otherwise code looks like noodles.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1458532
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse