January 4, 2011 at 1:58 am
This is the query inside the stored procedure. Individually gives a much lesser number but when run with procedure gives a high number.
/*
SET STATISTICS TIME ON
SET STATISTICS IO ON
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
*/
declare @today smalldatetime
declare @KeyCountry varchar(5)
declare @KeyGAAPDomainClass nvarchar(max)
SET @today = getdate()
Set @keycountry = 'DE'
set @KeyGaapDomainClass=N'1 13 2 9 8 14 5 4 7 19 16 6 10 15 3 17 18 23 20 21 22'
select distinct top 8 x0_0.KeyDoc,rtrim(x0_0.Headline)'Headline',x0_0.ArticlePostDate,x0_0.KeyArticleType,
x0_0.KeyMarketStory,x0_0.KeyArticleMarketingBadge,x0_0.ArticleAmendmentCount,rtrim(x1_0.ArticleType)'ArticleType',
x0_0.ArticlePostDate, x6_0.ArticleMarketingBadge from ObjectViews..AISArticle x0_0
left join ObjectViews..AISArticleType x1_0 on x0_0.KeyArticleType=x1_0.KeyArticleType
inner join ObjectViews..AISIndustry x2_0 on x0_0.KeyDoc=x2_0.KeyDoc
inner join ObjectViews..AISArticleIndex x3_0 on x0_0.KeyDoc=x3_0.KeyDoc
inner join ObjectViews..ArticleGeography x4_0 on x0_0.KeyDoc=x4_0.KeyArticle
inner join ObjectViews..GeographicAreaTree x5_0 on x4_0.KeyGeographyTree=x5_0.KeyGeographyTree --and TreeID<=7
left join ObjectViews..AISMarketingBadge x6_0 on x0_0.KeyArticleMarketingBadge = x6_0.KeyArticleMarketingBadge
where x5_0.Country = @KeyCountry --Belgium
and x4_0.CorporatePresenceRelation = 0-- Only Story Subject
and x2_0.KeyGAAPDomainClass in (SELECT number FROM dbo.iter_intlist_to_table(@KeyGAAPDomainClass)) -- Bank & Thrift
and x0_0.KeyMarketStory is null
and x3_0.KeyArticleSubject = 67 -- Industry News
and (x1_0.KeyArticleType not in (46,113) or x1_0.ArticleType is null ) -- Exclude Daily Dose and European Daily Dose
and x0_0.ArticlePostDate<=@today
order by x0_0.ArticlePostDate desc
Table 'Articles'. Scan count 375, logical reads 2452, physical reads 0, read-ahead reads 1, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
When i use the same query with procedure it gives me higher read.
Here is the procedure
exec s_Country_GetRecentNews @KeyGaapDomainClass=N'1 13 2 9 8 14 5 4 7 19 16 6 10 15 3 17 18 23 20 21 22'
Table 'Articles'. Scan count 1, logical reads 27282, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Can anyone help with that?
Thanks in advance
January 4, 2011 at 2:17 am
What do the two execution plans look like?
For that matter, what does the query look like as a procedure?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 4, 2011 at 2:34 am
I have attached execution plan for both with SP and with Query.
It showing one missing index on both, i have tried creating missing index on both, but getting the same result.
Thanks for quick response
January 4, 2011 at 3:41 am
Can you try this, see if it makes any difference?
UPDATE STATISTICS Articles WITH FULLSCAN
The key point is that the plan in the procedure uses a different index on the Articles table to when it's a query.
Also
UPDATE STATISTICS ArticleIndustry WITH FULLSCAN
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 4, 2011 at 5:42 am
Hi Gill,
I have tried with Update statistics as well but result is same.
Please let me know if you have any idea that i can try with.
Thanks
January 4, 2011 at 5:47 am
Try using local variables in the procedure, as explained: http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/
Do one at a time, until you find which parameter/variable affects the plan the way you want it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply