CODEWITHSUNDEEP
sql-serverdatabaseoracle-databasedb2
Rashmi Pandit
Rashmi Pandit

Reputation: 23848

More efficient of the two queries?

I have a table with columns user_id, email, default. Default stores 'Y' or 'N' depending if the email is the users default email. Each user can have only one default email.

When a user is doing an update or insert on the table, in my SP I check if the user has passed isDefault as 'Y'. If so, I need to update all the entries for that user to make default 'N'. My question is: considering there are no locking issues (not more than one thread will request data from the table for a particular user) which one amongst the following queries is least time consuming:

update table
set default = 'N'
where user_id = 'abc'
and default = 'Y'

(Overhead of where default = 'Y' check)

OR

update table
set default = 'N'
where user_id = 'abc'

(Overhead of updating all records for the user)

Upvotes: 3

Views: 207

Answers (5)

Gary Myers
Gary Myers

Reputation: 35401

On the Oracle front, I'd go for option 1. Sort of.

I'd have a unique index enforcing that there is only one default entry for each user, then I'd use the function in that index as part of the update. You only update the rows you need to update. Plus the unique index minimises the work needed to get the default email for a given user, not just for the update but anywhere you use that function to get the row. 

drop table user_email;

create table user_email 
(userid varchar2(4) not null, default_ind varchar2(1) not null, 
email varchar2(30));

create unique index ue_x on user_email 
  (userid, decode(default_ind,'Y','Y',email));

insert into user_email (userid, default_ind, email) values ('fred','N','a');
insert into user_email (userid, default_ind, email) values ('fred','N','b');
insert into user_email (userid, default_ind, email) values ('fred','Y','c');

update user_email
set default_ind = 'N'
where userid = 'fred'
and decode(default_ind,'Y','Y',email) = 'Y';

update user_email
set default_ind = 'Y'
where userid = 'fred'
and email = 'a';

PS. "Overhead of where default = 'Y' check" is pretty insignificant as you need to access that column anyway to update it.

Upvotes: 1

Aaron Alton
Aaron Alton

Reputation: 23236

With 99% certainty I can say the first query will be more performant.

Chances are your clustering key is user_id, so your UPDATE statement is going to find the row to be updated very quickly. An update is logically implemented in SQL Server as a delete and then an insert, so it can be a fairly expensive operation (relative to a simple lookup).

Couple that with the fact that the UPDATE requires an exclusive lock on the record, and all signs are pointing to statement 1.

Upvotes: 5

Lucas Wilson-Richter
Lucas Wilson-Richter

Reputation: 2324

There's not a clear-cut answer here. Your efficiency will be best in the first case if default = 'N' for most records. In the second, it will be best if default = 'Y' in most cases.

So if most of your users only have 1 email address, use the 2nd query. If most users have at least 2, use the first.

Upvotes: 1

Jonathan Parker
Jonathan Parker

Reputation: 6795

Why not try them out yourself. In SQL Management Studio run both queries one after the other and enable "Include actual execution plan". Whichever has the higher percentage is the slower query.

Upvotes: 1

Jeremy
Jeremy

Reputation: 6670

The speed of the queries depends on several factors such as number of rows in the table, indexes, check constraints and foreign and primary keys.

The best way to determine which is faster at least in SQL Server is to use the include client statistics and execution plan when you run the query. compare the times for each and pick the best.

Upvotes: 0

Related Questions