Views for optimisation - when to use? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Views for optimisation - when to use?

Author

Topic

Lorna70
Starting Member

19 Posts

Posted - 2010-05-24 : 11:18:50

Hi

I have created a view to link 3 tables together so that I can then create an sproc to narrow the records down depeding o the customer's search criteria. However, some of these tables are huge and I can't even run the View locally as it times out! My dilemma is - should I archive some records to reduce the number of records my View is dealing with and then go on to create the sproc or should I not bother with a View at all and only use an sproc for my query? In otherwords some advice as when to use Views and when not to would be great!
Thanks
Lorna

Kristen
Test

22859 Posts

Posted - 2010-05-24 : 11:24:31

I doubt its the VIEW.

If you do

SELECT *
FROM MyView

it will get all rows - which may be slow.

but if you do:

SELECT *
FROM MyView
WHERE SomeColumn = 'ABC123'

then it should be as fast as you would expect to select just that row.

Otherwise you may have the JOINs wrong so that they are selecting gazzillions of rows.

What does

SELECT COUNT(*)
FROM MyView

return? Is that a reasonable number for the tables / criteria in your view? If not then you perhaps have (accidentally introduced) a Cartesian join

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-05-24 : 12:11:30

Views are for convenience, not for performance.

--
Gail Shaw
SQL Server MVP

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2010-05-24 : 13:54:38

When you have complex queries, that use many places in the stored procedures or functions, etc..,

It will be used as security mechanism in the web applications. When we use the original table in the web applications the hackers may drop the table. That time the original data will be persist in the table.

When you want to hide the particular columns to the specific people then we can create the specialized view.This is the fundamental use of creating views

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources