1

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.

1
  • Please do login at both sites with the same credentials to retain ownership of this question when you follow it across!
    – jcolebrand
    Jan 24, 2013 at 15:40

1 Answer 1

7

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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