Since information is missing in the Q, I'll assume:
Your data comes from a file on the database server.
The data is formatted just like COPY output, with a unique id per row to match the the target table.
If not, format it properly first or use COPY options to deal with the format.
You are updating every single row in the target table or most ...
LOAD DATA INFILE and extended INSERTs each have their distinct advantages.
LOAD DATA INFILE is designed for mass loading of table data in a single operation along with bells and whistles to perform tings like:
Skipping Initial Lines
Skipping Specific Columns
Transforming Specific Columns
Loading Specific Columns
Handling Duplicate Key Issues
Less overhead ...
Your second option is far cleaner and will perform well enough to make that worth it. Your alternative is to build gigantic queries which will be quite a pain to plan and execute. In general you are going to be better off letting PostgreSQL do the work here. In general, I have found updates on tens of thousands of rows in the manner you are describing to ...
A solution I've used in the past (and have recommended here and on StackOverflow before) is to create two additional schemas:
CREATE SCHEMA shadow AUTHORIZATION dbo;
CREATE SCHEMA cache AUTHORIZATION dbo;
Now create a mimic of your table in the cache schema:
CREATE TABLE cache.IPLookup(...columns...);
Now when you are doing your switch operation:
A few more days of reading and experimentation and I was able to (mostly) answer a lot of these:
I found this buried in the ODP.NET documentation (ironically not in the OracleBulkCopy docs):
The ODP.NET Bulk Copy feature uses a direct path load approach, which is similar to,
but not the same as Oracle SQL*Loader. Using direct path load is faster than ...
You can use csvsql, which is part of csvkit (a suite of utilities for converting to and working with CSV files):
Linux or Mac OS X
free and open source
sudo pip install csvkit
Example: csvsql --dialect mysql --snifflimit 100000 datatwithheaders.csv > mytabledef.sql
It creates a CREATE TABLE statement based on the file content. Column names are taken from ...
You can use VALUES (...), (...):
INSERT INTO table(colA, colN, ...) VALUES
(col1A, col1B, ...)
, (colnA, colnB, ...)
DECLARE @In TABLE (Col CHAR(20))
INSERT INTO @In VALUES
It will insert X rows all at once. GO is not needed. Variables declared before GO don't ...
If the data is in memory, you can use SQLBulkCOpy in .net or similar to send data to SQL Server. No need to instantiate a file.
And load a staging table first in SQL Server. Then use MERGE from this staging table to the actual table
If you don't want a persistent staging table, create a #temp table and use that in the subsequent MERGE. I'm not sure about ...
It definitely could.
It requires locks just like any other insert operation. If enough locks are taken it will escalate to a full table lock (assuming the table allows it). Any insert operation like this would block anything else that was trying to read the data, unless NOLOCK was specified for those queries (which I am not recommending here).
You can ...
There is simply no point. autovacuum will not clean up any rows locked in a running transaction. So autovacuum
may not do anything useful.
may report that the rows are dead and nonremovable
may do something useful with the other rows (if there are any).
But, it won't block or slow down the update in any meaningful sense that I'm aware of.
I'll stay generic on this answer, as Cristian look to have already covered a significant number of MySQL specific considerations.
The general recommendation for bulk operations is definitely to remove and rebuild indexes afterwards. The amount of work to maintain the balance of the tree structures for each index is fairly high and depending on insert order ...
I have tried a differen variety of solution with a similar data load -over 1B- but the better that I have found is this:
From mysql documentation
With some extra work, it is possible to make LOAD DATA INFILE run even faster for a MyISAM table when the table has many indexes. Use the following procedure:
Execute a FLUSH TABLES statement or a mysqladmin ...
How about a GROUP BY count on foobar from scratch ???
First, insert any new data into foobar
Then, do a fresh GROUP BY count on foobar into the temp table:
CREATE TABLE foo_amount_new LIKE foo_amount;
INSERT INTO foo_amount_new
FROM foobar WHERE bar_id = ...
GROUP BY foo_id;
Finally, swap the temp table in and drop the old ...
Your bulk insert buffer is 4G. That's great ... FOR MyISAM !!!
InnoDB does not use the bulk insert buffer.
You may need to have sqlalchemy throttle the load data infile calls into multiple transactions.
You may also want to disable innodb_change_buffering, setting it to inserts.
Unfortunately, you cannot do SET GLOBAL innodb_change_buffering = 'inserts';....
My first comment is that you are doing an ELT (Extract, Load, Transform) rather than an ETL (Extract, Transform, Load). While ELTs leverage set based relational advantages and can be very fast, they are sometimes very write intensive (hard on storage). Specifically, the t-log. This is because the transform is done on disk (typically an update or insert). I ...
When you see CHECKPOINT as the log_reuse_wait_desc for that database, it is because no checkpoint has happened since the last time the log was truncated.
You can alleviate this issue by manually kicking off a CHECKPOINT command.
Factors That Can Delay Log Truncation
Checkpoints and the Active Portion of the Log
Most database management systems have a bulk load facility for loading large volumes of data quickly. An INSERT statement has a significant amount of per-statement baggage - locking, transaction demarcation, referential integrity checks, allocation of resources, I/O that has to be done on a per-statement basis.
Bulk insert operations streamline the process ...
Parsing and executing individual INSERT statements carries a much larger overhead than splitting a CSV file into columns and directly loading them.
Each INSERT statement has to be individually parsed by the MySQL engine & checked for validity - this consumes extra CPU resources & also requires more client<>server round-trips. This does not need ...
Borrowing from my old answer
Below are some good ways to improve BULK INSERT operations :
Using TABLOCK as query hint.
Dropping Indexes during Bulk Load operation and then once it is completed then recreating them.
Changing the Recovery model of database to be BULK_LOGGED during the load operation.
If the target has Clustered Index then specifying ORDER BY ...
The first thing you need to determine about col2 is if it can be a PRIMARY KEY.
Run this query
SELECT COUNT(1),col2 FROM table GROUP BY col2 HAVING COUNT(1) > 1;
If nothing comes back, then col2 can be a UNIQUE KEY. If even one row comes back, then col2 cannot be a UNIQUE KEY. You can create an index on it.
Since this query would take a while without ...
Yes, use OPENROWSET with BULK. You need a format file though.
Assuming you want to attach blobs to existing records, something like:
INSERT SomeTable (id, blob)
FORMATFILE = 'c:\myfileformat.txt'
) B ON X.AKey = B.AKey
If you're ok with using Python, Pandas worked great for me (csvsql hanged forever and less cols and rows than in your case). Something like:
from sqlalchemy import create_engine
import pandas as pd
df = pd.read_csv('/PATH/TO/FILE.csv', sep='|')
# Optional, set your indexes to get Primary Keys
df = df.set_index(['COL A', 'COL B'])
engine = create_engine('...
MongoDB transactions are always atomic on single document. However, if it involves multiple documents transactions are not atomic.
Coming to Bulk operations, there is no transactions concept. For example, you are inserting 100 documents in a collection, if 51st insert fails, MongoDB will not insert the remaining documents in the list as it executes the ...
Simplistically, the GO batch separator should be removed (as stated in @Julien's answer).
Just prove that it does work, try the following:
DECLARE @ValuesPerInsert INT = 1000; -- 1000 works, 1001 fails
DECLARE @SQL NVARCHAR(MAX) = '
DECLARE @In TABLE (Col CHAR(20))';
;WITH cte AS
SELECT TOP (3523)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))...
A fairly easy option I like is just using PowerShell.
Get the out-datatable.ps1 module from CodePlex here and loop through your .csv files.
Then generate a SQL statement to create your table and use SQL bulk insert to load your data into your table.
This is a script that could do what you want, it processes .txt files as tab-delimited and csv files as ...
select macaddress, upper(replace(macaddress,':','')) as new_macaddress
set macaddress = upper(replace(macaddress,':',''));
create table macs
insert into macs values('90CCAADD3341');
insert into macs values('90:3f:ff:11:22:33');
insert into macs values('33:44:...
Well we tend not to care what the originators table structure is, but only if it meets our requirements (which we send to them).
If you are trying to figure out how to design a way to store the data permanently because you don't currently have a structure, then this is the method I use. Import the file into a staging table (not the final permanent table, I ...