Posts Tagged ‘SQL Server’

Newer Entries »

Few Smart Database Indexing Tips To Boost Application Performance

Wednesday, June 2nd, 2010

Indexes play key role in performance and scalability of any database intensive applications. Here are few tips to smartly use indexes to get the best out of it.

  1. Decide Best Candidates for Index. The column(s) which are most frequently used to filter records (WHERE clause and JOIN clause), group records (GROUP BY clause) and/or sort records (ORDER BY) are best candidates for index creating. Analyze your queries very attentively and avoid creating indexes which are seldom used.
    You should also minimize the number of indexes for the tables which has data being frequently updated/deleted/inserted to reduce index maintenance cost during each such operation.
  2. If you create a composite (multi-column) index, try to order the columns in the key as to enhance selectivity
    1. With the most selective columns to the left most of the key.
    2. The WHERE clauses of the frequently used queries match the column(s) that are leftmost in the index.

    The order of the columns in a multi column index plays a key role during query execution. The composite index used only when the left most column is included in WHERE clause of the query otherwise the index will not be hit and instead an expensive full table scan will be performed.
    For example, if you create composite index such as “Branch, Department”, then the query with the WHERE clause such as “WHERE Branch = ‘New York’” will use the index, but the query with the WHERE clause such as “WHERE Department = ‘Finance’” will not use the index.

  3. Every table should have a clustered index. Cluster index ensures that data rows stored in particular order only. If you do not create a cluster index for a table, the data rows will not be stored in any particular order, and ‘Heap’ will be used. Every time data is inserted into this table, the row will be added to the last block of the table. When many rows will be added concurrently, a “hot spot” can occur in absence of proper locking. To avoid “hot spot” and improve concurrency, you should create a clustered index for each table.
  4. Carefully create clustered index instead of non clustered to increase performance of the queries that return a range of values and for the queries that contain the GROUP BY or ORDER BY clauses and return the sort results. However, make sure to NOT create clustered index on column(s) that is updated very frequently. Because clustered index ensures that data is stored in order of key to the index, updating of key data will cause lot of index maintenance which is not desirable for performance centric application.You should choose column(s) for clustered index very very carefully because every table can have only one clustered index. First you analyze all your queries and select optimum candidate which is best suitable for the above mentioned criteria to create the clustered index.

By smartly placing indexes you can achieve great response times to your database applications and offer better user experience.

Happy Indexing!

Tags: ,
Posted in Technology | 11 Comments »

3 Little Known (Yet Very Effective) SQL Performance Optimization Tips

Monday, May 10th, 2010

As a software engineer, most of us know how to write SQL Queries, but many of us DO NOT know how to write an optimized SQL query. Query optimization is a kind of ignored subject. In majority of the cases the developers don’t take into consideration if they are writing an optimized query or not, they just write them.

Writing an optimized query does not always require special skills or techniques but a few of the basic things. Some of you might be surprised if I say that using specific names of the columns instead of * makes your query work faster. Let’s take few examples:

Tip 1: Use of Select *

There could be times when you need to work on only two columns from a table, but you may query all the columns by specifying SELECT *. This is done just because this is easy to do. The basic idea here is to limit the data to be returned by the database query.

For example when you need only name and salary columns from the database table,

SELECT * FROM employees WHERE deptno=10

would be less efficient than

SELECT name, salary FROM employees WHERE deptno=10

Tip 2: Use of Having clause

A common mistake would be something like below:

SELECT deptno, count(deptno) FROM employees GROUP BY deptno HAVING deptno !=10 AND deptno != 20

In the above query first we are grouping all the deptno’s and the applying the having filter. The correct way of writing this would be to first eliminate all the not required deptno and then do the grouping – as done correctly in the below query:

SELECT deptno, count(deptno) FROM employees WHERE deptno !=10 AND deptno !=20 GROUP BY deptno

Tip 3: Use of UNION ALL

Use UNION ALL instead of UNION. UNION checks for the duplicate rows and then eliminates them which make it slower as compared to UNION ALL. This is another very common mistake. In most of cases when we are making UNION of two or more statements we just use UNION irrespective of the fact we require it or not.

As a software engineer, your main focus should be to serve your client. And just by optimizing performance of even a minor query, you are serving your client better. And that’s what matters.

Tags:
Posted in Technology | 4 Comments »

Newer Entries »