Why are batch inserts/updates faster? How do batch updates work?

by

I was looking for an answer on the same subject, about “bulk/batch” update. People often describe the problem by comparing it with insert clause with multiple value sets (the “bulk” part).

INSERT INTO mytable (mykey, mytext, myint)
VALUES 
  (1, 'text1', 11),
  (2, 'text2', 22),
  ...

Clear answer was still avoiding me, but I found the solution here: http://www.postgresql.org/docs/9.1/static/sql-values.html

To make it clear:

UPDATE mytable
SET 
  mytext = myvalues.mytext,
  myint = myvalues.myint
FROM (
  VALUES
    (1, 'textA', 99),
    (2, 'textB', 88),
    ...
) AS myvalues (mykey, mytext, myint)
WHERE mytable.mykey = myvalues.mykey

It has the same property of being “bulk” aka containing alot of data with one statement.

More Related Contents:

  1. Drop all tables command
  2. Why don’t DBMS’s support ASSERTION
  3. What’s the correct name for an “association table” (a many-to-many relationship) [closed]
  4. Is this a good way to model address information in a relational database?
  5. How to get the byte size of resultset in an SQL query?
  6. Insert an image in a column of a table that already has 5 columns
  7. How to use GROUP BY to concatenate strings in SQL Server?
  8. SET versus SELECT when assigning variables?
  9. Is it possible to for SQL Output clause to return a column not being inserted?
  10. Why would an IN condition be slower than “=” in sql?
  11. Oracle’s default DATE format
  12. Create a pivot table with PostgreSQL
  13. How to convert an integer (time) to HH:MM:SS::00 in SQL Server 2008?
  14. Query error with ambiguous column name in SQL
  15. Show a one to many relationship as 2 columns – 1 unique row (ID & comma separated list)
  16. What does DELIMITER // do in a Trigger?
  17. SSIS Package not wanting to fetch metadata of temporary table
  18. Select a random sample of results from a query result
  19. Seeing the underlying SQL in the Spring JdbcTemplate?
  20. SQL GROUP BY CASE statement with aggregate function
  21. How can I alter this computed column in SQL Server 2008?
  22. Alphanumeric sorting with PostgreSQL
  23. INSERT VALUES WHERE NOT EXISTS
  24. Database efficiency – table per user vs. table of users
  25. Cannot create an instance of OLE DB provider Microsoft.Jet.OLEDB.4.0 for linked server null
  26. How to use Timestamp_to_scn and Scn_to_timestamp in Oracle?
  27. How to get a list column names and datatypes of a table in PostgreSQL?
  28. Inner Joining three tables
  29. Using django how can I combine two queries from separate models into one query?
  30. SQL select * from column where year = 2010

Leave a Comment