Monthly Archives: September, 2018

SQL Performance Analyzer

10: How to make the most of a SQL Performance Analyzer?

Posted by MySQL NEWS No Comment yet

With the sudden increase in data and its importance, database management systems have become an essential part of any organization, big or small. And in most cases, a DBA is deployed to set-up and maintain a database system. Post initial setup, a DBA spends most of the time monitoring the database. He also constantly works on optimizing the throughput.

But, running an analysis can be tough whenever there’s a change in action or execution. This could include any amendments in the SQL database such as upgrade or addition of indexes. Moreover, these processes can impact your SQL database performance. With the release of Oracle Database 11g, the Real Application Testing option was introduced.

A key feature of the Real Application testing is the SQL Performance Analyzer (SPA). It brought relief to the DBAs with its ability to cut down on the time and effort required to identify slow queries.

 

How does the SQL Performance Analyzer Work?

 

An SQL Performance Analyzer offers a granular view of how a change impacts the performance. An SQL performance Analyzer executes the SQL query in isolation and offers the before and after results. It then generates a report with a proper detailing of the effects of the statement on the workload.

In case of a regressive SQL statement, the Analyzer identifies it and repairs it before the execution of the changes to the database such as migration, upgradation or new index introduction. Moreover, running SPA prior to running the database replay feature is one of the best practices which save a lot of time and effort. The main aim is to identify and repair any regression. For this purpose, SPAs make use of the already present SQL Tuning Sets (STS). An STS contains predefined statements, execution information, and execution plans from the workload.

 

Where is a SQL Performance Analyzer used?

 

Some common scenarios which need an SQL Performance Analyzer are mentioned below. These are scenarios when changes are made to the database, potentially affecting its performance.

 

Database upgradation: Whenever a newer version of the database is installed, it leads to the installation of a new optimizer. This affects the system performance. The analyzer is then used to create the before and after view, pointing out to any regressions. This enables the DBA to re-write the required SQL queries prior to the change.

Hardware and Software change: At times, the system is upgraded, meaning additional CPUs or memory. Additionally, new operating systems can be installed. These changes to the system affect the SQL performance. In such scenarios, an SQL Performance Analyzer plays a key role in identifying any deterioration.

Patch Deployment: If a patch to fix issues with the performance is being deployed, it is advisable to run it through the SQL performance analyzer. It checks your production for any errors which might cause regression post-implementation to the workload.

Optimizer Statistics: The plans generated by the system rely heavily on the optimizers’ decisions. These decisions, in return, are affected by Optimizer statistics. Whenever new statistics are collected and the existing refreshed, it can cause an impact on the SQL performance. Hence, preventing any regressions the analyzer keeps a check on the statistics.

Changes to Schema: Whenever a database undergoes schema changes such as addition or change in the index, it will inevitably affect the SQL performance. As this can lead to deterioration of performance, it is important for the SQL Performance Analyzer to keep a check on the same.

The SQL Performance Analyzer process

 

The execution process of an SPA is very simple. After capturing the SQL workload, and storing it in an SQL test set, the DBA will set up the test target. This is where the analyzer comes into the picture.

An SQL Performance Analyzer task is then assigned to the test system. Using this, the DBA creates a pre-change task followed by the implementation of the change. Once the change has been implemented, a post-change task is created.

The two tasks are compared for any regressive queries or changes which might affect the performance of the SQL system. The analysis will guide the DBA on the optimization process. The process is repeated until the required performance statistics are obtained.

 

I have been working for almost 2 decades on Database optimizations. I can help you optimize your database to make your application perform better and save costs. Contact me to know more about how I can help you.

ericvanier.com-sql-join

09: Introduction to the SQL Join concepts

Posted by MySQL NEWS No Comment yet

Introduction to the SQL Join concepts

 

As the importance of data continues to grow exponentially, so does the amount of data. And data management is the most important factor for any organization, big or small. Hence, a very important part of normal functioning is a database management system.

 

Owing to the ever-increasing amount of data, the databases have multiple tables. It is highly unlikely that the user will refer to a single table. Multiple tables need to be accessed. In a relational database management system, a simple way to access multiple tables is through the use of joins. Let’s look at joins and its types with a few examples.

What is a Join?

Whenever data from different tables need to be accessed, joins are used to link data between different rows based on related columns of a database. A simple example would be retrieving different details of an individual such as phone number, address, and age against the Name of an individual.

Why are Joins important?

 

With the multiple data fields and types, working with multiple tables is necessary. Without the access to joins, it will be extremely difficult to retrieve multiple data entries. They also form the basis for a relational database. For any relational DBMS, it is very important to relate tables. Joins is the perfect solution to that problem.

What are the different types of SQL Joins?

ericvanier.com-database-schema-sql-join

 

Joins have multiple types of functionalities. And hence, different situations call for different types of joins. There is majorly four type of joins which are used. Let’s have a look at these and understand.

1.    Inner Join

Consider a Venn diagram with two different sets, namely, A and B. The intersection between the two sets A and B (A ∩ B) is the condition of the join. An inner join will consist of the selection of this common area. In technical terms, the inner join will return all the rows that fulfill the condition of the join from two or more tables.

Syntax:

SELECT columns
FROM A
INNER JOIN B
ON A.columnname = B.columnname

2.    Left  SQL Join

Also known as the left outer join, this is the join which focuses on the left-hand table. Consider a Venn diagram with A and B. The left join will include the output from entire set A and the intersection of A and B (A ∩ B). In a DBMS, a left join will retrieve all rows from the left side table and only those from the right which fulfill the criteria of the join.

Syntax:

SELECT columns
FROM A
LEFT OUTER JOIN B
ON A.columnname = B.columnname

Left Outer join without intersection: Another subtype of the left join is where the rows returned include all rows from the left side table which also meet the condition of the SQL join but none of the rows from the right side table fulfilling the conditions of the joins.

Syntax:

SELECT columns
FROM A
LEFT OUTER JOIN B
ON A.columnname = B.columnname
WHERE B.columnname IS NULL

3.    Right SQL Join

Also known as right outer join, this conditional join focuses on the right side table. The right join returns all the rows from the right side table and the rows from the left side tables that fulfill the conditions of the join.

Syntax:

SELECT columns
FROM A
RIGHT OUTER JOIN B
ON A.columnname = B.columnname

Right Outer Join Without intersection: Similar to the left outer join without intersection, this join retrieves all the rows from tables on the right side except those fulfilling the condition of the joint from the left.

Syntax:

SELECT columns
FROM A
LEFT OUTER JOIN B
ON A.columnname = B.columnname
WHERE A.columnname IS NULL

4.  Full Join

Also known as Full outer join, retrieves all the values from both the tables mentioned in the ON condition. Rows from tables on either side are retrieved where the join condition is not met. With a Venn diagram, it includes data from the entire set of A and B include the unions. This includes all NULL Values.

Syntax:

SELECT columns
FROM A
FULL JOIN B
ON A.columnname = B.columnname

Full Join without intersection: Similar to a full SQL join, this retrieves all the rows from either side of the tables mentioned in the ON condition where the join condition is not met. But, this excludes all the NULL Values, unlike a FULL OUTER JOIN.

Syntax:

SELECT columns
FROM A
LEFT OUTER JOIN B
ON A.columnname = B.columnname
WHERE A.columnname IS NULL
OR B.columnname is NULL

Conclusion

The above article explained the different types of SQL joins used in the relational DBMS environment. But, it also emphasizes on the fact that joins are an essential part of the RDBMS as they enable the relational part of the system by interconnecting different tables and their data.

ericvanier.com MySQL Query

#8: How to Make the most out of a MySQL Query?

Posted by MySQL NEWS No Comment yet

Database Management systems have evolved over the years, yet it is crucial to stick to the basics to optimize their performance. From a developer’s perspective, one of the most critical components for an efficient, database-driven application is an SQL Query.

 

One ill-written SQL Query can not only slow down the entire database but also affect other components of the application. Good observation and statistical analysis are critical, but, MySQL comes loaded with specific tools and techniques to optimize SQL Queries and improve the overall time of the database. Let’s have a look at some of these MySQL query techniques.

 

Benchmarking

Assumptions are limited to certain conditions. Hence, numbers are required to support your results and give you clarity on the efficiency. Benchmarking points out the issues in the queries such as bottlenecks, and bad queries.

 

With the help of certain profiling tools such as SuperSmack and SysBench, it is possible to simulate high-stress situations and find faults in the database. This process creates the pathway to solution through simulation.

Profiling a MySQL Query

Post the simulation of high-stress situations through the benchmarking process, it is important to point out the cracks in the database. This can be done using profiling. The primary function of the profiling process is to point to the bottlenecks present in the CPU, memory, disk I/O, network, and/or any other component.

After the identification of slow queries, use of internal MySQL tools like EXPLAIN, STATUS, and SHOW will give you information regarding the use of resources and their side effects. This process helps identify the slow queries.

Schema Designing

This step is a pre-prep for query execution. A schema design needs to be completed before any query can even be written. When creating a schema, keeping in mind the details such as memory requirements and the type of data to be entered can help optimize MySQL query execution.

This process of normalizing a database, unlike a popular opinion of creating a complex schema, leads to minimizing redundancy of the data, hence speeding up the query. To make the most of your MySQL query, normalizing is essential. Moreover, if required, de-normalization can be processed later.

 

Indexing

A query is only as good as the choice of indices, so selecting the index is very important. An index, usually a B-tree, speeds up the process of a query. If a query is executed without any index, then the DBMS must scan every row of the table and compare it with the given condition. However, MySQL query can be optimized, by merely using an index.

During the process, one needs to maintain a balance. Not only do indexes take up memory space, but also affect the write performance as they have to be updated after every ‘write’ operation. Profiling helps to understand this balance, which will vary from situation to situation.

Partitioning the tables

For any database, it is essential to identify and differentiate between frequently used data and otherwise. A common example could be a blog where data such as ‘title of the topic’ might be used more regularly than other content. Now, based on this differentiation, if partitioning is performed the infrequently used data will take up less memory and help optimize the query executed.

When the data is kept in separate tables, the caching process is simplified. The reason being that MySQL will no longer have to change cache for the unaffected data.

MySQL queries might seem extremely simple or extremely complex, but some simple tips mentioned above can change the entire perspective and give a more efficient MySQL database.

 

I have almost 2 decades of experience in MySQL database tuning and performance. Get in touch with me and I can show you how you can improve your application performance.

ericvanier.com - SQL Database

#7: A brief understanding of SQL Database

Posted by MySQL NEWS No Comment yet

As we step into the era of the digital, the most essential component of the system is data. Also, over the years, the quantity and quality of data have been on the rise. A database is the collection of data to make its management easy.

Further, the database can be accessed using a Database management system (DBMS), a set of programs used to manipulated and edit the stored data. There are four types of database management systems, namely, hierarchal, Object-oriented, Network and relational.

 

What is SQL?

Structured Query Language (Pronounced “S-Q-L” or “See-Quel”) is the standardized programming language for relational DBMS. It enables all functionalities of a database such as allow, insert, merge, update and delete database records. SQL, besides, forms the basis for relational databases such as MySQL, MS SQL Server, Oracle, and Sybase. While SQL is not the only relational DBMS programming language, it the most popular.

 

History of SQL

SQL language was created in an attempt to manage a database called System R, developed back in the 1970s by IBM. The concept for RDBMS was given by Dr. Edgar F Codd which also formed the basis for the development. In 1979, Relational software (Now Oracle) released its version of SQL called Oracle V2. Almost four decades later, SQL serves various purposes for both personal and corporate use. It has also led to several open source platforms such as MySQL, PostgreSQL, and Firebird among others.

 

Why SQL Database?

What started off as a concept is now one of the most critical aspects of the database world. SQL databases are preferred for many reasons. Some of them are:

 

  1. It forms the pathway to relational database management systems and provides access to the same.
  2. It offers the ability to embed within other similar languages with the help of SQL pre-compilers, libraries, and modules.
  3. It enables the user to add data description along with addition and manipulation into the database.
  4. SQL database allows the creation of the view, procedure, and function. The user also gets control over permissions on them.

 

What is the SQL standard?

SQL complies with American National Standards Institute (ANSI) and has seen multiple versions over the years. The SQL standard is not just for basic commands anymore but has evolved to support newer functionality such as recursive queries, triggers, XML, standard sequencing, and regular expression matching to name a few.

While some of them are common, SQL standards’ large volume makes it impossible to implement all the standards in DBMS such as MySQL and PostgreSQL. This is also the root cause for the difference between different SQL implementations, causing incompatibility between these platforms.

 

The elements of SQL

The SQL language, based on certain features is imbibed in respective DBMS. For the ease of developers, the necessary commands can be executed through the Command Line interface (CLI). The elements include:

Clause:

Clauses are the components of statements and queries which are used to define conditions for them.

Expressions:

The result of an expression is the production of scalar values and/or tables with defined rows and columns.

Queries:

Queries are used for the retrieval of specific data based on certain conditions.

Statements:

Statements are the primary control system for multiple SQL components such as program flows, connections, diagnostics, and connections. The statements carry queries to the SQL server and get the reply in return.

Predicates:  

Predicates can influence the flow of the program in addition to limiting the conditions on the statements and queries.

 

Conclusion

With brief insight into the SQL database, it can be seen how the language has evolved since its early days. It should also be noted that with the increasing impact of data on our daily transactions, SQL database will further up its level to match the daily requirements giving support to even more relational database management systems.

 

As an SQL Database expert, I help businesses to solve issues and fortify their application database. Get in touch with me and I can surely help you improve your database performance.