I was wondering for a very very long time what all these "battels" around direct sql, views and stored procedures are about. I read several articles on the Internet where people were claiming that nowadays there is almost no difference anymore between these three database "constructs". I saw many discussions and blogs where people tested the performance of these three with different results and that made me even more confused.
So as I am curious and the fact that I need to know this as a software developer I decided to try this out by myself. At work I had the chance to test this against a very large amount of real data (I needed to optimize this query anyway);
Sometimes the sample databases which are used for theses kinds of tests are either very small or very simple or even both which makes not really sense in my eyes.
So the query consisted of joins of multiple tables with a case in the select statement. I run the query 300 times with every "construct" and then I took the average value for all three of them in order to reduce network and SQL Server workload "noise".
The result of my test (in Ticks):
So what I found out for myself is that it really does matter what kind of "construct" you use to query your data; especially on a large amount of data.
On large data you should also take into consideration that a big performance impact may also have where you set your indexes and which statistical data you let create by SQL Server.
If you made other experiences on this, please let me know and leave a comment...