Clustered Index performance

I have a table with the structure as below. I am running the query 'update
DTH_StatementMaster_PREP set PrintIndicator = 1 where BatchID =
'BTCH00000000030'. There are only 25,000 records in the table and all of the
m
qualify for the update. Since there is a clustered index on the predicate
(BatchID), I would naturally expect this query to run quick. Unfortunately i
t
is taking over 3 seconds to run which is way too long.
If I look at the execution plan, it says 83% of the cost is on a Sort
operation. The arguments of the Sort operation are PrintIndicator desc,
BatchID asc. Can anyone explain what this Sort operation is? I didn't expect
to see it as I'm not retreiving records, just updating.
Thanks,
Dean
CREATE TABLE [dbo].[DTH_StatementMaster_PREP] (
[StatementID] [char] (15) COLLATE Latin1_General_BIN NOT NULL ,
[StatementAmount] [money] NOT NULL ,
[StatementDate] [smalldatetime] NOT NULL ,
[CurrentBalance] [money] NOT NULL ,
[OverdueBalance] [money] NOT NULL ,
[CustomerID] [varchar] (15) COLLATE Latin1_General_BIN NOT NULL ,
[BatchID] [char] (15) COLLATE Latin1_General_BIN NOT NULL ,
[EntryUserID] [varchar] (30) COLLATE Latin1_General_BIN NOT NULL ,
[EntryDateTime] [datetime] NOT NULL ,
[PrintIndicator] [tinyint] NOT NULL ,
[RowID] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [IX_DTH_StatementMaster_PREP_BatchID] ON
[dbo].[DTH_StatementMaster_PREP]([BatchID]) ON [PRIMARY]
GO
CREATE UNIQUE INDEX [IX_DTH_StatementMaster_PREP] ON
[dbo]. [DTH_StatementMaster_PREP]([CustomerID])
ON [PRIMARY]
GO
CREATE INDEX [IX_DTH_StatementMaster_PREP_PrintIndica
tor] ON
[dbo]. [DTH_StatementMaster_PREP]([PrintIndicat
or] DESC ) ON [PRIMARY]
GOThere might be a composite key on the table and which is causing the
sort operation.
If you always use 'BTCH00000000030' to update, just create a view for
this and try to update the view
Please let me know if u have any questions
best Regards,
Chandra
http://www.SQLResource.com/
http://chanduas.blogspot.com/
---
*** Sent via Developersdex http://www.examnotes.net ***|||Dean,
I think we need more info about the execution plan. Which index is the
optimizer using during this operation?

> CREATE INDEX [IX_DTH_StatementMaster_PREP_PrintIndica
tor] ON
> [dbo]. [DTH_StatementMaster_PREP]([PrintIndicat
or] DESC ) ON [PRIMARY]
> GO
Can you tell us a little bit more about possible values for column
[PrintIndicator]?
What is the selectivity for those values?
Based on the selectivity, is it valuable to have an index by [PrintIndicator]?
Tips on Optimizing Non-Clustered
SQL Server Indexes
http://www.sql-server-performance.c...red_indexes.asp
AMB
"Dean" wrote:

> I have a table with the structure as below. I am running the query 'update
> DTH_StatementMaster_PREP set PrintIndicator = 1 where BatchID =
> 'BTCH00000000030'. There are only 25,000 records in the table and all of t
hem
> qualify for the update. Since there is a clustered index on the predicate
> (BatchID), I would naturally expect this query to run quick. Unfortunately
it
> is taking over 3 seconds to run which is way too long.
> If I look at the execution plan, it says 83% of the cost is on a Sort
> operation. The arguments of the Sort operation are PrintIndicator desc,
> BatchID asc. Can anyone explain what this Sort operation is? I didn't expe
ct
> to see it as I'm not retreiving records, just updating.
> Thanks,
> Dean
>
>
> CREATE TABLE [dbo].[DTH_StatementMaster_PREP] (
> [StatementID] [char] (15) COLLATE Latin1_General_BIN NOT NULL ,
> [StatementAmount] [money] NOT NULL ,
> [StatementDate] [smalldatetime] NOT NULL ,
> [CurrentBalance] [money] NOT NULL ,
> [OverdueBalance] [money] NOT NULL ,
> [CustomerID] [varchar] (15) COLLATE Latin1_General_BIN NOT NULL ,
> [BatchID] [char] (15) COLLATE Latin1_General_BIN NOT NULL ,
> [EntryUserID] [varchar] (30) COLLATE Latin1_General_BIN NOT NULL ,
> [EntryDateTime] [datetime] NOT NULL ,
> [PrintIndicator] [tinyint] NOT NULL ,
> [RowID] [int] IDENTITY (1, 1) NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE CLUSTERED INDEX [IX_DTH_StatementMaster_PREP_BatchID] ON
> [dbo].[DTH_StatementMaster_PREP]([BatchID]) ON [PRIMARY]
> GO
> CREATE UNIQUE INDEX [IX_DTH_StatementMaster_PREP] ON
> [dbo]. [DTH_StatementMaster_PREP]([CustomerID])
ON [PRIMARY]
> GO
> CREATE INDEX [IX_DTH_StatementMaster_PREP_PrintIndica
tor] ON
> [dbo]. [DTH_StatementMaster_PREP]([PrintIndicat
or] DESC ) ON [PRIMARY]
> GO
>|||"Dean" <Dean@.discussions.microsoft.com> wrote in message
news:13F04AE4-2B00-4CB2-AB55-DA9A416CD759@.microsoft.com...
>I have a table with the structure as below. I am running the query 'update
> DTH_StatementMaster_PREP set PrintIndicator = 1 where BatchID =
> 'BTCH00000000030'. There are only 25,000 records in the table and all of
> them
> qualify for the update. Since there is a clustered index on the predicate
> (BatchID), I would naturally expect this query to run quick.
Why would you expect that? The clustered index makes it cheap to identify
the set of records that qualify for the update. But you said that all of
the rows qualify!

>Unfortunately it
> is taking over 3 seconds to run which is way too long.
> If I look at the execution plan, it says 83% of the cost is on a Sort
> operation. The arguments of the Sort operation are PrintIndicator desc,
> BatchID asc. Can anyone explain what this Sort operation is? I didn't
> expect
> to see it as I'm not retreiving records, just updating.
Since the update changes the PrintIndicator on every row, it must
completely rewrite this index:

> CREATE INDEX [IX_DTH_StatementMaster_PREP_PrintIndica
tor] ON
> [dbo]. [DTH_StatementMaster_PREP]([PrintIndicat
or] DESC ) ON [PRIMARY]
> GO
Since BatchID is the clustered index, this nonclustered index contains
PrintIndicator and BatchID. Thus the two-column sort.
However, while not cheap, 3 seconds does seem a bit long to update 25,000
rows, even with a sort. Is this a disk sort? (Look at physical IO). How
much memory does SQL Server have?
David|||I'm not sure which index is the optimizer. Is this something I can see in th
e
execution plan?
Print indicator is really a bit field but had to make it tinyint to put on
index on it. Possible values are only 0, 1 but testing has shown the index o
n
PrintIndicator helps considerably.
Thanks,
Dean|||Dean,

> I'm not sure which index is the optimizer. Is this something I can see in
the
> execution plan?
You can use "set statistics profile on" to display the profile information
for the statement.
AMB
"Dean" wrote:

> I'm not sure which index is the optimizer. Is this something I can see in
the
> execution plan?
> Print indicator is really a bit field but had to make it tinyint to put on
> index on it. Possible values are only 0, 1 but testing has shown the index
on
> PrintIndicator helps considerably.
> Thanks,
> Dean
>