I heard that one big DECLARE statement is better than a plenty of small statements. I tried to check which approach is really better. To find the answer I’ve created two stored procedures OneBigDeclare and ManySmallDeclare. Also I’ve written a simple test that executes 100 cycles. Each cycle executes each stored procedure 10 thousand times.
All source codes can be downloaded from declare_execution_test
My results
Average execution time of a cycle:
OneBigDeclare – 165.62ms
ManySmallDeclare – 171.12ms
It seems that one big DECLARE statement is better for just 3.3%. But when you look at the graph of cycle’s execution time you see that it’s not always true. On X axis is the sequence number of a cycle, on the Y axis is the execution time in milliseconds.
So I can’t prove that one big DECLARE statement is really better.