Performance issue in update query

Question

I have one small doubt in query performance. Basically, I have a table with more than 1C records. sl_id is the primary key in that table. Currently, I am updating the table column status to true (default false) by using the sl_id.

In my program, I will have 200 unique sl_id in an array. I am updating the status to true(always) by using each sl_id.

My doubt:

Shall I use individual update queries by specifing each sl_id in a where condition to update the status?

(OR)

Shall I use IN operator and put all 200 unique sl_id in one single query?

Which one will be faster?

Are you asking if you should run 1 query for 200 rows or 200 queries for 1 row each? — shree.pat18, Jul 09 '14 at 07:19

score 53 · Answer 1 · edited Jun 25 '17 at 04:42

53

In rough order of slower to faster:

200 Individual queries, each in their own transaction
200 Individual queries, all in one transaction
1 big query with WHERE ... IN (...) or WHERE EXISTS (SELECT ...)
1 big query with an INNER JOIN over a VALUES clause
(only faster for very big lists of values): COPY value list to a temp table, index it, and JOIN on the temp table.

If you're using hundreds of values I really suggest joining over a VALUES clause. For many thousands of values, COPY to a temp table and index it then join on it.

An example of joining on a values clause. Given this IN query:

SELECT *
FROM mytable
WHERE somevalue IN (1, 2, 3, 4, 5);

the equivalent with VALUES is:

SELECT *
FROM mytable
INNER JOIN (
  VALUES (1), (2), (3), (4), (5)
) vals(v)
ON (somevalue = v);

Note, however, that using VALUES this way is a PostgreSQL extension, wheras IN, or using a temporary table, is SQL standard.

See this related question:

Postgres NOT IN performance

edited Jun 25 '17 at 04:42

user3112401

373
2
9

answered Jul 09 '14 at 07:28

Craig Ringer

307,061
76
688
778

The `values` clause as you have shown is defined in the standard if I'm not mistaken (not 100% about the alias with the column definition though - but that could be turned into a CTE if standard compliance was important) – Jul 09 '14 at 07:40
1

`VALUES` is standard, sure. I don't think it's SQL-spec to use it as a statement in a subquery though. Certainly it hasn't worked in non-PostgreSQL DBs I've tried it with. Nor as a top-level statement like Pg permits - `VALUES (1)` is a legal PostgreSQL query. – Craig Ringer Jul 09 '14 at 07:53
2

`select * from ( values (1), (2) ) t (id)` works (at least) in SQL Server 2012 and DB2. – Jul 09 '14 at 08:15
@a_horse_with_no_name That's really good to know, thankyou for testing. I think it was MySQL and Oracle I was trying with. – Craig Ringer Jul 09 '14 at 08:16
2

Btw: for a simple test table (150k rows) the `in` solution was faster for me than the join with `values`: http://explain.depesz.com/s/9rkR vs. http://explain.depesz.com/s/7WL `not in` however was substantially slower than the left join/is null solution – Jul 09 '14 at 08:36
*`VALUES` conforms to the SQL standard. LIMIT and OFFSET are PostgreSQL extensions.* http://www.postgresql.org/docs/current/static/sql-values.html – pozs Jul 09 '14 at 13:59
There's a syntax error in the second query. Instead of WHERE, it should be ON. – user3112401 Jun 24 '17 at 14:41
4

One other thing to consider is the load impact on your DB. I had a very frequently used query where the number of values varied from 5-30,000. When testing different approaches on the 30K values, using a temp table was a little bit faster than joining on values but had a much larger impact on the server's CPU load. – DrewB Nov 04 '17 at 00:12

score 1 · Answer 2 · answered Jul 09 '14 at 07:20

1

Definitely you should use WHERE IN operator. Making 200 queries is much slower than one bigger. Remember, when you sending query to database, there is additional time needed to communicate between server and DB and this will crush your performance.

answered Jul 09 '14 at 07:20

Kasyx

3,170
21
32

score 0 · Answer 3 · answered Jul 09 '14 at 07:36

Definitely IN is more powerful, but again the number of match to check in IN will make performance issue.

So, I will suggest to use IN but with BATCH, as in if you have 200 record to update then part in 50 each and then make 4 UPDATE query, or something like that.

Hope it helps...!!

Performance issue in update query

3 Answers3

Linked