Hi there.
I wonder which practice is best.
Say I have a stored procedure that is EXECuting an SQL statement, retrieving data from a complex view.
Assuming that criteria are standard, which would it be faster?
Setting the criteria in the Where clause of the SQL statement or setting the criteria directly to the view?
Is SQL server clever enough to pass the criteria to the view on retrieval or does it retrieve all the data from the view and then apply filtering?
SQL will use the criteria from the query when creating a plan using the view. You can specify the conditions in either place and you should get the same query plan.
1 Like
Is it a indexed view and does the where clausule make it a lot smaller? When you insert/update/delete the data the view is also adjusted. What are you trying to solve?
I do think you should use the sp, when the requirements change you can easily adjust the sp but adjusting a where clausule in a view is much harder as you cannot know what else you change/break. But as Scott mentioned I don't expect much perfermance improvement on standard criteria.
1 Like
I am using Microsoft Access since it was created.
So I am used to Rapid Developement and Visual Design.
I found a way to sent parameters directly to View (via Session_Context) and also to set up the WHERE CLASUE and pass it as a parameter.
I wonder if it is worthwhile to put criteria in View or leave them only to where clause.