July 14, 2006 at 6:25 am
I was wondering if the optomiser is intellegent enough to filter out columns from the view rather than select them all when a query on a view is run.
Take for example a simple view like this :
CREATE VIEW vw_Products
AS
SELECT * FROM tbl_Products
Is there any difference between the following two statements apart from the fact it is going to have to review the view details. Basically is the view runnig all columns or just the two I selected?
SELECT ProductId, ProductName FROM vw_Products
SELECT ProductId, ProductName FROM tbl_Products
July 17, 2006 at 2:49 am
In certain circumstances, with more complex views , I've found that indeed the entire select * may be done within the view - not quite as you ask, in my case a filter should have returned 6 rows from a view but in fact it did two table scans resulting in several hundred thousand i/o .. I personally don't like views, they often don't optimise as well you hope and can sometimes prove almost impossible to provide useful indexes on the underlying tables. That said you can do some really useful things with partitioned views and indexed views.
You really have to examine the query plan(s) carefully with views to make sure they are optimal.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
September 3, 2006 at 1:09 pm
decided I need to revisit this one.....
I'm now looking at a database server where multiple users share the same database.To provide row level security I'm thinking of using a view like this :
SELECT *
FROM dbo.tbl_Contacts
WHERE SystemUser = SUSER_SNAME()
Queries will then be executed against the view.
So on a system with multiple users sharing the database I need this to be efficient. If sticking a generic view like this in the way is going to cause problems I need an alternative solution.
I wanted to avoid having to put "SystemUser = SUSER_SNAME()" in every query as this then becomes a security risk if for whatever reason the developer drops it.
I also wanted to give users direct access to their data as well through these views but if they then start reporting on the views and query optimiser isn't intelligent enough to see they are transparant it's going to cause problems.
September 6, 2006 at 7:54 am
Dan,
As far as I'm aware there should be no difference in performance when selecting from the view or the main table.
I set up a test as follows:
CREATE view testview as
select * from Orders2003Q3
where orderdate >'20030703' and orderdate '20030703' and orderdate '20030703' and orderdate '20030703' and orderdate CONVERT_IMPLICIT(datetime,[@1],0) AND [AdventureWorks].[dbo].[Orders2003Q3].[OrderDate] '2003-07-03 00:00:00.000' AND [AdventureWorks].[dbo].[Orders2003Q3].[OrderDate] '2003-07-03 00:00:00.000' AND [AdventureWorks].[dbo].[Orders2003Q3].[OrderDate] @mindate and orderdate <= @maxdate
resulted in a direct comparison, with no conversion required.
Either way, there appears to be no appreciable difference in performance between the views or the table query, with a CLUSTERED INDEX SEEK being used in each case.
Phil
September 6, 2006 at 8:36 am
That was a fairly simplistic example though.
I've got the DB and the views setup now so I'll load it with some test data and see what happens.
September 6, 2006 at 11:54 am
when views contain joins and the view becomes part of a join then sometimes things don't work out quite as you might expect. for a simple view there's no difference.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
September 6, 2006 at 12:19 pm
And that's really where my concern lies.
As I said earlier this is how I intend to give users direct access to their data. If they start using reporting services or crystal reports based on these views I'm thinking it could cause issues.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply