July 10, 2017 at 1:12 am
Hi All,
I have a question please about caching and buffering.
Is there a difference between how a view or a stored procedure behave regarding caching execution plan or buffering the data results?
And are there any scenarios where it's better to choose a stored procedure or a view (Performance wise) , meaning the stored procedure will not have any complex logic just an sql running with some conditions.
Regards
Nader
July 10, 2017 at 1:24 am
Sort of an apples vs oranges comparison. They're used for different purposes. What's your end goal?
July 10, 2017 at 2:53 am
pietlinden - Monday, July 10, 2017 1:23 AMGot Google?
https://stackoverflow.com/questions/31153523/is-there-any-performance-difference-between-view-and-stored-proceduresSort of an apples vs oranges comparison. They're used for different purposes. What's your end goal?
Thank you i will check that.
July 10, 2017 at 3:42 am
They're completely different.
Procedures are objects that you call and execute. They have cached plans.
Views are used as part of other statements. The other statements have plans, but the views, because they're not called alone, don't.
Neither get results cached. Nothing in SQL caches results.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 10, 2017 at 3:53 am
GilaMonster - Monday, July 10, 2017 3:42 AMThey're completely different.Procedures are objects that you call and execute. They have cached plans.
Views are used as part of other statements. The other statements have plans, but the views, because they're not called alone, don't.Neither get results cached. Nothing in SQL caches results.
After reading many posts about this which is "Is there a performance difference between running a simple SQL that runs with some conditions in a stored procedure or a view"
I concluded there is no difference in that performance wise.
But that takes me to another question, if some of those SQLs have variable conditions,if i run it as a view, SQL server will cache a different execution plan for each variance of those variables which will increase the amount of memory allocated for caching, while if i run it as a stored procedure it will cache one plan only but in this case we will face the parameter sniffing issue.
Looking forward for your input on this.
Thanks
Nader
July 10, 2017 at 3:57 am
nadersam - Monday, July 10, 2017 3:53 AMBut that takes me to another question, if some of those SQLs have variable conditions,if i run it as a view, SQL server will cache a different execution plan ...
SQL doesn't cache execution plans for views. They don't have execution plans. Queries that use them have execution plans, those queries can be ad-hoc, or in stored procedures.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 10, 2017 at 4:09 am
GilaMonster - Monday, July 10, 2017 3:57 AMnadersam - Monday, July 10, 2017 3:53 AMBut that takes me to another question, if some of those SQLs have variable conditions,if i run it as a view, SQL server will cache a different execution plan ...SQL doesn't cache execution plans for views. They don't have execution plans. Queries that use them have execution plans, those queries can be ad-hoc, or in stored procedures.
Sorry i meant for the sqls that use those views.
for the same SQL but only difference is the variables used in where condition, how to balance between SQL saving execution plan for each SQL compared to creating a parameterized stored procedure which SQL will save one execution plan only for but could face a problem with parameter sniffing.
July 10, 2017 at 4:12 am
If you're using ad-hoc queries, they should be parameterised, not have embedded literals. That's just good practice (and prevents SQL injection). At which point it's parameterised query vs parameterised query.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 10, 2017 at 4:13 am
GilaMonster - Monday, July 10, 2017 4:12 AMAt which point it's parameterised query vs parameterised query.
Sorry i don't get that part
July 10, 2017 at 4:22 am
nadersam - Monday, July 10, 2017 4:13 AMGilaMonster - Monday, July 10, 2017 4:12 AMAt which point it's parameterised query vs parameterised query.Sorry i don't get that part
i.e. no difference.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply