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.
21
questions
1
vote
1
answer
129
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 ...
- 849
0
votes
2
answers
50
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 ...
- 197
0
votes
0
answers
15
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 ...
0
votes
0
answers
76
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
"...
- 113
2
votes
2
answers
910
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 ...
- 257
2
votes
2
answers
116
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',
'...
- 133
0
votes
1
answer
242
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 ...
- 103
0
votes
0
answers
54
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 ...
- 1
1
vote
0
answers
496
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 ...
- 142
2
votes
2
answers
6k
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 ...
- 23
0
votes
1
answer
4k
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=...
- 157
0
votes
1
answer
986
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 ...
- 101
0
votes
1
answer
647
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 ...
- 133
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 ...
- 5,472
3
votes
3
answers
297
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, ...
- 157
0
votes
0
answers
47
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 ...
- 301
-1
votes
1
answer
525
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 ...
- 99
2
votes
1
answer
976
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 ...
- 23
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: ...
- 113
0
votes
4
answers
396
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) ...
2
votes
5
answers
16k
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 ...
- 1,910