Basic steps while dealing with application slowness due to database

In this blog I will share my experience of improving web application performance. Application slowness is often faced in large scale applications. Slowness can happen due to multiple factors and one of the major factor is because of bad database design.

Recently I got a chance to work on an application optimization task. The whole activity includes many areas that are focused and optimized. The main performance improvements are though after database optimizations are done. I am listing down some of the major activities that are performed and found very helpful in finding performance improvement areas.

Is it really a database problem?

Get web server traces and find if there is performance issue in the application itself. Before saying that DB is the only issue just double check by getting and comparing the web server traces and SQL-Profiler traces. Confirm that most of the time taken by request is through database and not something from application itself.

Execute and check SQL-Profilers

Execute the SQL-Profiler and check the queries/stored-procedures that are taking most of time. Notice both duration and read counts in causing application slowness.

Check proper indexes

From the profiler traces one can easily identify the queries or stored-procedures that are taking time. Once those are identified, first thing you have to look is to check your indexes on tables used in queries or stored-procedures. Add appropriate indexes on required columns.

Execute query execution plans

Before I was not very friendly with this feature of Microsoft SQL Server. The Actual Execution Plan of a particular query or SP is very helpful in finding out the specific area that is taking time. The execution plan also suggests the indexes that can fasten up the execution. The suggested indexes can be applied on tables and the impact can be re-checked by executing query/SP again.

Use JOIN instead of IN statements on large data

Other than indexes do go through the statements either those can be overwritten more efficiently. For example instead of using IN clause use JOINS if the data is too much for IN clause.

Check for LOCKS

Specifically taking about MSSQL, it has different pre-defined SP calls that can hep in identifying locking information. For example sp_who2 and sp_lock SPs can be called to get locking information. Statements can be locked due to synchronous Update/Read hits are made on a same table in database. One of the statements can be blocked/locked until other statement releases the table lock.

Use WITH (NOLOCK) where ever required

Do not use WITH(NOLOCK) everywhere. Use it where ever required. I am not going to mention the details of using/not-using WITH(NOLOCK) but I will suggest to read some helpful articles related to this.

Check database statistics

Database statistics can be find out from sys.stats system table. sp_updatestats command can be used to update database statistics. Index rebuilding can also happen statistics update. These activities are not mandatory but if for some reason your database indexes goes bad you have to update/rebuild them. Bad database indexes cannot only return false results but can also make the query/SPs very slow to execute.

rizzz86

A Step Forward - Working abroad now !

After working for more than six years in different software companies of Pakistan, now its time to move forward and accept the opportunity to work abroad. This is my first experience to work overseas and live without family. I hope I will settle in the new environment soon.

I moved to Bahrain in mid August 2013 and joined a software company 01 Systems WLL (ZOS).

"Since its establishment in 1986, 01 Systems has become a pioneer in implementing complex, customized software solutions coupled with comprehensive professional services like Consultancy and Systems Integration to successfully optimize core business processes."

The company is established in the same year when I was born. With over 25 years of services in software industry the company is well established and has solid software products in market. It is a good chance for me to work on these large scale products and add my experience to them as well.

I hope everything will go smoothly and this will become a worthy experience in my career.

rizzz86

My experience @ RIKSOF

In this post I am going to mention my experience at 'RIKSOF Private Limited' where I have worked for around 2 years as a 'Senior Java Developer'.

Before starting I would like to mention one thing i.e. all the points are solely based on my own working experience at RIKSOF, :)

Learning Curve: You can learn a lot of new technologies here. Most of the developers working here have hands-on experience on at least two different technologies. You can get a chance to work on multiple platforms (Java, .Net, iOS, Android) and use different operating systems (Ubuntu, Mac, Windows). Due to 'project based' nature of company you will also be learning different domains based on what type of project you will get.

Staff: From CEO to Peon you can find a really nice and cooperative staff in RIKSOF. All of them are very helpful and always ready to support you whenever needed.

Picnic and Parties: This is something very common in RIKSOF. There is a quarterly Picnic/Dinner from company (location decided by employees). Apart from that the staff members are very keen to arrange Lunch, Dinner, Refreshments etc whenever possible. A separate well maintained planner is also available for this called 'Mangaloo Planner'.

