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):
Direct SQL | 23077 |
View | 19363 |
Stored procedure | 3438 |
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...
5 comments:
It is very interesting if you look at what goes on behind the stages when your query is being processed by the DBMS. This makes it also very easy to understand the time differences and why one approach is faster than the other.
We went over this topic in a recent Oracle course. Extremely interesting although DBs are not really my favorite CS topic ;)
Greets
Databasese are also not my main interest, but sooner or later the performance question arises and then, as you sad, you have to understand how the DBMS works.
It would be very nice to take a good course on SQL Server which explains a bit what goes on behind the scenes, but it is very very difficult to find such a course or trainer...
I'd try searching on the web...maybe u'r able to find some good resources (vids and stuff..) :)
I had heard there were big differences between SPs and direct SQL, but never knew of anyone who actually took the time to sit down and do the comparison. That's a HUGE performance boost for SPs. I knew it was a lot, but didn't know it would be that much. Thanks for posting this!
Nice post very helpful
dbakings
Post a Comment