Tag Info

Hot answers tagged

16

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 ...


11

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: ...


10

Assumptions 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 ...


7

SSIS is the way to go on this. If you've never built a package before, and you know your source files (read also: spreadsheets) are always going to be the same ones, what you can do is use SQL Server's Import/Export wizard. In SSMS right-click database and select Tasks > Import (or Export) Data... This opens a wizard which walks you through the steps of ...


5

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 ...


5

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 ...


5

You are removing the primary key in database Cvti101687 and inserting to a table in database cvti101639.


5

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 ...


4

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 SELECT foo_id,COUNT(1) FROM foobar WHERE bar_id = ... GROUP BY foo_id; Finally, swap the temp table in and drop the old ...


4

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. Supporting references: Factors That Can Delay Log Truncation Checkpoints and the Active Portion of the Log


4

Here is one example on how to import data from excel to SQL Server. One of the main problems is making sure you use Data Conversion component between Excel and SQL Server and do a conversion from NVARCHAR to VARCHAR as excel treats the data as NVARCHAR. http://www.mssqltips.com/tip.asp?tip=1393


3

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 ...


3

Use SQL Server Integration Services. It is that simple. We can't show you how here: it's too general


3

If you make sure that your filesystems are on striped storage. That way you can prevent the io bottleneck. Just specifying different location without knowing what kind of storage they are on, does not make much sense. This workload typically has large scans and large writes so striping will certainly help you. Ronald.


3

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 ...


3

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 = ...


3

It is technically possible, yes. But you may be able to leverage performance gains by having multiple files on multiple disks as opposed to just one file. Why not try using one file and see if it works, and what the performance is like? Here is a link for info on BULK INSERT: http://msdn.microsoft.com/en-us/library/ms188365.aspx


3

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 ...


2

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 ...


2

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 ...


2

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) select X.SomeID, B.Blob from SomeKeyTable X JOIN OPENROWSET ( BULK 'c:\myfile.txt', FORMATFILE = 'c:\myfileformat.txt' ) B ON X.AKey = B.AKey


2

John, normally it does not make sense to use dbms_redefinition in your case since you are not redefining a table. You are creating an extra schema. dbms_redefinition is meant for situations where for example your table T1 gets an other layout and this has to be done online. Application can stil use the old form of the table while you are completing the new ...


2

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 ...


2

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 ...


1

This is definitely a loaded question. Why ??? InnoDB caches data and index pages InnoDB may lock Clustered Indexes during Inserts/Updates InnoDB is multithreaded and can be tuned for accessing CPUs/Cores MyISAM only caches index pages MyISAM must always be read data from disk MyISAM can have dedicated caches per tables If you have plenty of RAM and ...


1

I tend to shy away from the FEDERATED storage engine for three(3) reasons: It only supports MyISAM DDL performed on the source table requires manually changing the FEDERATED table design on external services. Bulk operations against a FEDERATED table can become an instant mightmare !!! What to do ??? One possibility is to use mysqldump and merge them ...


1

Holy cow, you've got a lot of questions in here: How the table will be affected? During this import (updates and inserts) what could happen to other processes trying to read or write data to this table? With careful index design and partitioning, you can get away with minimal disruption to other queries. Discussing how to implement partitioning is a ...


1

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 ...


1

You can do this using the BCP format parameter. This will create a format file of the table for you. Based on your requirement, you can create XML/native/character formatted files. eg: bcp db_name.schema_name.table_name format nul -T -n -f format-file.fmt


1

You talk about your data already being in memory - I assume this is in the memory of your application and so still needs to be submitted to the SQL Server. You also mention many threads wanting to do the same thing. I would use MERGE or a combination of INSERT/UPDATEs. In order to use MERGE, you will need to submit this data first to a source table, and then ...



Only top voted, non community-wiki answers of a minimum length are eligible