Performance tuning of query

what is performance tuning in sql
sql query performance tuning tips
sql performance tuning interview questions
how to optimize sql query with multiple joins
how to check query performance in sql server
sql performance tuning oracle
sql query optimization tool
performance tuning in database

I have a query which takes a long time to run. It is probably because I used too many isnulls in the join condition. How can I optimise it by removing the isnull?

Is there any alternate way without updating the table? The query is given below:

 select pos.C_id
    ,pos.s_id
    ,pos.A_id
    ,pos.Ad_id
    ,pos.Pr_id
    ,pos.prog_id
    ,pos.port_id
    ,pos.o_type
    ,pos.o_id
    ,pos.s_id
    ,pos.c_id
    ,pos.s_type_id
    ,pos.s_type
    ,pos.e_date
    ,pos.mv
    ,0 is_pub
    , 1 is_adj           
    ,pos.is_unsup
    ,getdate() date
    ,getdate() timestamp
    from #temp pos
    left join acc c with(nolock) ON pos._id = c.c_id
    AND pos.account_id = c.account_id
    AND isnull(pos.Pr_id,0) = isnull(c.pr_id,0)
    AND isnull(pos.prog_id,0) = isnull(c.prog_id,0)
    AND isnull(pos.port_id,0) = isnull(c.port_id,0)
    and isnull(pos.style_type_id,0)=isnull(c.s_type_id,0)
    AND pos.s_id = c._id
    AND pos.c_id = c.c_id
    AND pos.s_type = c.s_type
    AND pos.is_unsup = c.is_uns
    AND pos.is_pub = 1
    where c.a_id is null

try using

AND (pos.Pr_id = c.pr_id OR (pos.Pr_id IS NULL AND c.pr_id IS NULL))

instead of

AND ISNULL(pos.Pr_id,0) = ISNULL(c.pr_id,0)

IS NULL is more efficient than ISNULL

Performance Tuning SQL Queries, This lesson of the SQL tutorial for data analysis covers how to conduct SQL performance tuning by reducing table size, simplifying joins, and the EXPLAIN  SQL Server query performance tuning is considered as a top priority and an endless battle for database administrators to achieve the best performance for their managed systems, with the least resources consumption.

What about the query below

 select pos.C_id
    ,pos.s_id
    ,pos.A_id
    ,pos.Ad_id
    ,pos.Pr_id
    ,pos.prog_id
    ,pos.port_id
    ,pos.o_type
    ,pos.o_id
    ,pos.s_id
    ,pos.c_id
    ,pos.s_type_id
    ,pos.s_type
    ,pos.e_date
    ,pos.mv
    ,0 is_pub
    , 1 is_adj           
    ,pos.is_unsup
    ,getdate() date
    ,getdate() timestamp
    from #temp pos
    left join acc c with(nolock) ON pos._id = c.c_id
    AND pos.account_id = c.account_id
    AND pos.Pr_id = c.pr_id
    AND pos.prog_id = c.prog_id
    AND pos.port_id = c.port_id
    and pos.style_type_id=c.s_type_id
    AND pos.s_id = c._id
    AND pos.c_id = c.c_id
    AND pos.s_type = c.s_type
    AND pos.is_unsup = c.is_uns
    AND pos.is_pub = 1
    where c.a_id is null
union all
 select pos.C_id
    ,pos.s_id
    ,pos.A_id
    ,pos.Ad_id
    ,pos.Pr_id
    ,pos.prog_id
    ,pos.port_id
    ,pos.o_type
    ,pos.o_id
    ,pos.s_id
    ,pos.c_id
    ,pos.s_type_id
    ,pos.s_type
    ,pos.e_date
    ,pos.mv
    ,0 is_pub
    , 1 is_adj           
    ,pos.is_unsup
    ,getdate() date
    ,getdate() timestamp
    from #temp pos
    left join acc c with(nolock) ON pos._id = c.c_id
    AND pos.account_id = c.account_id
    AND pos.s_id = c._id
    AND pos.c_id = c.c_id
    AND pos.s_type = c.s_type
    AND pos.is_unsup = c.is_uns
    AND pos.is_pub = 1
    where c.a_id is null
    AND pos.Pr_id is null AND  c.pr_id is null
    AND pos.prog_id is null AND  c.prog_id is null
    AND pos.port_id is null AND  c.port_id is null
    and pos.style_type_id is null AND c.s_type_id is null

SQL Query Tuning or Query Optimization, How do you make database queries faster or optimize the queries? Queries and Performance Tuning. This lesson provides an overview of how Greenplum Database processes queries. Understanding this process can be useful when writing and tuning queries. Users issue queries to Greenplum Database as they would to any database management system.

You really need to set a clustered index on your temp table #temp, if not present sql-server will provide a default hash-based index for joins, and it is not performant for large tables.

If any of all that id's columns you have in your #temp table is a unique key you really need to create a clustered index on it:

CREATE CLUSTERED INDEX cx_temp ON #temp (YourIDColumn);

If you have not a key column in #temp add an identity primary key to it in this way:

1) if you use SELECT INTO approach to create #temp, change

select *
into #temp
from YourQuery

in

select IDENTITY (int, 1,1) ThisIsTheKey, *
into #temp
from YourQuery

-- and then add the index
CREATE CLUSTERED INDEX cx_temp ON #temp (ThisIsTheKey);

1) if you use CREATE TABLE + INSERT INTO approach, simply add the column like this:

CREATE TABLE #TEMP (
    ThisIsTheKey INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    ..
    ..
    -- ALL YOUR OTHER COLUMNS
    ..
    ..
)

Don't rely on covering non clustered indexes because the optimizer will likely not use them because you are selecting too many columns.

First test with the clustered index, then if you need further optimization you can try to add some specific non clustered index.

SQL Performance Tuning: 7 Practical Tips for Developers, In a nutshell, SQL performance tuning consists of making queries of a relation database run as fast as possible. As you'll see in this post, SQL 

Tips for SQL Database Tuning and Performance, Performance tuning SQL Server databases can be tough: Sometimes the smallest change has the biggest impact. Learn the benefits of SQL query tuning and 

SQL Performance Tuning: 5 Best Tips for Developers, So what can you do to optimize your SQL queries? Create indexes, but do it wisely. Indexing is probably the most important part of the query 

Performance Tuning Your SQL Queries like a Pro (7 Tips), Here is the list of queries which we use reqularly and how these sql queries can be optimized for better performance. SQL Tuning/SQL Optimization Techniques: 1) 

Comments
  • Check your execution plan and its suggestions. May be creating a covering index could help in your case