Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I was told from a SQL-DBA that there is a huge gap in performance between these statements, both statements are called from a stored procedure:

-- Procedure that select from table directly
Select * From [Table] Where condition

-- Procedure that select from a view
Select * From [View] Where condition

The SQL-DBA told me that selection from table is faster because when you select from a View, all data will be selected first then filtered, but from table it will apply filtration directly. I am a developer and I don't have much knowledge in database so I was hoping for some expert to advice me in this matter.

Upon answering this question, I think I will be able to know weather using Views is a best practice or not.

share|improve this question

migrated from stackoverflow.com Jan 23 '13 at 18:08

This question came from our site for professional and enthusiast programmers.

    
Please do login at both sites with the same credentials to retain ownership of this question when you follow it across! –  jcolebrand Jan 24 '13 at 15:40
add comment

1 Answer 1

up vote 7 down vote accepted

The SQL-DBA told me that selection from table is faster because when you select from a View, all data will be selected first then filtered, but from table it will apply filtration directly.

As a general statement this is nonsense. The view definitions are expanded out during query optimization and the predicates are pushed down as if you had filtered against the table directly.

You can check the execution plans to confirm this.

If you are filtering on the result of a calculated expression then this may be problematic however (the same as any unsargable expression on a table) and there have been issues with SQL Server not pushing predicates down as far as it could do for views containing window functions.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.