Nuwan Online - Nuwan Weerasinghe’s Web Log

When the December 26th Tsunami was devastating the shores of Sri-Lanka all of us including me could not belive that this was really happening. Its Simple….This has never happened before. The ITN was showing people running and climbing up to higher ground to servive and the ’stories’ they said about vehicles travelling on the Galle road being washed to the sea were hard to belive.
Since Hurricane Catrina has done somewhat the same to US (Gulf of Mexico) is seemed that people and authorities were struggling to predict the calamity. On top of the death toll the same old story of looting and rape cases were recorded from US just as it did in Sri-Lanka. So………Irrespective of the country or any other thing there were certain things in common right……………

But thinking of Japan and how better prepared they are for these situations might provide not just SL but US as well, some valuable lessons. Japan faces these natural calamities on a daily weekly basis, but we hardly here of looting, raping and other crap. They face desaster as one nation(Since Hiroshima, Nagasaki) and rise from disaster as one nation. Above all they are better equipped with knowledge,technology and mental strength.

We all will be living in a world where’ Day After Tommorows’ are fact rather than fiction. So its high time we take notice of Japaneese ways and means since they have proved successful. Also one thing to highlight is the Buddhist Theory of ‘Anithya’ or constant change. Japan being brought up in a Buddhist,Tao,Sen background knows better of the realities of life. But for ‘USA’ Anithya is not the case at least till 911.

Thats why I belive Japaneese have more secrets than just consumer electronics and ASIMO’s.

This whole issue of whether to use SP’s or Querries did’nt really occure to me until my recent 70-229 Exam.(SQL Server Development). I did not have a reason to select one over the other since I did not know the benefits,pitfalls of both approachs. But Our Web Application ‘Client’ was totally done using SP’s and the ‘Admin’ was running on Transact SQL Querries.
Once I’ve done 70-229 I had every reason to believe that ‘Admin’ also better be shifted to Stored Procedures due to couple of reasons.

1. Our Application is totally Internet based which means that we have severe limitations when it comes
to guaranteeing a maximum application execution speed. (Network Bandwidth.)
This was a critical issue since even an ADSL connection is rare commodity in Sri-Lanka.

2. Also we have to think in terms of Optimizing Queries once the system is up and running and the Data Base is
fed with high volumes of data. This also could be better handled.(Maintenance cost will be less) with SPs

3. Later if the client wants to restrict certain user activities by introducing extra Views, Invoking/Revoking
permisins on SELECT, UPDATE, DELETE statements, these could be easily handled via Stored
procedures .

So Considering all these we thought that we have a very good case to Make our whole application run on SPs. To further justify my thinking I did some research and found some interesting articles which says exactly why SPs are a better choice over Transact SQL queries.

This I thought would be a good starting point for anyone who is going through the same dilema. This is also another article which explains the same stuff a little more in detail. Anyway google offers a lot on this topic.

This also speaks of taking certifications since this would not have stuck me so powerfully if no for 70-229.

Having said all this I’ll briefly jot down a few Advantages of using SPs Over Transact SQL Querries in your code.

* SP’s greatly reduces Network Traffic by limmiting the Network call to a simple RPC style call rather than
sending a whole Querry Statement from Client to SQL Server.

*Stored procedure execution plans are reused by SQL Serevr rather than parsing-optimizing and Compiling
Queries everytime as and when they are executing.

*Code reusability is high with SPs since your code is separate from your querries.

*Stored procedures do a better job in Encapsulating Logic. You can change and Manipulate SPs as long as the
parameters remain the same and returning result sets are intact.

*Finally Stored procedures helps you to implement a better security model with INVOKING/REVOKING user
rights.