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.

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:

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


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
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.
We all know that end users experience the Internet primarily with mobile devices. From an app development perspective, we know that successfully responding to the needs of mobile customers depends on rapid DevOps – failing fast, in short, until the right solution evolves in your customers' relationship to your business. Whether you’re decomposing an SOA monolith, or developing a new application cloud natively, it’s not a question of using microservices – not doing so will be a path to eventual b...
Infoblox delivers Actionable Network Intelligence to enterprise, government, and service provider customers around the world. They are the industry leader in DNS, DHCP, and IP address management, the category known as DDI. We empower thousands of organizations to control and secure their networks from the core-enabling them to increase efficiency and visibility, improve customer service, and meet compliance requirements.
In his session at 21st Cloud Expo, Michael Burley, a Senior Business Development Executive in IT Services at NetApp, will describe how NetApp designed a three-year program of work to migrate 25PB of a major telco's enterprise data to a new STaaS platform, and then secured a long-term contract to manage and operate the platform. This significant program blended the best of NetApp’s solutions and services capabilities to enable this telco’s successful adoption of private cloud storage and launchi...
In his general session at 21st Cloud Expo, Greg Dumas, Calligo’s Vice President and G.M. of US operations, will go over the new Global Data Protection Regulation and how Calligo can help business stay compliant in digitally globalized world. Greg Dumas is Calligo's Vice President and G.M. of US operations. Calligo is an established service provider that provides an innovative platform for trusted cloud solutions. Calligo’s customers are typically most concerned about GDPR compliance, applicatio...
Companies are harnessing data in ways we once associated with science fiction. Analysts have access to a plethora of visualization and reporting tools, but considering the vast amount of data businesses collect and limitations of CPUs, end users are forced to design their structures and systems with limitations. Until now. As the cloud toolkit to analyze data has evolved, GPUs have stepped in to massively parallel SQL, visualization and machine learning.
Join IBM November 1 at 21st Cloud Expo at the Santa Clara Convention Center in Santa Clara, CA, and learn how IBM Watson can bring cognitive services and AI to intelligent, unmanned systems. Cognitive analysis impacts today’s systems with unparalleled ability that were previously available only to manned, back-end operations. Thanks to cloud processing, IBM Watson can bring cognitive services and AI to intelligent, unmanned systems. Imagine a robot vacuum that becomes your personal assistant tha...
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...
The next XaaS is CICDaaS. Why? Because CICD saves developers a huge amount of time. CD is an especially great option for projects that require multiple and frequent contributions to be integrated. But… securing CICD best practices is an emerging, essential, yet little understood practice for DevOps teams and their Cloud Service Providers. The only way to get CICD to work in a highly secure environment takes collaboration, patience and persistence. Building CICD in the cloud requires rigorous a...
In his Opening Keynote at 21st Cloud Expo, John Considine, General Manager of IBM Cloud Infrastructure, will lead you through the exciting evolution of the cloud. He'll look at this major disruption from the perspective of technology, business models, and what this means for enterprises of all sizes. John Considine is General Manager of Cloud Infrastructure Services at IBM. In that role he is responsible for leading IBM’s public cloud infrastructure including strategy, development, and offering ...
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.
SYS-CON Events announced today that N3N will exhibit at SYS-CON's @ThingsExpo, which will take place on Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. N3N’s solutions increase the effectiveness of operations and control centers, increase the value of IoT investments, and facilitate real-time operational decision making. N3N enables operations teams with a four dimensional digital “big board” that consolidates real-time live video feeds alongside IoT sensor data a...
Gemini is Yahoo’s native and search advertising platform. To ensure the quality of a complex distributed system that spans multiple products and components and across various desktop websites and mobile app and web experiences – both Yahoo owned and operated and third-party syndication (supply), with complex interaction with more than a billion users and numerous advertisers globally (demand) – it becomes imperative to automate a set of end-to-end tests 24x7 to detect bugs and regression. In th...
In a recent survey, Sumo Logic surveyed 1,500 customers who employ cloud services such as Amazon Web Services (AWS), Microsoft Azure, and Google Cloud Platform (GCP). According to the survey, a quarter of the respondents have already deployed Docker containers and nearly as many (23 percent) are employing the AWS Lambda serverless computing framework. It’s clear: serverless is here to stay. The adoption does come with some needed changes, within both application development and operations. Tha...
SYS-CON Events announced today that Avere Systems, a leading provider of enterprise storage for the hybrid cloud, will exhibit at SYS-CON's 21st International Cloud Expo®, which will take place on Oct 31 - Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. Avere delivers a more modern architectural approach to storage that doesn't require the overprovisioning of storage capacity to achieve performance, overspending on expensive storage media for inactive data or the overbui...