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?

Minh

Are you asking if you should run 1 query for 200 rows or 200 queries for 1 row each? – Lighterman

@shree.pat18, Yes. That is my doubt. – Minh

Answer 1

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

Bo

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) – Consociate

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. – Bo

select * from ( values (1), (2) ) t (id) works (at least) in SQL Server 2012 and DB2. – Consociate

@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. – Bo

Btw: for a simple test table (150k rows) the in solution was faster for me than the join with values: explain.depesz.com/s/9rkR vs. explain.depesz.com/s/7WL not in however was substantially slower than the left join/is null solution – Consociate

VALUES conforms to the SQL standard. LIMIT and OFFSET are PostgreSQL extensions. postgresql.org/docs/current/static/sql-values.html – Symphonic

There's a syntax error in the second query. Instead of WHERE, it should be ON. – Approve

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. – Deliberate

Answer 2

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.

Karyn

Answer 3

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...!!

Azotize