Recently got a chance to tune a SQL query, which was around 5000 lines long. This SQL was very clumsy, used around 2000 lines of decode statements, and it took around 9-10 hrs to retrieve any results, and was a big drain on the CPU resources. It was business critical report and users were complaining about the performance issues since past 2-3 months. After tuning we were able to bring down the execution time to around 30 mins.

When the Root cause analysis behind such state of this code was done following were the findings:-

• As usual the project had very tight time lines and development time-frame was very short. Hence things were developed in a great hurry!!!J
• Requirements were not clear and were changing frequently
• Design was also done in a great hurry and lot of temporary fixes were applied due to paucity of time (the problem with temporary fixes is that they are always permanent)!!!
• Coding was also done in a hurry and some fundamental issues, initially people were not even aware as to what they were doing!! They just coded something under pressure

This is not just the case with this SQL query, rather most of the IT projects suffer from this problem. Most of the times there are unreasonable expectations from the customer and management, times lines are very short, additionally due to huge skill set gap in demand and supply in IT and Software Industry it’s very difficult to find “Superheroes” who can surge the team through the adversity and  deliver that perfect code which exists only on the imaginary plane,  ultimately all these things result in a cluttered software swamp.!!!

This long running query also reminded me of two axioms from famous book the Fifth Discipline,

• Today’s problems came from yesterday’s solutions:- as this performance issues came from the way coding was done in a great hurry and under the gun!!
• The easy way out usually leads back in :- These temporary or easy fixes again created the issues which had to be fixed from scratch

That’s what happens when we do not devote an optimal time to the activities like requirements gathering and design and violate all the fundamental software development practices, in order to finish the project.

Not only this practice is harmful for us, but it also leads to Global warming, you must be wondering, how a piece of code can lead to Global warming? Just think over it, A badly written code, will consume more computing power and CPU resources and when this information is over written in CPU, more information will be disseminated into the environment, and we know from Dr Vedral that, Information is physical and hence it will lead to an increase in the temperature around the machines on which this code is running, hence power required to keep the servers cool in the data center is much more energy intensive than the commercial buildings.

Hence every time we write a code which is not optimal and consumes more RAM and CPU, we are contributing to the Global warming!!!

Heat in Data centers has become a subject for Research these days, however at this point of time the amount of heat getting dissipated to the environment due to inefficient resource intensive code is not known,  however the IT industry and demand of IT Services is growing exponentially around the Globe, new technologies are taking birth. The amount of Code in the servers of the world is also increasing at an alarming rate and so is the inefficient code. Very soon in the near future, I won’t be surprised if there are fines imposed on IT companies, just in case, they fail to write the Green Code, and performance tuning may become an environment/planet rescue job!!!