An SQL query is a non-procedural process. This means that one query may result in many thousands of rows being processed to give you just a few rows in the results table. With such vast amounts of data being processed, anything that can be done to make the process faster is called performance tuning.SQL tuning is the process of arranging the SQL statement for the most efficient access of the database. This includes such things as the order in the FROM clause, WHERE clause, use of an INDEX, etc.

Database tuning is the process of configuring the database for the most efficient access. This includes database design, layout of tables, creation or dropping of indexes, views, disk allocation, memory usage, etc.

To do SQL tuning, start with proper SQL formatting of the statement. See SQL Formatting under SQL Basics. While this will not improve performance, it is necessary for easy human comprehension of the statement.
> Many RDBMS systems have tools for evaluating an SQL statement.

> DB2 and Oracle have a tool called EXPLAIN PLAN.

Concurrency is when two users try to update the same data at nearly the same time.

 

RDBMS systems use locking to prevent users from reading data that is being changed by other users. This is why a SELECT statement may need the option FOR UPDATE. The locking ensures transaction integrity and database consistency. Concurrency transparency means that data locking makes each transaction appear as though it is the only transaction. Sometimes each user is trying to access data locked by the other user causing a deadlock. Most RDBMS systems unlock one user, process that user, and then unlock the other user.

A transaction needs to have a starting and ending point. The starting point can be implied, for example, by starting a host program, or it can be explicit with a START TRANSACTION or a BEGIN TRANSACTION. The ending point is explicit with either a COMMIT or ROLLBACK command.

SQL Server starts with BEGIN TRANSACTION.

     BEGIN TRANSACTION;
       INSERT INTO MANAGER_TBL
         ('STOR_NO', 'MGR_NO', 'MGR_NAME')
           SELECT STOR_ID, EMPL_ID, EMPL_NAME
             FROM EMPLOYEE_TBL
            WHERE TITLE = 'STORE MANAGER';
     COMMIT;

SQL:1999, MySQL, PostgreSQL, and SQL Server switched to the new SQL standard START TRANSACTION:

     START TRANSACTION;
       INSERT INTO MANAGER_TBL
         ('STOR_NO', 'MGR_NO', 'MGR_NAME')
           SELECT STOR_ID, EMPL_ID, EMPL_NAME
             FROM EMPLOYEE_TBL
            WHERE TITLE = 'STORE MANAGER';
     COMMIT;

> Access transaction control starts with BeginTrans and ends with CommitTrans or RollbackTrans.

> DB2 and ORACLE always have an implied starting point, so they do not need an explicit starting statement.

A transaction is a sequence of one or more SQL statements that are to be executed together as a group. If any part of the transaction’s statements fails, then all actions taken by the statements within the transaction must be reversed. It is an all-or-nothing requirement.If all statements in a single transaction are successful, then all actions taken are made permanent with a COMMIT command. On the other hand, if any of the statements within a transaction fail, then all actions must be reversed with a ROLLBACK command.

An example of a transaction in accounting would be a set of debit and credit entries where the amount of the debit entry exactly matches the amount of the credit entry.

It is your responsability to start and end a transaction at a point that creates a logical transaction. It should be as small as possible while maintaining a logical unit.

See the following EXAMPLEs.