Welcome!

Blog Feed Post

Database Tuning – Performance tuning tips for Database Developers

What is Database Tuning?

Database Tuning is a group of activity of making database application more robust. Database tuning is used to optimized the performance of the database. It aims to increase throughput and lower the response time. Database tuning is one of an essential process to maximize use of system resources to perform work as efficiently and rapidly as possible.

Basically, Database performance tuning is a responsibility of a Database administrator(DBA) but database developers also play a significant role in the task. During the database performance tuning, the developers need to know few important things regarding the coding and best practices. Although newer relational databases and hardware configuration runs SQL queries with a small response time but there is always a room for improvement. So Let’s have a look how a developer can avoid the mistakes to make database application more robust

Statistics Of Database

The database statistics contains all the information about the indexes and the distributions with each other. A developer needs to optimized the least expensive path that satisfies a query. Missing statistics information can cause to take a more expensive path rather than more optimized path. Therefore this increases the overall response time for a query.

SQL optimizer always chooses the least expensive path which gives the least number of rows in the fastest time. Let’s go through the following query to understand the statistics.

SELECT * 
FROM Employee 
WHERE city="Shanghai"
AND phone="+23894554";

As We know a table uses only one index. Therefore it all depends on the developer to choose what column you are using for the index. In above case, if you use “phone” as an index then the response time will be low.

Creating optimizing Indexes

The SQL optimizer massively depends on Indexes set for a particular table. If you have no Index set for a table then it degrades the performance of select query and if you have a lot of Indexes for a particular table then it slows down your DML(Insert, Update, Delete) queries. Hence it is important to balance the Indexes on tables in a database.

Composite index: In this type of indexing more than one fields are used. Such indexes should be created when you have more than one field in the where clause of the query.

clustered Index: In clustered indexing, a table includes physical order of data that can be determined by the clustered index. Each table contains only one clustered index. It is used in the implementation of a telephone directory. Oracle calls it Index-Organized Table (IOT) whereas DB2, MS SQL Server and Sybase all call it clustered index.

Specify optimizer hints in SELECT

In most cases, the optimizer selects an index according to the statistics but it is better to specify the index name in your SELECT query like this:

SELECT * 
FROM Employee 
WITH ( Index(IdxPhone) ) 
WHERE city="Shanghai"
AND phone="+23894554";

Use EXPLAIN

Most of the newer database return the execution query plan for a SELECT query. Therefore, the EXPLAIN value can be used in fine tuning of the SQL Query.
The different database uses different SQL syntax like:
In Oracle: EXPLAIN PLAN FOR >Your query<
In MS SQL Server: Set SHOWPLAN_ALL ON >Your query<
In DB2: EXPLAIN PLAN SET queryno = xxx for >Your query<

Choose limited data in SELECT

It is a good choice of filtering data at server side rather than on the client side. So you must avoid (SELECT *) if there is no need on the client side. Instead of choosing everything you must select only the required fields in the query. for example:

SELECT Fname, Lname
From Employee;

Avoid Foreign key constraints

As we know in the relational databases a foreign key ensures the data integrity. Therefore, if performance is your first choice then you can push the formula of the data integrity towards the application layer. System tables are a good example of this. These tables contain meta data information about user databases and also there is no foreign key relationship.

Drop Indexes before inserting data

If you want to make you DML queries faster then you must skip the index before loading the data into the table. As I told earlier that indexes reduce the performance of the DML queries. Once the insert operation complete you can restore the index again.

In conclusion, Database tuning increases the performance and reduce the response time. Therefore, developers can perform the database tuning with the right coding in the right places.

The post Database Tuning – Performance tuning tips for Database Developers appeared first on AppPerfect.

Read the original blog entry...

More Stories By Steve Young

Steve Young is a software developer working at AppPerfect Corporation, a software development company based in Cupertino-US, renowned for its monitoring and testing softwares and providing robust and cost-effective custom software development services.

