May 1, 2013 at 12:33 pm
I have which im looking to optimize ...
I have tried whatever i could think like Creating indexes, schema binding the view to create an index on the view ([dbo].v_adnetwork..... View in the code), included columns, but to no success. Could any one suggest anything which could help me
Below is how the view looks
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 outer 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 outer 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
Thanks
May 1, 2013 at 1:24 pm
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
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply