SQL - improve NOT EXISTS query performance

sql sql-server performance where-clause not-exists
Melursus picture Melursus · Feb 16, 2009 · Viewed 42.6k times · Source

Is there a way I can improve this kind of SQL query performance: 

INSERT
INTO ...
WHERE NOT EXISTS(Validation...)

The problem is when I have many data in my table (like million of rows), the execution of the WHERE NOT EXISTS clause if very slow. I have to do this verification because I can't insert duplicated data.

I use SQLServer 2005

thx

Answer

cjk picture cjk · Feb 16, 2009

Make sure you are searching on indexed columns, with no manipulation of the data within those columns (like substring etc.)

Related questions

When should I use cross apply over inner join?

What is the main purpose of using CROSS APPLY? I have read (vaguely, through posts on the Internet) that cross apply can be more efficient when selecting over large data sets if you are partitioning. (Paging comes to mind) I …

Read More
How to check which locks are held on a table

How can we check which database locks are applied on which rows against a query batch? Any tool that highlights table row level locking in real time? DB: SQL Server 2005

Read More
SQL Server SELECT LAST N Rows

This is a known question but the best solution I've found is something like: SELECT TOP N * FROM MyTable ORDER BY Id DESC I've a table with lots of rows. It is not a posibility to use that query because …

Read More