Database Administrators Stack Exchange
Database Administrators Stack Exchange

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.

25 questions
7
votes
1 answer

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 time with 1-N second interval between…
Aleksey Vitsko
  • 6,148
  • 5
  • 39
  • 70
3
votes
2 answers

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 has exact same schema. How can I copy records from…
BountyHunter
  • 33
  • 1
  • 4
3
votes
3 answers

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, but I want to have another alternative. So I…
acgbox
  • 157
  • 1
  • 7
2
votes
2 answers

Is it possible to store SQL path instead of uploading entire BLOB data?

I've a c# site which is uploading images in SQL database and now that SQL table size is around 300GB. Is it possible to store the path of those images instead of storing entire blob data while uploading images from the C# site? Also, I would like to…
James
  • 21
  • 2
2
votes
2 answers

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 tables size in check. it uses the CTE below, which…
DBA Greg14
  • 265
  • 3
  • 18
2
votes
2 answers

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', 'replication_factor' : 3 } and durable_writes =…
gudge
  • 133
  • 4
2
votes
1 answer

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 insertion batch size? And the insertion will be…
nprog89
  • 23
  • 3
2
votes
5 answers

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 archive_table $$ create procedure archive_table ()…
TheDataGuy
  • 1,986
  • 5
  • 36
  • 67
1
vote
1 answer

Weird behaviour with batch and trigger(MariaDB)

I found a weird issue involving mariadb. batch update from hibernate. the original question was added here…
Rudy
  • 151
  • 7
1
vote
1 answer

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 table is used by different processes, then I…
Marcello Miorelli
  • 17,274
  • 53
  • 180
  • 320
1
vote
0 answers

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 batch job that calls the sql script TRUNCATE…
SQL_NoExpert
  • 1,107
  • 1
  • 21
  • 37
1
vote
1 answer

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 record (user in this case). Ideally, I would…
Nishant
  • 899
  • 2
  • 13
  • 20
1
vote
0 answers

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 ids, to put them on children's rows. I split this…
Darii Petru
  • 142
  • 1
  • 12
1
vote
1 answer

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 as is is not an option. Looking at vacuumlo…
Luky
  • 121
  • 4
1
vote
2 answers

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: Deadlock found when trying to get lock; try…
ankit.vishen
  • 115
  • 7
1
2