Hi and welcome to SSC.

The first I did was run through a formatter so I could read it.

SELECT client_name

,client_id

,web_id

,CASE

WHEN active_hierarchy.trending_id IS NULL

THEN 1 -- web_id expired

ELSE CASE

WHEN mapping_is_historical = 1

THEN 2

ELSE 0

END

END is_historical

FROM (

SELECT DISTINCT client_name

,client_id

,web_id

,CASE

WHEN dbo.CurrentEffectiveDate(- 1) BETWEEN EFFECTIVE_DATE

AND EXPIRATION_DATE

THEN 0

ELSE 1

END mapping_is_historical

FROM (

SELECT category_id AS webster_category_id

,trending_id AS web_id

,map.EFFECTIVE_DATE

,map.EXPIRATION_DATE

FROM object_category_map map(NOLOCK)

,LEVEL l(NOLOCK)

WHERE category_index BETWEEN 50000

AND 60000

AND (

object_id = level_id

AND level_type = 'SYN'

OR object_id = level_id

AND level_type = 'CUS'

AND level_level = 1

OR object_id = level_id

AND level_type = 'ALT'

AND right(level_name, 3) <> '_AR'

)

) entity_client_map

,(

SELECT category_id AS webster_category_id

,CATEGORY_NAME AS client_name

,CATEGORY_DESCRIPTION AS client_id

FROM category NOLOCK

) client_defintion

WHERE client_defintion.webster_category_id = entity_client_map.webster_category_id

AND client_defintion.webster_category_id = entity_client_map.webster_category_id

) all_client_web_mapping_active_and_expired

LEFT JOIN (

SELECT trending_id

,CASE

WHEN hierarchy_id IS NULL

THEN syn.level_type

ELSE other_type.level_type

END AS level_type

FROM LEVEL syn(NOLOCK)

LEFT JOIN (

SELECT dc_hierarchy_id AS hierarchy_id

,'WGG' AS level_type

FROM [dbo].v_distributed_content(NOLOCK)

UNION

SELECT adn_hierarchy_id AS hierarchy_id

,'ADN' AS level_type

FROM [dbo].v_adnetwork(NOLOCK)

) other_type ON level_group_id = hierarchy_id

WHERE dbo.CurrentEffectiveDate(- 1) BETWEEN syn.EFFECTIVE_DATE

AND syn.EXPIRATION_DATE

) active_hierarchy ON all_client_web_mapping_active_and_expired.web_id = active_hierarchy.trending_id

A couple of things just jump off the page as performance hogs here.

First you have a scalar function CurrentEffectiveDate.

Secondly you have several different predicates that are nonSARGable. Like right(level_name, 3) <> '_AR'. No matter what kind of indexing you do there you will have a scan. That one is actually nonSARGable twice. First you have the column inside a function and then you have a <> comparison.

You have what looks like views joining views from within a subselect that do a UNION to other views joining views.

You might want to take a look at this article that explains best practices when trying to solve performance problems. http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]

Last but not least, be VERY careful using the NOLOCK hint, especially in a view. If duplicate data, missing data and dirty reads are ok then this hint may be ok.

http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx

If possible using snapshot isolation would be better.

http://msdn.microsoft.com/en-us/library/ms173763.aspx