Views

Question

Post reply

Views

rohaandba

Mr or Mrs. 500

Points: 585
More actions
May 2, 2011 at 8:10 am

#250140

Hi,
I have 4 views on my database on SQL Server 2008 R2,
Its really taking more time to execute these views.
How Can I Make the views to execute faster.
I do have an idea of creating an Index on these Views,
Can I go creating the index on the view or Can I do some thing else
Please suggest me the best ways to increase the performance of these Views

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 1

Views are no different from any other query, what does the execution plan look like ?

Clear Sky SQL
My Blog[/url]

rohaandba Mr or Mrs. 500 Points: 585 More actions · Answer 2

If I do

Select * from 'View_Name',

Then its taking more than 3 mins to fetch the result

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 3

Please post execution plans as per

http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Clear Sky SQL
My Blog[/url]

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

rohaandba (5/2/2011)
If I do
Select * from 'View_Name',
Then its taking more than 3 mins to fetch the result

That doesn't mean it's slow.

How many rows in each tables touched by the view? How many rows returned by the view?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Dave Ballantyne (5/2/2011)
Please post execution plans as per
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

+1

Revenant SSC-Forever Points: 42467 More actions · Answer 6

rohaandba (5/2/2011)
. . .Its really taking more time to execute these views. . . .

That really dows not say much. How many rows are you retrieving? On what hardware is your SQLS running? (Hint: no matter how well the query is optimized, SQLS retrieving 50 million rows on an old Pentium machine will take minutes...)

rohaandba Mr or Mrs. 500 Points: 585 More actions · Answer 7

Please find the querry execution plan as attachment

Thankyou

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

We need the actual execution plan not the estimate.

When I open this up in SSMS R2 I immediately get a missing index warning.

The real warning I get is that you have something like 10 - 25 full table scans in there.

The "worst" warning is this one :

Time Out for the reason for early terminason of optimisation. That means that the server has runout of tries (not actual time) to figure out a god enough plan to return the data.

The means you'll have to really help out the optimiser (more index, more stats, or actually just update stats might do it).

We'll be able to give a few pointers here but I don't think we can realy tune this out correctly over a forum.

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 9

Wooo

A lot going on in there , as stated we need the actual plan.

The DDL of the view and tables involved would help us make sense of it ..

Clear Sky SQL
My Blog[/url]

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 10

Indexed Views can help. But they have their own limitations.

As experts already mentioned, please post the actual execution plan.

First things First, You need to optimize the underlying query itself by removing table scans, Bookmark or key lookups , creating missing indexes etc...

Also make sure that the statistics are updated regularly to get better performance.

Thank You,

Best Regards,

SQLBuddy.