Physical Environment: Talk about the building first that will definitely attract any one who sees it first time. The interior designs and building architecture is very impressive as compared to other office buildings in overall Karachi. The office from inside is also nicely designed. It has been designed by the interior designers that gives the creative look of office. The sitting area is also good (though not have the cubicles) with nice view outside the building.

Two day weekend: This is something that is quite common now but I liked it because in my previous company I have to work on alternative Saturdays.

Timings: There is no restriction on timings in RIKSOF. You just have to complete your task and leave. Some people come at 7 am and leave at 4 pm. My timing is mostly from 11 am to 8 pm.

Yearly Bonus: At the end of each year you will get the yearly bonus equals your salary. This is the actual saving that you can make the whole year.

Fuel Card: PSO card is provided by company which has limit up to 3000 rupees. The limit is ok for those who travel on bikes but very low for them who are on cars. But I will count it as positive because something is better than nothing :)

Salary on Time: You will get your salary on time

Some Good Memories:

Distance: Its too far from my home. I have to drive 55 km daily to cover home-office-home.

Technology Switching: In software houses mostly a person hired on technology has to work on the same technology or may be switch to a different flavor of it (for example Java EE switching to Android development). But in RIKSOF it can be possible that your designation is Java developer but you work on iOS application and the same case is with me. Most of times it doesn't feels good that you have to work on technologies that you haven't work on previously and you have to meet all deadlines as well.

Very tight deadlines: Project deadlines are very tight and its quite tough to meet the timelines and complete project on time. In span of two years I can see only two projects that completes on time else all are delayed.

No Work-Life Balance: To meet tight deadlines it becomes quite difficult to balance the work with your daily life. Most of the developers in RIKSOF has to do late sitting.

Project Base Development: You will feel sometimes that your job is not safe when you see that company doesn't have a project on which you can work on. Same situation comes to me as well when there are no Java or Android projects in company and I have only one option to switch to iOS projects.

Timings: I have also mentioned this in GOOD part of RIKSOF but there is also bad in it. To balance work with daily life I have tried several times to come early in office and leave early from office but have failed in not more than two days. If I reach office at 9 am and still have to leave at 9 pm due to work then I think there should be some strictness on timing i.e. to come earlier than 10 am or not to stay at office more than 8 pm.


rizzz86

Installing Adobe Flash Plugin on Ubuntu

Installing Adobe Flash Plugin on latest versions of Ubuntu (11 +) is straight forward and can easily be done by directly search and install from Ubuntu Software Center.

The problem comes when you are using the old versions of Ubuntu. Yesterday I have to install Ubuntu 9.10 on my system and got the problem of installing flash player plugin. After some searching I found the solution that requires enabling of repositories. Step by step solution is as follows:

• Go to "System > Administration > Software Sources" from the menu.
• Click "Other Software" tab and check the two options that represents the repositories. Selecting those repositories allows Ubuntu to download other softwares that includes adobe flash plugin as well.
• Go to  "System > Administration > Synaptic Package Manager" and search for 'adobe flash plugin'. It will give the package to install, you just need to select that package and click the 'Apply' button on the top.

Now you can run flash videos/games on your system.

rizzz86

Open Transport Tycoon Deluxe (TTD)

TTD is one of my most favorite games that I have played and when I saw the 'Open' with it, I was really excited to install it on my Ubuntu OS.

The installation of Open TTD is difficult and you need to be a bit keera of shell scripting to do this job. I have followed all the steps on this link and managed to install OTTD successfully. After completing installation and launching the game I was really amazed by watching the same TTD main screen as what I was playing on my 'Windows OS' four years back.

The game is upgraded now and contains four different types of maps with each map containing different industries, disasters, vehicles etc. Maps are also really interesting with environment like a Desert area map, Snow area map, Toyland area map and a default one as well. I have some getty images of the game that are as follows:

All TTD lovers do enjoy this on your Linux OS.

rizzz86

Flood Relief Update Link

Following link contains the activities performed by a group of people ( from IT/Software Industry of Karachi) for flood victims:

http://floodreliefupdates.blogspot.com/


rizzz86

My UBUNTU 9.04 Desktop ;)

rizzz86