Simple query using group clause for 1 million records resulting
in final list of 27 records.
First time takes 0.43053775 secs.
Same query through Stored procedure: First time takes 0.43341600
secs.
So in terms of time, first time they are very close.
Profiling comparison for both can be seen in below figure
no_cache_comparison.png where left one is simple inline query and
right one is stored procedure query.
There are some actions which are extra in the inline query:
1. freeing items
2. logging slow query
3. cleaning up
Running both second time retrieve data from cache taking
0.00048025 secs for simple query and 0.00036625 for stored
procedure.
Profiling comparison for both can be seen in below figure
cache_comparison.png where left one is simple inline query and
right one is stored procedure query.
Here too there are some extra actions for inline query:
1. logging slow query
2. cleaning up
Taking into consideration the approximate equal timing of both
the type of queries and that on second time execution both use
cache, there is not much difference. But given the fact that
stored procedures are compiled and stored, will be efficient for
the db.
Also considering the article
https://sachinkumaram.wordpress.com/2011/03/01/stored-procedures-advantages-disadvantages/,
the advantages outweigh the disadvantages, I think.
Extra References:
http://www.seguetech.com/blog/06/04/Advantage-drawbacks-stored-procedures-processing-data
http://www.linuxjournal.com/article/9652
I would like to know your thoughts on this :)
Aug
24
2015