Skip to main content

Questions tagged [batch-processing]

Processing an operation in chunks rather than the whole set in one operation. Typically applied to DELETE, UPDATE, and INSERT commands to improve concurrency and reduce resource usage.

Filter by
Sorted by
Tagged with
7 votes
1 answer
2k views

How to avoid Table Lock Escalation?

I've got a task to update 5 million rows in a production table, without locking down the whole table for extended time So, I used approach that helped me before many times - updating top (N) rows at a ...
Aleksey Vitsko's user avatar
3 votes
3 answers
378 views

what files should I exclude/add/copy mysql dbs (without mysqldump)

I need to have an exact copy of my databases in a clone VM of my server (just in case the server goes down, have the VM ready with the dbs). I know that mysqldump is the best alternative to migrate, ...
acgbox's user avatar
  • 157
3 votes
2 answers
8k views

Copy millions of rows to another table in batches mySQL

Table A which is always getting updated (records being inserted or updated). Table A contains millions of records. I'd like to copy some of these records to a new table Table B. Table A and Table B ...
BountyHunter's user avatar
2 votes
5 answers
17k views

Delete from table in chunks

I want to cleanup one of my tables which has 4 million. So I prepare this query to clean it. But it's throwing an error, can anyone please help me to fix this? DELIMITER $$ DROP PROCEDURE IF EXISTS ...
TheDataGuy's user avatar
  • 1,950
2 votes
2 answers
1k views

CTE vs. temp table for batch deletes

Two-part question here. We have a large table (between 1-2 million rows) with very frequent DML operations on it. During low volume periods, we have an agent job to remove older rows to keep the ...
DBA Greg14's user avatar
2 votes
1 answer
1k views

PostgreSQL Copy command internals

I need to know some internals about the copy command in PostgreSQL 10. In particular I need to know if the copy from into a remote database makes batch insertions. If so, what is the default ...
nprog89's user avatar
  • 23
2 votes
2 answers
130 views

Why do batch updates to a CQL list merge the data?

I have created the following table CONSISTENCY LOCAL_QUORUM; drop keyspace if exists cycling; CREATE KEYSPACE IF NOT EXISTS cycling WITH REPLICATION = { 'class' : 'SimpleStrategy', '...
gudge's user avatar
  • 133
1 vote
1 answer
72 views

how can I see how many records have been affected by a transaction before it is committed? or how costly would be the rollback if I have to kill it?

I am using sqlwatch to monitor some of my servers and it has a job that deletes in batch from a table called dbo.sqlwatch_logger_snapshot_header I had lots of problems of locking and blocking as this ...
Marcello Miorelli's user avatar
1 vote
1 answer
195 views

Handle partial failures in a transaction (commit selectively) in MSSQL

I am doing a batch update where I process records sequentially (User 1, User 2 etc). Each record has multiple update queries associated to it. I need to skip if there is a data issue for an individual ...
Nishant's user avatar
  • 879
1 vote
2 answers
2k views

Mysql innodb table dead lock while batch update in multiple thread

I've two tables of innoDB type and I'm updating one table using batch statement from 10 thread (one batch statement per thread) but some time I'm getting dead lock. java.sql.BatchUpdateException: ...
ankit.vishen's user avatar
1 vote
1 answer
45 views

Weird behaviour with batch and trigger(MariaDB)

I found a weird issue involving mariadb. batch update from hibernate. the original question was added here : https://stackoverflow.com/questions/78204056/hibernate-sqlintegrityconstraintviolation-...
Rudy's user avatar
  • 151
1 vote
0 answers
67 views

SQL script to run from batch jobs

I have a sql script that truncates the table and insert into database from a linked server.Since the server is SQL express I will need to use batch jobs to schedule the jobs. Below is a snippet of the ...
SQL_NoExpert's user avatar
  • 1,061
1 vote
0 answers
550 views

Postgres batch insert with custom return value

I use Postgres 13 with Golan and pgx/v4 package for batch insert items Now we're migrating from UUID to serial ID's. For one table with parent ID, I need to generate one sort of mapping of parentds ...
Darii Petru's user avatar
0 votes
2 answers
124 views

Delete all rows if input parameter is 0 otherwise delete only those entries which match the input param

I have a stored proc which is receiving id as input. If the id which is received is 0 then we need to delete all the rows otherwise we need to delete only those rows which matches the input id. I can ...
Himanshuman's user avatar
0 votes
1 answer
314 views

PostgreSQL: does the WAL group commit affect synchronous replication performance?

I'm curious if PostgreSQL's Group Commit mechanism (controlled by commit_delay and commit_siblings parameters) only affects disk fsync calls. Or does it also allow multiple on-the-fly transactions to ...
ASBai's user avatar
  • 103
0 votes
1 answer
5k views

How can I create a mysql database (if it doesn't exist) or replace it (if it exists)?

My dump: mysqldump --host=localhost --user=root --password=whatever --single-transaction --routines --triggers --log-error=error.txt --databases mydatabase > out.sql My restore mysql --host=...
acgbox's user avatar
  • 157
0 votes
1 answer
788 views

How to assign GUID to batches of data based on row counts and tunable batch size

I need to assign batches of rows a common GUID id (for consumption by an external process). Below is a simplified setup describing what I'm looking for: BASE_TABLE represents one of pre-existing ...
Gus's user avatar
  • 133
0 votes
4 answers
414 views

Dynamically generate batched data for import

I need to execute a test in which I have to simulate 20 years' historical data in PostgreSQL (and TimescaleDB) DB. My problem is that I have to generate .sql files and ingest them (using psql-client) ...
Benjamin Soulas's user avatar
0 votes
0 answers
28 views

Creating a real time data pipeline with transactional DB

I am working in fintech industry, they recently needed a dash-boarding solution for executive , finance and other departments while they also want AI solution to keep track of valid disputes and fraud ...
Muhammad Affan's user avatar
0 votes
0 answers
79 views

Search from text file contain string and replace row

My database table named VmIpAddress has the following 12 columns id, hosting_id, server_id, vm_id, ip, mac_address, subnet_mask, gateway, cidr, trunks, tag, net A row might contain for example "...
Toodarday's user avatar
  • 113
0 votes
0 answers
55 views

Deleting large amounts of rows in SQL server 15

I'm trying to execute a batch delete, but I'm having trouble executing it due to not properly using clustered index. Clustered index consists of three fields(int, int, datetime2). The table consists ...
elitra's user avatar
  • 1
0 votes
1 answer
1k views

how to call lo_unlink(lo) in batches

In Postgres 9.5 I am tying to reproduce the postgres tool vacuumlo to get rid of unreferenced large object to free disk space. References are stored in text type columns by hibernate so using the tool ...
Luky's user avatar
  • 101
0 votes
0 answers
63 views

Drop or truncate a temporary table during LOAD DATA?

I'm using LOAD DATA to batch csv content into a TEMPORARY table, with deactivated auto-commit. Within the same transaction, I then INSERT... ON DUPLICATE KEY UPDATE the content from temp table into ...
membersound's user avatar
-1 votes
1 answer
674 views

What is the safe way to update production data if data found inconsistent

I am in a small company and require to fix a bunch of data in production which are inconsistent. I have written the script to handle the fix. I understand that writing sql to fix data is way more ...
LittleFunny's user avatar