Latest Stories
Automation is enabling enterprises to design, deploy, and manage more complex, hybrid cloud environments. Yet the people who manage these environments must be trained in and understanding these environments better than ever before. A new era of analytics and cognitive computing is adding intelligence, but also more complexity, to these cloud environments. How smart is your cloud? How smart should it be? In this power panel at 20th Cloud Expo, moderated by Conference Chair Roger Strukhoff, pane...
Cloud Expo, Inc. has announced today that Andi Mann and Aruna Ravichandran have been named Co-Chairs of @DevOpsSummit at Cloud Expo Silicon Valley which will take place Oct. 31-Nov. 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. "DevOps is at the intersection of technology and business-optimizing tools, organizations and processes to bring measurable improvements in productivity and profitability," said Aruna Ravichandran, vice president, DevOps product and solutions marketing...
@DevOpsSummit at Cloud Expo taking place Oct 31 - Nov 2, 2017, at the Santa Clara Convention Center, Santa Clara, CA, is co-located with the 21st International Cloud Expo and will feature technical sessions from a rock star conference faculty and the leading industry players in the world. The widespread success of cloud computing is driving the DevOps revolution in enterprise IT. Now as never before, development teams must communicate and collaborate in a dynamic, 24/7/365 environment. There is ...
SYS-CON Events announced today that CA Technologies has been named "Platinum Sponsor" of SYS-CON's 21st International Cloud Expo®, which will take place October 31-November 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. CA Technologies helps customers succeed in a future where every business - from apparel to energy - is being rewritten by software. From planning to development to management to security, CA creates software that fuels transformation for companies in the applic...
SYS-CON Events announced today that IBM has been named “Diamond Sponsor” of SYS-CON's 21st Cloud Expo, which will take place on October 31 through November 2nd 2017 at the Santa Clara Convention Center in Santa Clara, California.
What's the role of an IT self-service portal when you get to continuous delivery and Infrastructure as Code? This general session showed how to create the continuous delivery culture and eight accelerators for leading the change. Don Demcsak is a DevOps and Cloud Native Modernization Principal for Dell EMC based out of New Jersey. He is a former, long time, Microsoft Most Valuable Professional, specializing in building and architecting Application Delivery Pipelines for hybrid legacy, and cloud ...
In his session at Cloud Expo, Alan Winters, an entertainment executive/TV producer turned serial entrepreneur, presented a success story of an entrepreneur who has both suffered through and benefited from offshore development across multiple businesses: The smart choice, or how to select the right offshore development partner Warning signs, or how to minimize chances of making the wrong choice Collaboration, or how to establish the most effective work processes Budget control, or how to ma...
We build IoT infrastructure products - when you have to integrate different devices, different systems and cloud you have to build an application to do that but we eliminate the need to build an application. Our products can integrate any device, any system, any cloud regardless of protocol," explained Peter Jung, Chief Product Officer at Pulzze Systems, in this SYS-CON.tv interview at @ThingsExpo, held November 1-3, 2016, at the Santa Clara Convention Center in Santa Clara, CA
SYS-CON Events announced today that Cloud Academy named "Bronze Sponsor" of 21st International Cloud Expo which will take place October 31 - November 2, 2017 at the Santa Clara Convention Center in Santa Clara, CA. Cloud Academy is the industry’s most innovative, vendor-neutral cloud technology training platform. Cloud Academy provides continuous learning solutions for individuals and enterprise teams for Amazon Web Services, Microsoft Azure, Google Cloud Platform, and the most popular cloud com...
With major technology companies and startups seriously embracing Cloud strategies, now is the perfect time to attend 21st Cloud Expo October 31 - November 2, 2017, at the Santa Clara Convention Center, CA, and June 12-14, 2018, at the Javits Center in New York City, NY, and learn what is going on, contribute to the discussions, and ensure that your enterprise is on the right path to Digital Transformation.
With major technology companies and startups seriously embracing Cloud strategies, now is the perfect time to attend 21st Cloud Expo October 31 - November 2, 2017, at the Santa Clara Convention Center, CA, and June 12-14, 2018, at the Javits Center in New York City, NY, and learn what is going on, contribute to the discussions, and ensure that your enterprise is on the right path to Digital Transformation.
21st International Cloud Expo, taking place October 31 - November 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA, will feature technical sessions from a rock star conference faculty and the leading industry players in the world. Cloud computing is now being embraced by a majority of enterprises of all sizes. Yesterday's debate about public vs. private has transformed into the reality of hybrid cloud: a recent survey shows that 74% of enterprises have a hybrid cloud strategy. Me...
DevOps at Cloud Expo, taking place October 31 - November 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA, is co-located with 21st Cloud Expo and will feature technical sessions from a rock star conference faculty and the leading industry players in the world. The widespread success of cloud computing is driving the DevOps revolution in enterprise IT. Now as never before, development teams must communicate and collaborate in a dynamic, 24/7/365 environment. There is no time to w...
"When we talk about cloud without compromise what we're talking about is that when people think about 'I need the flexibility of the cloud' - it's the ability to create applications and run them in a cloud environment that's far more flexible,” explained Matthew Finnie, CTO of Interoute, in this SYS-CON.tv interview at 20th Cloud Expo, held June 6-8, 2017, at the Javits Center in New York City, NY.
In 2014, Amazon announced a new form of compute called Lambda. We didn't know it at the time, but this represented a fundamental shift in what we expect from cloud computing. Now, all of the major cloud computing vendors want to take part in this disruptive technology. In his session at 20th Cloud Expo, Doug Vanderweide, an instructor at Linux Academy, discussed why major players like AWS, Microsoft Azure, IBM Bluemix, and Google Cloud Platform are all trying to sidestep VMs and containers wit...