By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,101 Members | 1,416 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,101 IT Pros & Developers. It's quick & easy.

Non-clustered, Non-unique index performance

P: 1
I have to regularly import records into a table in the database that also contains records that were created internally. Those that are imported have an ImportRecordID column that I use to ensure that I don't import a record twice. In the import code, I query the table on the ImportRecordID column, and if I get no results, I go ahead and add the new record. Very straightforward.

The problem is that I noticed that when I removed this "existing record" check (SELECT query), the import sped up by nearly 10x. Why should a simple SELECT take longer than 3 or 4 INSERTs? I asked myself.

So I added an Index on the ImportRecordID. It had to be non-Unique because there are many records in the table for which this value is NULL. I looked at the execution plan in Management Studio both before and after adding the index, and it assured me that I would get massive performance gains. Great!

Except that I didn't get massive performance gains when I actually ran the import. The performance on that SELECT was exactly the same. Why? How can I tell that the new Index is actually working?
Mar 26 '08 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.