Common mistakes we do in SQL Server Queries.
For the last several years I was working with several enterprise applications, obviously that makes me work on MS SQL Server more than any other application or technology. When I speak about Enterprise applications, we have lot of data – millions. So applications built on that size of data need to be very cautious about the queries and performance results of those queries.
I learned so many things and as I was noticing the patterns in SQL Server ‘human-errors’ looked same for several programmers. Smaller steps that you keep in mind can really help you write high performing application. I would like to comment on few points that I noticed to improve the performance of queries.
- Make sure you have Indices created for all Join operations – When the huge tables are created and joined with queries, there are possibility that the table gets ‘scanned’ several times for validating all join conditions. So if you create Index for that join field, system will use hashing and will be much faster.
- Avoid using cursors – Cursors are real curses. Avoid cursors and use temporary tables if joins are complex. In case you have recursive cursors, you can use Common Table Expressions. These both will give much quicker results than a cursor.
- Avoid views or complicated joins for large tables – When you have a big table containing tens-of-thousands of data and several other tables, the join causes to read through main table several times for each join (even thought is having index). In this case, you can make snapshot of large table in to a small table and then do all the joins with that temp table. For example if you need to prepare reports for year 2009, then get all the data related to 2009 from the large table to the temp table and then join other tables with this temp table.
- Avoid update statements even for temp tables – Consider for reports you need to calculate the extended price for the materials sold. I see several people declaring a temp table and then insert the material details and cost in to the temp table. After that they do an update for calculating the extended price = unitprice * qty. Instead you can directly use this as a calculated column in the select statement itself.
- Avoid Delete – Very important. Any delete (physical delete) should be done when there are no much use of the system. So I would suggest to do a batch delete in off-peak hours. When we delete the system internally does so many calculations to verify the foreign keys, validations, recalculation of indexes etc. For a large table a delete can cause deadlock situation if some one else is trying to read from this table during a delete (if delete happens to take time).
- Get only what you want – It is a common practice to use Select (*) even though we need just 2 or 3 columns from that table. Even if you take all columns better make it a practice to specify the columns for the select. This way in later stages, you can avoid unwanted fetches even if we add additional columns may be years later.
- Do not copy what is working for another procedure – It is again another common practice that we copy an existing SQL statement or procedure to make a variant of the same (may be for some additional filtering or joins). It is always a wrong practice, I strongly recommend to build the query from scratch, that allows you to revalidate your requirements and take only what you need.
- Do not read table twice – I see several programmers write queries to get the record before we do an update. In a large table this would cost you almost twice the duration.
- Have a primary key for all tables – If you donot keep it, better do not program SQL Server
There are obviously lot of other performance improvements we can implement. I welcome you if you have any more points to add to this list.
Being good.
In Hinduism, there are Devas and Thrimurthis (Super Gods). Devas are considered to be in charge of a function like air, fire, rain, water and other elements and forms. They have much focused ‘job’ and responsibilities for taking this universe ahead. Indra the king of Devas is blessed with lot of wealth, kamadhenu for enough milk, ‘akshaya patra’ for enough food etc. He gets anything he wishes for. But still he always fears and fights ‘Asuras’. He always looks out for ‘Rishi’ that could harm his position. He really worries too much to keep his position as ‘Devendra’. He is the controller of gods of pancha-boothas at the same time; he fears that others may take over the crown.
That is the situation of all businesses today. They always compete each other and fear about their position. The management will do anything to keep their competitive position and profits. Same is the case of humans. There is no value for relation, humanity or anything. All just want is money.
In Shrimath Bhagavatham, Lord Krishna says ‘The one who knows ME, never fear about anything….. Everything happening is for Good.’. He means that he is the supreme God of the universe. All other devas and deities are a form of him. So if we believe in Lord with out any doubts and we should understand that every thing is happening for the ultimate Good; GOD will never let you down for any reason.
For any incident, be it good or bad, you are giving your version that you like to it. God is always with you – in you. When you listen to God the ‘God’ in you reflects to others since you do not commit damages for others; when you don’t you become cruel and a daemon.
You worship Lord Vishnu and if you are near to Lord, then naturally Lakshmi follows God Vishnu. There is another story that if you worship Lord Vishnu and if you win his attention, Lakshmi will give you lot of wealth to make you leave the Lord – her husband. But if you just worship Lakshmi, she would not stay back at your house since she needs to get back to her husband.
The story always has a meaning – Never worship just money. You need to be content. Be it an organization or human being. You do what you are supposed to be doing and be more ‘value-centric’ than ‘profit-centric’. If you are good human being or a business having the values – profit or wealth will follow you..
Microsoft brings its Web services to Nokia phones
Nokia is ready to bring Microsoft Corp.’s Windows Live suite of Web services, like e-mail and instant messaging, to many of its cell phones.
Oberoi Udaivilas voted world’s best hotel – survey
An Indian hotel has won the award for the world’s best hotel for the first time in a survey by an international travel magazine .
Travel + Leisure Magazine released its 12th annual list of the world’s best awards after a poll of almost 19,000 conducted by researcher Harris Interactive.
The Oberoi Udaivilas in Udaipur, a grand setting combined with palatial architecture in heart of Rajasthan was voted the top hotel worldwide.
Prices for a room per night start at about $600 with an exclusive suite with private pool priced from $3,300, according to the hotel’s Web site.
Read more about the story in Reuters site.
Mail from a frustrated victim of chain mails
This one is actually another forward sent from one of my contacts; but I liked the mail very much since the following could be anyone among us.
I wanted to thank all my friends and family who have forwarded chain letters to me in 2003 & 2004 & 2005 & 2006
Because of your kindness:
* I stopped drinking Coca Cola after I found out that it’s good for Removing toilet stains.
* I stopped going to the movies for fear of sitting on a needle infected With AIDS.
* I smell like a wet dog since I stopped using deodorants because they cause cancer.
* I don’t leave my car in the parking lot or any other place and sometimes. I even have to walk about 7 blocks for fear that someone will drug me with a perfume sample and try to rob me.
* I also stopped answering the phone for fear that they may ask me to dial a stupid number and then I get a phone bill from with calls to Uganda, Singapore and Tokyo .
* I also stopped drinking anything out of a can for fear that I will get sick from the rat feces and urine.
* When I go to parties, I don’t look at any girl, no matter how hot she is, for fear that she will take me to a hotel, drug me then take my kidneys and leave me taking a nap in a bathtub full of ice.
* I also donated all my savings to the charitable trust. A sick girl that was about to die in the hospital about 7,000 times. (Poor girl! she’s been 7 since 1993…)!
* I went bankrupt from bounced checks that I made expecting the $15,000 that Microsoft and AOL were supposed to send me when I participated in their special e-mail program would arrive soon.
* My free Nokia phone never arrived and neither did the free passes for a paid vacation to Disneyland .
* Still open to help some from Bulgaria or Nigeria who wants to use my account to transfer his uncle property of some hundred millions $.
* Made some Hundred wishes before forwarding those Ganesh Vandana, Tirupathi Balaji pics etc… now most of those ‘Wishes’ are already married (to someone else) IMPORTANT NOTE: If you do not send this e-mail to at least 11,246 people in the next 10 mins, a bird will SH** on your head today at 6:30pm.
Acquisition by Indian companies overseas continue.
Raman Roy’s (who was heading Spectramind of Wipro earlier) Quatrro BPO Solutions has announced the acquisition of mortgage loan processing operations and the technology platform of US-based Preferred Financial Group, Inc. The financial terms are undisclosed.
The deal is considered unique since Quatrro would become the first BPO from India to offer an onsite-offsite model. This path has already been taken by software services firms. According to Raman Roy, this acquisition will help the company to acquire the technology platform, the people and the origination business of the US partner and that would also enable to provide end-to-end fulfillment services to mortgage lenders across the US.
Apparently this model will hep Quatrro give services at 30 per cent to 50 per cent cheaper than the US costs. The acquisition will be done by Quatrro Mortgage Solutions, a subsidiary of Quatrro. PFG is a privately-held financial services corporation that originates, underwrites and funds mortgage products.
In April 2007, Quatrro had bought a majority stake in Chennai-based KPO firm Scope eKnowledge for an undisclosed amount. It bought out Mauritius-based fund eIndia’s stake in the 500 people firm.
Wipro acquires Infocrossing, one of the largest IT deal in India
As rumoured earlier, Wipro Technologies, the global IT services business of Wipro Ltd, has acquired Infocrossing, Inc., a Nasdaq-listed IT infrastructure management company for $600 million in an all cash deal. Wipro will pay $18.70 a share, which is about 13 per cent premium over the six month average of Infocrossing’s share price.
The deal, the largest by Wipro and the largest overseas acquisition by an Indian IT company yet (see the table, bottom), is expected to close by the fourth quarter of 2007.
It also needs regulatory approvals. The deal is also significant since it’s the first acquisition by Wipro of a listed company abroad, which shows that Indian companies are getting better at wading through the complex regulatory hurdles and disclosure norms. Infocrossing had revenues of $232.4 million and net income of $9.3 million for the year ending March 31, 2007.
The company provides integrated managed infrastructure services to global clients. The acquisition will give Wipro Technologies a strong presence in the remote infrastructure management space, by adding five data center locations and about 900 employees in the US, according to Sudip Banerjee, President, Enterprise Solutions of Wipro Technologies. Infocrossing also brings in expertise in health plan and payer management segments. The company has processed over 175 million claims annually and also provides contracted services to over 90 managed care organisations.
The operating margins of Infocrossing is only in the range of 10-14 per cent as against Wipro’s 25 per cent. Apparently, the margin differences will be mitigated in the next 2-3 years, according to company officials. The current global IT infrastructure market is estimated to be around $150 billion, of which the remote infrastructure management services (that can be offshored) will be worth $70 billion, according to NASSCOM.
