September 10, 2008 at 12:09 pm
Our developers almost exclusively use
SELECT * FROM viewname
with no WHERE clause. These types of queries are run in our Production environment. My feeling is that this is highly inefficient because I understand that SQL Server will do a table scan irrespective of any other execution plan it could use and then weeds out the columns / rows not defined in the view. Is this true?
September 10, 2008 at 12:27 pm
Developers having access to production it self is a bit too dangerous in my humble opinion. You dont know what they will run. You get a Select * with no where clause, you are going to get hell loads of reads (especially if it is a big table)
-Roy
September 10, 2008 at 12:29 pm
good gut feeling :w00t:
It depends what's the definition for the particular view.
e.g.
[Code]
Create view myview
as
select col1, col2
from mytable
where thekey = 15
go
Select * from myview
[/code]
Will most likely not do as much damage to your system as would do ...
e.g.
Create view myview
as
select *
from mytable
go
Select * from myview
Generally it is considered to be a bad practice to use
SELECT* from ...
One should always specify the column names one actually needs !
And select the strict minimum number of rows. (i.e. always specify a where clause)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 10, 2008 at 12:39 pm
SELECT * FROM ViewName
This is not bad if you really need all columns and all rows from the view. MSSQL will still use whatever execution plan it deems best to return this information, so if you have a complex view with a lot of tables, it may not do any table scans or it may do a few.
It is unusual to need every row, so I would typically expect a WHERE.
It is bad practice to use * rather than specify column names because you may not need all of the columns, or they could change order (particularly in a view) and this can cause problems in an application.
Finally, use of a stored procedure to return the data or changing over to LINQ would also probably be a better direction than any kind of embedded query against a view.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply