Author Box
Articles Categories
All Categories
Articles Resources

5 Tips to Improving Database and Query Performance

February 24, 2012 | Comments: 0 | Views: 103

Considerable amounts of planning needs to go into building a database application. There are a number of things which can affect performance in a negative way if best practices are not followed.

Performance might seem perfectly fine in a test environment with only a few thousand rows inside the database but when the application goes live and begins to grow the database, bad design considerations can become obvious very quickly.

When designing the database and building the code to query it, there are simple design considerations and best practices which can help ensure that as the database grows, the system remains responsive.

The following five tips are in no particular order of preference.

1/ Design tables with the appropriate indexes

The lack of appropriate indexes can lead to massive decreases in performance. As the tables become larger, the optimizer has to scan through the data to retrieve the required rows based on the WHERE clause in the query. Imagine using a phone directory without an index to tell you where the page was for your local plumber. It is going to be slower finding them without the page that tells you that they are on page 302 or your 500 page directory. The query optimizer has the same problem if indexes are not put in.

2/ Don't use SELECT * FROM

It is easy to write SELECT * FROM. Writing queries in this way is much quicker than writing out the required fields from the table. Well you reap what you sow. If you take the time to build that SQL statement properly, your return on investment will be improved response times. This is because the sql query is pulling back less data than if all columns were included. If you only need 3 columns and not the 10 that comprise your table then just select those three.

3/ Ensure that stored procedures are used

There are many benefits to using stored procedures. One of the benefits is that the optimizer can call upon a cached execution plan. Ad-hoc SQL requires more time and resource to execute because the optimizer has to work out the best execution plan to execute the query.

4/ Don't use cursors

There is a time for using cursors however they should generally be avoided unless absolutely necessary. The optimizer works best dealing with sets of data. It is not too great at doing things very quickly using a row by row method.

5/ Ensure that you have a maintenance plans in place

In SQL Server, you can build maintenance plans to help rebuild indexes and refresh statistics. In MySQL, this can be done using OPTIMIZE TABLE commands as part of a batch process. Ensuring that maintenance plans are in place helps to control index fragmentation. Up to date statistics helps to ensure that the optimizer can use the best execution plan for the query.

For more tips view this post on improving database performance

Andy Hayes is a DBA working in SQL Server and MySQL technologies. His skills include database administration, performance tuning and ETL process design. Visit his blog DBA Diaries over at

Source: EzineArticles
Was this Helpful ?

Rate this Article

Article Tags:

Query Performance


Improving Database


Execution Plan


Maintenance Plans


Stored Procedures

Teenage rebellion is easier to tolerate in book or film form. Take a look back at some of the most well known and loved "coming of age" stories set in boarding schools.

By: Harvey McEwan l Arts & Entertainment l July 10, 2012 lViews: 445

An important thing you have to consider before working out is having a pair of great training shoes. You cannot immediately undergo any physical training wearing inappropriate footwear. If you do so,

By: Steve Hill l Recreation & Sports l July 10, 2012 lViews: 349

Granite is one natural stone that is used for several purposes from dimension stone to countertops to sculptures. The use of granite for building the temples and monuments was initiated millions

By: Steve L Patterson l Arts & Entertainment l July 10, 2012 lViews: 298

Up to 65,000 H-1B visas for new employment may be issued each fiscal year (FY) for professional workers, including medical providers. In general, a person who already has an H-1B and applies for

By: Ann Badmus l Arts & Entertainment l July 10, 2012 lViews: 308

Beside coin shooting, gold detecting or the many other things you can do with your metal detector. There is a large group of people who take their metal detector out looking for old stuff. Not just

By: Candy L Lindsey l Recreation & Sports l July 07, 2012 lViews: 293

Many people want to be writers and you may be one of them. In order to make your dream occupation a reality, you need to have a plan. You also need to work on this plan constantly. These tips from a

By: Harriet Hodgson l Writing & Speaking l July 06, 2012 lViews: 439

Have you ever needed portable power in a hurry? Temporary rental generators can help produce the power you need.

By: Todd Deall Computers & Technologyl June 06, 2012 lViews: 138

Technological expertise and talent are extremely valuable to many areas of modern industry. Hiring for this kind of skill often requires a special agency to match applicants with the right companies.

By: Jessica Lanel Computers & Technologyl June 06, 2012 lViews: 128

Computer issues can be tricky. Sometimes they can just happen without any warning. Issues may arise due to a power surge, a hardware failure, a virus, etc.

By: Daniel C Zical Computers & Technologyl June 05, 2012 lViews: 138

Information Technology is indeed one of the most fascinating and promising industries across the world. The rapid growth of this field is as a result of the roles played by the software development

By: Walt Generes Soltl Computers & Technologyl June 03, 2012 lViews: 154

CRM Customer Relationship Management plays an important part of any business and organization. With automation of every aspect of a business organization CRM is being automated too.

By: Neetu Jawlel Computers & Technologyl June 03, 2012 lViews: 162

Payroll Software is the most efficient and effective means of managing the financial accounts of an organization. The Payroll Software is basically a tool which handles, salary, bonuses, rewards,

By: Neetu Jawlel Computers & Technologyl June 03, 2012 lViews: 142