SQL Performance (UPDATE)- Which query would be quicker...... |
|
Author |
Message |
Enigo
|
Posted: Fri Nov 18 11:33:28 CST 2005 |
Top |
SQL Server Developer >> SQL Performance (UPDATE)- Which query would be quicker......
If I have a SQL table with Columns A B C D and an Primary Key of A then which
of the following would perform better - OK I know it depends on volumes etc,
but just by examining the query is it better to update more than actually
required so you can use the primary key and therefore index seek, or by
limiting the number of rows the update is applicable to?
update tablex set b=1, c=2, d=3 where A=0 (lets say there are 10 rows where
A=0 ie 10 updates)
or by limiting the update
update tablex set b=1, c=2, d=3 where A=0 and (b<>1 or c<>2 or d<>3) (lets
say 1 row now fulfils the conditions)
noting there are no additional indexes containing columns d,c,d.
Thanks in advance.
Pete.
SQL Server131
|
|
|
|
|
Gert-Jan
|
Posted: Fri Nov 18 11:33:28 CST 2005 |
Top |
SQL Server Developer >> SQL Performance (UPDATE)- Which query would be quicker......
It depends. It depends on the selectivity of the columns b, c and d, and
on your available indexes (and yet, on the number of rows and table
size)
If there is only a clustered index on A and no other indexes, then the
second query will always be faster than the first (or at least equally
fast).
If the index on column A is not clustered and/or there are indexes on
either of the columns b, c, d, then it really depends on the data
characteristics as mentioned above.
As a rule of thumb I would use the following tactic. Ask yourself: how
many rows that would be updated with the first query would not be
updated with the second? If this is less then 5% or maybe 10%, then I
wouldn't bother (unless the update set is very large) and keep the query
as simple as possible (query 1). Otherwise, if you are not satisfied
with the performance, you could consider tuning the query. Your second
query is one such example.
Gert-Jan
>
> If I have a SQL table with Columns A B C D and an Primary Key of A then which
> of the following would perform better - OK I know it depends on volumes etc,
> but just by examining the query is it better to update more than actually
> required so you can use the primary key and therefore index seek, or by
> limiting the number of rows the update is applicable to?
>
> update tablex set b=1, c=2, d=3 where A=0 (lets say there are 10 rows where
> A=0 ie 10 updates)
>
> or by limiting the update
>
> update tablex set b=1, c=2, d=3 where A=0 and (b<>1 or c<>2 or d<>3) (lets
> say 1 row now fulfils the conditions)
>
> noting there are no additional indexes containing columns d,c,d.
>
> Thanks in advance.
> Pete.
|
|
|
|
|
TrinityPete
|
Posted: Fri Nov 18 12:45:05 CST 2005 |
Top |
SQL Server Developer >> SQL Performance (UPDATE)- Which query would be quicker......
Thanks for the reply. I sort of guessed as much but it always pays to get
other informed opinions and rely less on the guess work.
The reason I actually ask is that we write a product that relies extensively
on SQL Triggers and one of our customers is experiencing deadlocks, I
personally believe it is to do with the spec of their SQL server (or mainly
lack of SQL Enterprise on an 8GB memory server therefore SQL is maxing out at
about 1.8MB - BTW nightly maintenance run like update stats etc.) The only
thing I can think of is trying to limit the number of updates and therefore
potential locks. (although I do know SQL escalates locks beyond row lock when
it sees fit and I knowabout the share locks placed during selects) all
together a mine field.....
kind Regards,
Pete.
> It depends. It depends on the selectivity of the columns b, c and d, and
> on your available indexes (and yet, on the number of rows and table
> size)
>
> If there is only a clustered index on A and no other indexes, then the
> second query will always be faster than the first (or at least equally
> fast).
>
> If the index on column A is not clustered and/or there are indexes on
> either of the columns b, c, d, then it really depends on the data
> characteristics as mentioned above.
>
> As a rule of thumb I would use the following tactic. Ask yourself: how
> many rows that would be updated with the first query would not be
> updated with the second? If this is less then 5% or maybe 10%, then I
> wouldn't bother (unless the update set is very large) and keep the query
> as simple as possible (query 1). Otherwise, if you are not satisfied
> with the performance, you could consider tuning the query. Your second
> query is one such example.
>
> Gert-Jan
>
>
> >
> > If I have a SQL table with Columns A B C D and an Primary Key of A then which
> > of the following would perform better - OK I know it depends on volumes etc,
> > but just by examining the query is it better to update more than actually
> > required so you can use the primary key and therefore index seek, or by
> > limiting the number of rows the update is applicable to?
> >
> > update tablex set b=1, c=2, d=3 where A=0 (lets say there are 10 rows where
> > A=0 ie 10 updates)
> >
> > or by limiting the update
> >
> > update tablex set b=1, c=2, d=3 where A=0 and (b<>1 or c<>2 or d<>3) (lets
> > say 1 row now fulfils the conditions)
> >
> > noting there are no additional indexes containing columns d,c,d.
> >
> > Thanks in advance.
> > Pete.
>
|
|
|
|
|
|
|