October 13, 2009 at 12:48 am
hi all!
I have a view which used in many SPs and it is very slow. when we have just 100-200 records it cant fetch records and we got a time out error.
any help would be appritiated.
thanx
Maryam
October 13, 2009 at 1:31 am
Please see the links in my signature below
October 13, 2009 at 2:48 am
Here is the script of view and execution plan.
October 13, 2009 at 3:40 am
Ok , first off thats and estimated plan not an actual plan, what i was looking for , amongst other things, was a difference between the actual rowcount and estimated row count which would point to the stats being out of date.
Secondly , remove all of the udfs from the view and rerun the query. My guess is that execution speed will be a lot better.
Replace as many of those as you can will joins. There is a high overhead even in calling a function see this post for proof
http://www.sqlservercentral.com/Forums/Topic790541-145-2.aspx#bm790978
October 13, 2009 at 6:44 am
I dont know how could I avoid using functions. please give me an example in my view, if it's possible.
thank you in advance.
I also post one of my functions' script.
Maryam
October 13, 2009 at 7:15 am
Not knowing your data , or having any sample data to test against , this is my best guess...
Select YourSourceTable.Id,
case when JobCategoryRecord.TopTaxonomyId = JobCategoryRecordParentId
then JobCategoryRecord.TopTaxonomy
else JCR2.Title
end
from YourSourceTable
join JobCategoryRecord -- Potentially a left join
on JobCategoryRecord.ID = YourSourceTable.Id
left join JobCategoryRecord JCR2
on JCR2.ID = JobCategoryRecord.ParentID
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply