SQL Database Performance

This article will help you to increase your database’s performance by improving the SQL. In theory, performance tuning should be done by database administrators (DBAs) but it also the responsibility of developers to make a good code. So in this article, we will dwell on the most important and simple methods of increasing the performance of your database by improving the SQL.

1. Improve Indexes

Creating useful indexes is one of the best ways to achieve better performance. Useful indexes help you to find data with fewer I/O operations and less system resource usage.

Note that more indexes mean that SELECT queries will potentially run faster. However, DML (INSERT, UPDATE, and DELETE) operations will be slowed down significantly because indexes should be maintained with each operation. Thus, if your application uses SELECT statements mostly, more indexes can be helpful, but if it’s DML operations the main thing, limit the number of indexes you create.

Create a primary key on each table you create, make it the clustered index (note that if you set the primary key in Enterprise Manager it will cluster it by default). Create an index on foreign-key columns.

2. Remove unnecessary indexes

You must consider that index maintenance requires lots of CPU resources and I/O.

The good thing is that MS SQL Server, for example, provides its users dm_db_index_usage_stats DMV for index usage statistics. You can use the following code to get usage statistics for different indexes. It would be better to remove indexes if they are not used or used rarely – it will increase the MS SQL Server performance.

SELECT

OBJECT_NAME(IUS.[OBJECT_ID]) AS [OBJECT NAME],

DB_NAME(IUS.database_id) AS [DATABASE NAME],

I.[NAME] AS [INDEX NAME],

USER_SEEKS,

USER_SCANS,

USER_LOOKUPS,

USER_UPDATES

FROM SYS.DM_DB_INDEX_USAGE_STATS AS IUS

INNER JOIN SYS.INDEXES AS I

ON I.[OBJECT_ID] = IUS.[OBJECT_ID]

AND I.INDEX_ID = IUS.INDEX_ID

3. Avoid loops in your code

In many cases you can simplify your code. Take this one for example:

for (int i = 0; i < 1000; i++)

{

    SqlCommand cmd = new SqlCommand(“INSERT INTO TBL (A,B,C) VALUES…”);

    cmd.ExecuteNonQuery();

}

In this example 1000 queries are querying you database in sequence so it requires a lot of system resources. But it’s easy to avoid such loops by using INSERT and UPDATE statements for example:

INSERT INTO TableName (A,B,C) VALUES (1,2,3),(4,5,6),(7,8,9) — SQL SERVER 2008

INSERT INTO TableName (A,B,C) SELECT 1,2,3 UNION ALL SELECT 4,5,6 — SQL SERVER 2005

UPDATE TableName SET A = CASE B

        WHEN 1 THEN ‘NEW VALUE’

        WHEN 2 THEN ‘NEW VALUE 2’

        WHEN 3 THEN ‘NEW VALUE 3’

    END

WHERE B in (1,2,3)

Look for every possible way to reduce the number of round trips to the server. Returning multiple resultsets is one way to do this.

4. Avoid Correlated subqueries

A correlated subquery is a subquery (a query nested inside another query) that uses values from the outer query. Because the subquery may be evaluated once for each row processed by the outer query, it can be inefficient.

Example:

SELECT c.Name,

       c.City,

       (SELECT CompanyName FROM Company WHERE ID = c.CompanyID) AS CompanyName

FROM Customer c

IN this example the problem is that the inner query (SELECT CompanyName FROM Company WHERE ID = c.CompanyID) is called for each row returned by the other query (SELECT c.Name)

A more efficient query constructed using JOIN:
SELECT c.Name,

       c.City,

       co.CompanyName

FROM Customer c

        LEFT JOIN Company co

                ON c.CompanyID = co.CompanyID

5. Use SELECTs instead of SELECT *

When using Select * (select all) query you query all data from the chosen table including the unnecessary one. So carefully define SELECTs to query only the data you need

Inefficient:

SELECT *
FROM public.customers

Efficient:

SELECT FirstName, LastName, Address, ZIP
FROM public.customers

6. Avoid SELECT DISTINCT

This query works by grouping all fields in a query to create distinct results. Though this method could be handy, it requires a lot of system resources.

Bad example:

SELECT DISTINCT FirstName, LastName, State
FROM public.customers

An efficient and accurate query:

SELECT FirstName, LastName, State
FROM public.customers

Use Wildcards at the End of a Phrase Only

When searching plaintext data, wildcards help you to create the widest search possible. However, this type of search is also the most inefficient search.

Bad example:

SELECT City FROM Customers
WHERE City LIKE ‘%Char%’

A more efficient query:

SELECT City FROM Customers
WHERE City LIKE ‘Char%’

7. Avoid temp tables

Avoid temp tables, but if it is not appropriate, create it explicitly using Create Table #temp. You can also use a subquery as an alternative.

—

Article by DataSunrise – Database Security Software.