View & Procedure - SQL Server
Posted on 2013-01-06
I cant help but ask this question to all SQL Gurus here.
Recently I created a VBA application that calls a complex set of sql statements to show a data set in a certain way.
In order to accomplish the above, I tried doing it in two different ways...
1)I created a stored procedure to do the above
2)I also created a View to do the above.
I was tracking the seconds in the VBA application in terms of the time it takes to complete the command.
I found some real interesting info:
When VBA called the view from SQL Server, it took near to 1 minute
when VBA called the stored procedure from SQL Server it took 30 seconds
Why is this amazing difference ?
Is the stored procedure is better off than view in certain cases?
What are the advantages of using StoredProcedure ? Why would you want one, performance wise? like whats the real 'physics' behind it, that makes it preferable...if you get what I am saying..