April 11, 2011 at 8:52 pm
hello,
I created a view which is using multiple databases, and am having very poor performance. I have added the required indexes on the underling tables, and used no lock hints in the view, but still i am having the performance issues.
when i see the execution plan it is having parallalisms and nested loops.
Can any one help me out working on this kind.
Thanks in advance.
Rahul.
April 11, 2011 at 9:13 pm
You need to post the definition of the view, and probably the definition of the tables and indexes - with some sample data, and the query you are using that references the view.
Also, if that view calls other views - we need to know that also. This is normally a large part of the problem with views and performance issues.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 12, 2011 at 1:46 am
We'd need to see the execution plan, actual by preference, estimated only if you just can't get it to run.
Also, are these databases on different servers/using linked server naming methods? If so you're probably dragging back huge quantities of data to the local server before anything can start. You might look into limiting what's ran on each foreign DB by proc or openrowset and dropping those into #tmp tables to help your optimization.
This is going to come down heavily to your code and the execution plan. It will be very difficult to assist without being able to see those. If you need help getting the plan out, check out the link in my sig with index/tuning assistance (second one down on the left, usually).
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 12, 2011 at 7:46 am
thanks.
April 12, 2011 at 11:26 am
Okay, now we will need to see how you are using this view - and the execution plan. With that said, I see a couple of issues right away:
1) You have a lot of ISNULL(CAST... operations on the columns. The columns appear to already be datetime (or date) columns which would mean the CAST is not needed. Also, you are returning an empty string when it is null - but, that empty string is going to be implicitly converted to '1900-01-01 00:00:00.000'.
2) There is no filtering in the view. If you are not including a where clause when you use this view it is going to return all rows in the table 'tblPackageID'. Also note that if you are filtering on any of the columns when you use the view that come from the outer joined tables, the results might not be what you are expecting. And, if you are using any of the columns where you have wrapped that column with an ISNULL and/or CAST in the where clause - you are going to get a table/index scan most likely.
3) You are referencing several additional views in this view - this could cause additional issues with performance if those views are anything more than a simple view over a single table.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 12, 2011 at 4:49 pm
As Jeferey said
excessive use of ISNULL,
Checking for NULLS and not replcing or doing nothing with it.
AS use said parallelism
Use maxdop(1) -query hint and run --> might be helpful.
What you are doing on this View.Ex: selects or any updates
while doing what you see performance degradation? explain more?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply