Posts tagged " SQL Database "

ericvanier.com SQL Performance Tuning

11: Getting Started with SQL Performance Tuning

Posted by MySQL NEWS No Comment yet

Sometimes you might have faced the issues of slow SQL performance due to some queries. This slowdown might have also impacted your operations to a major extent. And every time you might have felt the need to improve the performance of your SQL database. Well, you can easily improve the performance of your SQL database by tuning it.

 

What is SQL Performance Tuning?

SQL performance tuning, are several techniques used to ensure that the queries of the SQL database run in the fastest possible span to give maximum productivity.

 

Why is SQL Performance Tuning important?

While the smaller databases might not find SQL tuning significant, but when the databases are very huge ranging up to some Terabytes (TBs), then SQL performance tuning plays a major role in cutting down the query runtime resulting in a much-improved efficiency, making operations a lot faster than before.

Thus, for better performing fast operations, SQL performance tuning of databases is important.

 

How to do SQL Performance Tuning?

Here are a few ways you can use for doing SQL performance tuning of your database to it speed up:

  • Ensure a database server health check-up using DMVs

Use DMVs (Dynamic Management Views), to ensure server health and find the areas of slowdown. It gives the analytics and information about the active sessions and connections, letting you know the objects which consume most memory causing a slowdown.

 

You can use the below query to know the memory consumption of the given object, to identify areas of slowdown.

 

Select * from $System.discover_object_memory_usage

 

  • Avoid using SELECT * instead use SELECT fields

Using SELECT * (Select All) means calling of all the available data in the given database, which is often a stringent and more time-consuming process, often calling fields which are actually not required. Thereby, causing inefficiency and consuming more time.

 

If you use SELECT fields query that would mean you call only the required fields. This helps in significant optimization especially when the databases have a large number of rows and fields.

 

Avoid

SELECT *
FROM Users

 

Instead, use

SELECT FirstName, LastName, Address, City, State, Zip
FROM Users

 

  • Avoid using SELECT DISTINCT instead use SELECT for more fields

If you are using SELECT DISTINCT for removing duplicate queries, a lot of processing is required to call the query. As the SELECT DISTINCT query GROUPs all fields to create distinct results, it requires more processing time and can cause a slowdown when the database is significantly large.

 

Instead, if you opt to remove duplicate fields by calling SELECT for more fields, you tend to get unduplicated records without much processing, optimizing your database performance.

 

Avoid

SELECT DISTINCT FirstName, LastName, State
FROM Users

 

Instead, use

SELECT FirstName, LastName, Address, City, State, Zip
FROM Users

 

  • Prefer using Parameterized Queries

Using Parameterized Queries can improve the performance of the database, as they compile the query only once while executing the compiled plan multiple times. This can save time for recompiling each time, thereby improving performance.

 

Prefer

SELECT “User ID” FROM User WHERE “Sales ID” = ?

 

  • Prefer INNER JOIN to create joins over WHERE

Using WHERE to create joins result in a Cartesian of CROSS JOIN, where all the possible combinations of variables are created, causing a lot of unnecessary processing. This impacts more especially when the number of variables is more, like that in bigger databases.

 

While using INNER JOIN can be used to optimize the query as it does not create a Cartesian join.

Avoid

SELECT Users.UserID, Users.Name, Sales.LastSaleDate
FROM Users, Sales
WHERE Users.UserID = Sales.UserID4

 

Instead, use

SELECT Users.UserID, Users.Name, Sales.LastSaleDate
FROM Users
INNER JOIN Sales
ON Users.UserID = Sales.UserID

 

 

  • Prefer using LIMIT for sample queries

You can limit the number of results when using sample queries. Using LIMIT will run only the given number of outcomes, instead of calling the complete data.

 

Prefer

SELECT FirstName, LastName, Address, City, State, Zip
FROM Users

LIMIT 20

 

The Bottom Line

SQL Performance Tuning is very helpful in optimizing database performance significantly by decreasing the processing time, especially for large databases, improving overall efficiency.

If you are stuck with performance improvement, get in touch with me. I will help you unlock the true performance of your database.

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 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.