Questions tagged [bulk]
The bulk tag has no usage guidance.
51
questions
0
votes
1
answer
46
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
1
vote
1
answer
42
views
How do I bulk export data from an RDBMS without killing performance?
I'm building an app that lets the user export their data. When they click the Export button, we need to do a SELECT * WHERE user_id =, save it to a CSV file, and download it. There may be hundreds of ...
- 113
0
votes
2
answers
271
views
MySQL update a record on duplicate key update (merge)
I have a table products with the follow schema:
CREATE TABLE `products` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`user_id` bigint unsigned NOT NULL,
`article_id` bigint unsigned NOT NULL,
...
- 101
0
votes
0
answers
144
views
Use of tempdb for bulk upsert operation
We are using SSIS for ETL purpose. There is a shortcoming in SSIS as it doesn't provide any in built component which will bulk upsert the destination. The alternative to it to use Staging tables which ...
- 23
0
votes
0
answers
140
views
Creating a Format File For Bulk import
So when I try implementing this query:
BCP -SMSSQLSERVER01.[Internal_Checks].[Jan_Flat] format out ^
-fC:\Desktop\exported data\Jan_FlatFormat.fmt -c -T -UUGmn91 -SMSSQLSERVER01 -PChan
I am getting ...
- 1
4
votes
1
answer
903
views
Select and Update MAC Addresses to Same Format
I have a mysql database with a macaddress column, this contains thousands of mac addresses in various formats; uppercase, lowercase and with colons. See below for an example;
+-------------------+
| ...
- 177
0
votes
2
answers
186
views
Can't see process progress, machine getting slow trying to import 500 million records
Working environment:
PostgreSQL 12.0, Ubuntu 18.10, 4GB memory, i5-3230M CPU @ 2.60GHz × 4, Os 64 bits
I'm trying to import a .csv file with more than 500 million records into a PostgreSQL db table, ...
- 63
2
votes
2
answers
744
views
SSMS - Openrowset maximum file size
Apologies if this has been answered elsewhere but I couldn't find anything here or online.
Does openrowset have a file size limit? I'm trying to write a stored procedure, part of which checks if ...
- 135
2
votes
1
answer
1k
views
Mongodb - Will Bulk.find.remove() lock the collection
I'm planning to run Bulk.find.remove() on a very big collection. (1.2TB).
Will it block inserting or updating other records (which are not a part of the above find)?
- 1,734
0
votes
0
answers
231
views
Import folder with Multiple CSV's into SSMS w/ out SSIS
I am wondering what's the best and most effective way to import csv's stored in a local folder into DB w/ in SSMS.
Folder path- C:\Users\ngutierrez\Desktop\Open_Air_Tables
Folder contains about 30 ...
1
vote
0
answers
850
views
BULK INSERT ERRORFILE Not Created
We have stored procedure on SQL Server v12.0.5203.0. Within that procedure we have a dynamic SQL statement that performs BULK INSERT. We are trying to capture misformatted rows and continue processing ...
- 11
1
vote
1
answer
1k
views
How to perform builk update within mysql stored procedure
I have been trying to perform a bulk update using a single update query but unable to do so within the stored procedure.
Following is the bulk update query that run flawlessly
UPDATE `...
- 125
5
votes
2
answers
1k
views
Using CHECKPOINT vs GO
Typically, when one of the developers or data analysts needs to perform a very large update or deletion of data (where a truncate or truncate/insert wouldn't make sense because the data set to keep is ...
- 941
4
votes
2
answers
2k
views
postgres_fdw slower then copy + scp + copy (~12 times)
foreign server 9.2
local server 9.5
table is 10GB
data transfer performed on same network interface as foreign server works
no indexes set on data
old way:
copy to - 2:36
scp - 08:17
copy from - ...
- 1,105
9
votes
1
answer
7k
views
Should I disable autovacuum on a table while I do a bulk update?
I need to perform a simple update on all rows in a table. The table has 40-50 million rows. Dropping indexes and constraints during the UPDATE results in a massive performance improvement.
But what ...
- 1,552
1
vote
4
answers
3k
views
Bulk table creation and import of data
I have a around 400 CSV files which have been exported from an old Paradox database, with each CSV representing a table within the old database. Each CSV has a different number of columns and ...
- 13
0
votes
1
answer
2k
views
Importing Data in Parallel in SQL Server
I have more than 100 files to import into the sql server and most of them are of 500 MB. I want to leverage the the parallel import utility of SQL server and have read a number of webpages, like the ...
- 693
4
votes
1
answer
2k
views
Copy Millions of Records from one table to another with the same schema, in the same database
I am trying to find the best way to copy a large amount of rows ( ~70million) from one table to another with the same schema inside the same database.
Our system currently runs with a set of A/B ...
- 141
2
votes
2
answers
40k
views
How to insert numerous data into temporary table?
I'm building a program which generates a T-SQL query in following form:
DECLARE @In TABLE (Col CHAR(20))
INSERT INTO @In VALUES value1, value2... value1000
GO
INSERT INTO @In VALUES value1001, ...
0
votes
1
answer
2k
views
Execute immediate call causes 'variable not in select list' error
Execute immediate call causes 'variable not in select list' error
hi there, i'm practicing with collections and execute immediate by combining them in plsql and have the following code:
set ...
- 167
4
votes
1
answer
5k
views
mongodb bulk write all or nothing?
I am learning about mongodb. If I create a bulk write is this transaction all or nothing? I have a scenario where my users can delete who they are following. For this to happen I need to delete from ...
- 295
2
votes
0
answers
493
views
oracle bulk loader processing columns with newline characters
I'm trying to load a file with description columns which may contain newline characters. The file is Unix format utf16, tab separated and description columns are enclosed in ". The problem is bulk ...
- 121
0
votes
1
answer
1k
views
How to load 100k + Records into progres database from a CSV file?
Our project needs to load 100k+ rows into a Postgres table. We have a huge .CSV file with all these 100K+ rows and we need to load this data into a table effectively and quicker (may be in the form of ...
- 1
15
votes
3
answers
32k
views
Create a table from a CSV file with headers
I'm seeking to find a way to generate a new MySQL table solely based on the contents of a specified CSV. The CSV files I'll be using have the following properties;
"|" delimited.
First row specifies ...
- 151
0
votes
0
answers
600
views
Create Temporary Index for bulk operation and Delete after its completed
I am moving bulk data to archive tables.
Some times query returns up to 4.5million records, which takes about 2:15 minutes for a table of 40 columns.
I want too increase the performance for the ...
4
votes
1
answer
2k
views
Can SQL Server Bulk Copy cause timeouts when querying the table
In SQL Server 2008 R2, can using BulkCopy to upload thousands of rows of data cause problems for queries taking place on the same table?
Specifically could it cause the queries to time out?
The ...
- 143
3
votes
2
answers
3k
views
Bulk insert with format file: 0 rows affected
When using the BULK INSERT command in SQL Server 2008 it returns:
(0 row(s) affected)
I am using this command to carry out the bulk insert:
BULK INSERT Test
FROM 'C:\DataFiles\Tests.dat'
...
- 49
2
votes
0
answers
2k
views
CSV File Into MYSQL Merging - LOAD DATA on Duplicate Key?
Hey Dba Stack Exchange,
My question to you is with regards to mysql. Right now I have a medium to small sized csv with 25k lines I need imported into mysql for a client. Problem is, their is only ...
- 173
49
votes
2
answers
80k
views
Optimizing bulk update performance in PostgreSQL
Using PG 9.1 on Ubuntu 12.04.
It currently takes up to 24h for us to run a large set of UPDATE
statements on a database, which are of the form:
UPDATE table
SET field1 = constant1, field2 = ...
- 611
2
votes
2
answers
258
views
SQL Server BULK INSERT
I have a file saved using BCP and I want to know the specification of the table of the database that it needs to be materialized on the database. Is that possible?
Thanks
- 169
0
votes
1
answer
92
views
Reinsert data in new table with new IDs
I have the following two tables:
buynsell
CREATE TABLE `buynsell` (
`id` VARCHAR(20) NULL DEFAULT NULL,
`msg` VARCHAR(255) NULL DEFAULT NULL,
`date` VARCHAR(25) NULL DEFAULT NULL,
`...
- 480
7
votes
2
answers
6k
views
Index before or after bulk load using load infile?
I have a database with over 1B rows and two columns that are indexed (in addition to the PK).
Is it better to have the index pre-defined in the table before the load infile or better to index after ...
- 71
38
votes
2
answers
74k
views
Most efficient way of bulk deleting rows from postgres
I'm wondering what the most efficient way would be to delete large numbers of rows from PostgreSQL, this process would be part of a recurring task every day to bulk import data (a delta of insertions +...
- 549
3
votes
1
answer
1k
views
Which MySQL table to use for read-only, bulk-loaded tables?
So I have a workload on 2 key tables in my application with the following characteristics:
READ-ONLY DATA
The data is bulk-loaded in a fully consistent manner from a single process on a schedule, ...
- 133
7
votes
2
answers
5k
views
Bulk Data Loading and Transaction Log
I'm currently working on a project which bulk import data from flat files (csv) about 18 different files each linking to a specific table through some stored procedure.
I followed the steps as ...
- 229
1
vote
1
answer
1k
views
bulk insert from file
I am often requested to upload data from file (let's say csv) into a Sql Server 2008 db.
I would like to use BULK INSERT, but I have no access to the db server file system.
Questions
is there a way ...
- 359
11
votes
1
answer
3k
views
Best way to re-import large amount of data with minimal downtime
I need to import about 500,000 records containing IP lookup (read-only reference) data about once a week (only three int/bigint cols).
I don't really want to worry about merging the data with the ...
- 859
4
votes
2
answers
1k
views
fast bulk incrementing in MySQL
I have one big table foobar describing a many-to-many-relation and containing millions of foo's, millions of bar's and every bar having several hundereds of foo's -> billions of rows.
CREATE TABLE `...
- 837
4
votes
2
answers
7k
views
Mysql load from infile stuck waiting on hard drive
I've got a windows 7 64 bit machine that I'm using for some load testing of a mysql db. My program uses sqlalchemy to connect and run several load from infile statements on said database. These bulk ...
- 161
2
votes
1
answer
171
views
After dropping the clustered index, it is still attempting to do a clustered insert [closed]
I am trying to convert data from one database system to another. One of the tables I need to transfer and format contains over 10 million rows.
I am running the following script to do it:
USE [...
- 1,035
1
vote
1
answer
474
views
In a simple table with no complex indexes, does a large number of existing rows slow down an INSERT?
I have this small, simple table:
CREATE TABLE `ads_tracker` (
`id` int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
`type` smallint unsigned NOT NULL,
`reference` smallint unsigned NOT NULL,
...
- 355
23
votes
3
answers
37k
views
Why is 'LOAD DATA INFILE' faster than normal INSERT statements?
I've read an article that mentioned we can achieve 60,000 inserts per second by using the LOAD DATA IN FILE statement, that reads from csv files and inserts the data into a database.
Why should it ...
- 3,566
1
vote
2
answers
9k
views
What would be the fastest approach to bulk update this mysql table (Innodb)?
I have a table with 100M rows. I am able update my table like 850 updates / minute. How to speed this up?
I am using following approach:
mysql -uroot < query_file
where query_file contains 1M ...
- 365
1
vote
1
answer
363
views
Importing large file while keeping table available for other operations
We need to import millions of rows in a quite big table.
Say like 5 million rows into a table with more than 1 billion rows.
After preprocessing the import file we will have update operations and ...
- 51
14
votes
2
answers
13k
views
What specifically does OracleBulkCopy do, and how can I optimize its performance?
To summarize the specifics: We need to stage approximately 5 million rows into a vendor (Oracle) database. Everything goes great for batches of 500k rows using OracleBulkCopy (ODP.NET), but when we ...
- 441
4
votes
3
answers
501
views
How to send+update on duplicate a big time series to SQL server
I use a SQL Server 2008
I would like to send (and update on duplicate datetime index) a big time series (more than 1000 datapoints and eventually much more than that).
What is the best way to do so?
I ...
- 398
1
vote
1
answer
1k
views
How do I load them as varbinary(MAX) format into SQL Database?
There are 1000 picture that are jpeg format.How do I load them as varbinary(MAX) format into SQL Database?But at the same time I want the bulk insert functionality.
- 1,048
9
votes
4
answers
986
views
Suggestion for Bulk Data Import
We are working on a project which requires import data from Excel spreadsheet on daily basis. The data will be import from pre-define template and what we're thinking, first we will upload data in ...
- 337
4
votes
2
answers
4k
views
How do you copy a table from MySqlServer_A to MySqlServer_B?
I have a daily task that collects data from 3 MySql Servers and then pushes that aggregate data back out to each of the servers deleting the previous days data. All servers are MySql. I would like to ...
- 231
3
votes
1
answer
305
views
Can I use dbms_redefinition with bulk data transformations for better results?
Here is a situation for bulk loading in Oracle:
Schema A { T1, T2, T3, T4}
Schema B { TR1, TR2}
Where schema B is re-factored from schema A. Both exist in same DB. Schema A has terabytes of data ...
- 221