4

I am running a Simple Update statement. Its taking too long to execute. Here are the Update and index details.

The default value of Exported column is 0

UPDATE PAR_ITM SET exported = -1 WHERE exported < 1

Indexes:

CREATE NONCLUSTERED INDEX [IX_PAR_ITM_Exported_1]
ON [dbo].[PAR_ITM] ([exported] ASC)
WITH (
  PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = ON, SORT_IN_TEMPDB = OFF,
  IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON,
  ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80
) ON [PRIMARY]
Johan
  • 74,508
  • 24
  • 191
  • 319
Prateek
  • 231
  • 3
  • 6
  • 12

5 Answers5

3

So you are setting exported to -1 where exported already equals -1. Maybe changing your were clause to WHERE exported = 0 would hit fewer rows?

JBrooks
  • 9,901
  • 2
  • 28
  • 32
3

Cardinality is low
You are suffering for a problem called low cardinality.
See wikipedia.

The problem
In short every database (I know of) will refuse to use an index on a low cardinality column.

There's more
A good database will also refuse to use an index if it guesses that many rows will be hit by the select (low cardinality of the select), even if the column itself has lots of different values (high cardinality of the column)

Why will --insert DB here-- not use an index?
Here's the problem.
If more than 50% (differs per database) of all rows in a database have the same value, the DB will not use the index, because it would be pointless to use an index.

It is pointless to do 2 reads for most of the rows (read 1 for the index, read 2 for the table), if you can do one read (on the table itself).
The DB has to read most rows anyway, so it just goes ahead and reads them.
The fact that the DB has to fall back on using its slowest access mechanism (full table scan) is what's causing your slowness.

The solution
Increase the cardinality of the column or increase the cardinality of the select.
In other works make sure you select less than 50% of all rows, and make sure than SQL-server knows (or can guess) this.

How does the DB know the cardinality of a column?
A good DB keeps stats on tables and columns whilst doing selects/updates/inserts. In this manner it has the info it needs to make informed decisions.

Will forcing the use of an index speed up the query?
No, it will slow it down.

SO links
MySQL: low cardinality/selectivity columns = how to index?
Indexing & alternatives for low-selectivity columns

Community
  • 1
  • 1
Johan
  • 74,508
  • 24
  • 191
  • 319
  • 1
    The OP has told us precisely nothing about how selective the predicate is. – Martin Smith Sep 02 '11 at 15:55
  • @Martin Smith, the statement: `UPDATE PAR_ITM SET exported = -1 WHERE exported < 1` is a pretty strong hint the update query is hitting a large percentage of the rows every time. – Johan Sep 02 '11 at 15:57
1

Might be helpful to know how many records are included in this:

Select Count(*)
FROM PAR_ITM 
WHERE exported < 1

Basically how many rows are being updated. Granted, even a million rows would be done relatively quickly.

Do other types of queries execute slowly on your server?

Jack Marchetti
  • 15,536
  • 14
  • 81
  • 117
1

I don't think you should be using a non-clustered index here, as it appears there is no uniqueness to exported at all. The rule of thumb for non-clustered indexes is 95% uniqueness, because the query optimizer may not use the index otherwise, which means that you're slowing it down for no reason.

Take a look at this article for explanations on non-clustered index:

http://www.sql-server-performance.com/2007/nonclustered-indexes/

James Johnson
  • 45,496
  • 8
  • 73
  • 110
  • then what is the other option if I drop the non clustered index on exported column – Prateek Sep 02 '11 at 16:18
  • I think your query might run faster just by removing it. Remember, indexes speed up selects, but slow down inserts and updates. If a column is not a good candidate for indexing, it could impact the performance of inserts and updates considerably. – James Johnson Sep 02 '11 at 16:20
  • I have removed the index. I checked the query execution plan, the major (60%) cost is in the update clustered index. This column exported is also in the clustered index along with several other columns – Prateek Sep 02 '11 at 16:35
  • Can you remove the index from that column altogether. That column should not be indexed at all. Did the query execute any faster after removing the non-clustered index? – James Johnson Sep 02 '11 at 16:39
  • `CREATE NONCLUSTERED INDEX [IX_PAR_ITM_Exported] ON [dbo].[PAR_ITM] ( [Business_Date] ASC, [Store_ID] ASC, [exported] ASC, [Item_Discount_Number_RRN] ASC, [Combo_Number] ASC ) INCLUDE ( [Discount_Name], [Item_Discount_Amount], [Item_Void_Reason]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = ON, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY] GO ` this is the non clustered index on table – Prateek Sep 02 '11 at 18:25
1

You could also be involved in a blocking situation with another query.

HLGEM
  • 94,695
  • 15
  • 113
  • 186