Posted on

How to index SQL with aggregate function SQL for Oracle?

Here the following is an example SQL shows you that select the maximum emp_address which is not indexed in the EMPLOYEE table with 3 million records, the emp_grade is an indexed column.

select max(emp_address) from employee a
where emp_grade<4000

As 80% of the EMPLOYEE table’s records will be retrieved to examine the maximum emp_address string. The query plan of this SQL shows a Table Access Full on EMPLOYEE table is reasonable.

How many ways to build an index to improve this SQL?
Although it is simple SQL, there are still 3 ways to build an index to improve this SQL, the following are the possible indexes that can be built for the SQL, the first one is a single column index and the 2 and 3 are the composite index with a different order.
1. EMP_ADDRESS
2. EMP_GRADE, EMP_ADDRESS
3. EMP_ADDRESS, EMP_GRADE

Most people may use the EMP_ADDRESS as the first choice to improve this SQL, let’s see what the query plan is if we build a virtual index for the EMP_ADDRESS column in the following, you can see the estimated cost is reduced by almost half, but this query plan is finally not being used after the physical index is built for benchmarking due to actual statistics is collected.

The following query shows the EMP_ADDRESS index is not used and the query plan is the same as the original SQL without any new index built.

Let’s try the second composite index (EMP_GRADE, EMP_ADDRESS), the new query plan shows an Index Fast Full Scan of this index, it is a reasonable plan which no table’s data is needed to retrieve. So, the execution time is reduced from 16.83 seconds to 3.89 seconds.

Let’s test the last composite index (EMP_ADDRESS, EMP_GRADE) that EMP_ADDRESS is placed as the first column in the composite index, it creates a new query plan that shows an extra FIRST ROW operation for the INDEX FULL SCAN (MIN/MAX), it highly reduces the execution time from 16.83 seconds to 0.08 seconds.

So, indexing sometimes is an art that needs you to pay more attention to it, some potential solutions may perform excess your expectation.

The best index solution is now more than 200 times better than the original SQL without index, this kind of index recommendation can be achieved by Tosska SQL Tuning Expert for Oracle automatically.

https://tosska.com/tosska-sql-tuning-expert-pro-tse-pro-for-oracle/

Posted on

Transferring Data in SQL Server with an Eye on Performance

improve performance of sql query

A lot of database professionals often need to archive older data in SQL Server by transferring it from one table to another. There are multiple ways to achieve the transfer, the most useful of which we will discuss in this blog. We will also provide tips to ensure the performance of the database doesn’t get affected as these approaches are carried out.

Different Methods to Move Data from One Table to Another

Consider the following techniques that various DBAs take when they have to take data from a table to add to another table along with some ways to improve performance of SQL query while using them:

  1. Insert data with the INSERT INTO command – The INSERT INTO query is one of the basic methods of moving data from table 1 to table 2. You can help decrease the time it takes to enter information using this method. If the database is running under the full recovery model, just change it to the bulk-logged model. Doing this saves execution time as it skips over complete logging of bulk operations. The following query should help with this:

ALTER DATABASE <database name> SET RECOVERY <BULK_LOGGED>

Once you switch to the bulk-logged recovery model, you will have to use a truncate statement to flush table 2 (destination). You can carry out the same script you were using to transfer data after this.

  1. Use the SELECT INTO query – Using the SELECT INTO rather than the INSERT INTO command can prove useful in some cases. However, the benefits are significant when the recovery model is bulk-logged due to the reason mentioned above. Although users lack the ability to place the data in an existing table, SQL Server brought with it a feature to make things easier. It essentially enables them to pick the filegroup where they want to create a table.
  1. INSERT INTO query + Tab lock hint – Using both in combination has been known to provide better database performance. To achieve this, you will have to use TABLOCK for table 2. If the destination table is without a clustered index or other constraints, that data will remain as a heap. It helps to use the TABLOCK hint for the destination table during data insertion into a heap using the INSERT INTO statement. Doing this enhances query logging and locking since a shared lock is placed on the whole table rather than every row or page.
  2. Adding data using the SWITCH TO query – You can also try moving the data with the help of the SWITCH TO command. Although this query typically finds its use while transferring information between partitions among separate tables, it can help here as well. How? By moving data from one partition to the next using the ALTER TABLE command. If there are no allocated partitions, the data will transfer through tables instead. Before you begin data insertion, make sure you disable any constraints or indexes that exist on the table. It is better to enable constraints and rebuild indexes after insertion from a performance perspective.

Tips for Enhancing Performance During Data Transfer and Insertion

  • Reduce IO lag – Latency can negatively impact the process of writing database files on disk. You can decrease latency and bottlenecks using SSD drives that are comparatively better than SATA or SCSI drives.
  • Maintain Robust Server Infrastructure – The system needs to be properly built to ensure competent performance for various database operations. The greater the pressure on the resources, the greater the effect on performance.
  • Follow ACID Properties – ACID properties make sure each transaction contains certain properties when it gets processed. In the case of data insertion, the isolation factor is also important to consider because the values have another source. Here, the statements should contain the suitable isolation level to maintain integrity within the database.
  • Database Settings – One of the best ways to achieve improved outcomes is to maintain the right database configuration. This is because the settings can have a significant effect on performance. For instance, the location of the database files on the disk along with TempDB settings.

These are the various ways in which you can gain better performance at the query, trace, and constraint levels along with additions that can improve the execution of insert operations.

Posted on

Backup and Recovery in SQL Server: Understanding the Basics (Part 1)

SQL Server

Taking regular database backups is essential to provide assistance to businesses recovering from an unplanned event. They enable data restoration from when it was previously saved. Moreover, keeping a copy of the information separately is also vital for protection against corruption or data loss.

In this 2-part series, we’ll cover

In this guide, we will discuss SQL Server backup types, recovery models, as well as best practices that you should take into account when putting together your backup strategy.

Various Types of Backups in SQL Server

There are different backup types in SQL Server that users have to consider when constructing their backup strategy. Here, we will briefly explain each of these variants and how they work. Microsoft SQL Server supports the following backup forms:

Full Backup: This implies a complete backup of the SQL Server database. It covers every object in the database. It is the most popular and recommended backup type as it enables users to restore their database to the exact same version it was when the backup was taken.

Differential Backup: It backs up only the data that has undergone changes since you created the last full backup. This is why it takes lesser time than a full backup. However, creating several differential backups over time may eventually lead to greater storage requirements.

The size increases because of the addition of changed data in each subsequent backup, and it can grow to become as large as the full backup. Thus, it is important to schedule new full backups (even if they’re less frequent) to avoid extended backup times and oversized differential backups. Otherwise, these excessively-large backups will cause a negative impact on database performance, requiring optimization.

Transaction Log Backup: It is a form of incremental backup. It backs up the transaction log containing the modifications made since the last t-log backup. Log backups can take place quite frequently – even once every few minutes. This enables users to carry out point-in-time restores to reduce data loss.

File/filegroup Backup: This type of backup involves making separate copies of individual data files or files from a filegroup. Users can backup and restore each database file separately as well.

Copy-only Backup: This is a type of SQL Server backup that doesn’t depend on the sequence of traditional backups. When you create a backup, it generally makes changes to the database.  These impact the manner in which these backups will be restored in the future. On the other hand, it may sometimes be more useful to create backups that don’t affect the comprehensive backup and restore methods for the entire database.  

Creating Backups for SQL Server Databases: What Experts Suggest

Seasoned database professionals recommend a few things when it comes to creating backups of the database. For starters, they suggest using the full recovery method on a daily basis. However, you may create these on alternate days and differential backups every day if the database size exceeds three GB.

Many also advise making daily t-log backups once you’ve created a full or differential backup. You may even schedule one for every four hours, and avoid truncating one manually. If disaster strikes, it’s better to create a backup of the t-log that’s active at the moment. In case there isn’t any t-log backup available, you’ll be unable to restore database activities past the latest available t-log backup. This is likely to hinder point-in-time recovery as well.