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

Trying to improve the vIew performance Expand / Collapse
Author
Message
Posted Wednesday, May 1, 2013 12:33 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Today @ 3:43 PM
Points: 1, Visits: 138
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


Post #1448565
Posted Wednesday, May 1, 2013 1:24 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:56 PM
Points: 13,081, Visits: 12,545
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/

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1448589
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse