I frequently import a 2GB csv file with 24 million rows to SQL Server. I import this as text and then carry out the conversion via SELECT xxx INTO.
我经常向SQL Server导入一个包含2400万行的2GB csv文件。我将其导入为文本,然后通过SELECT xxx INTO执行转换。
Will the conversion use less memory be used if I split this into separate queries on different sections of the data?
如果我将其拆分为数据的不同部分的单独查询,那么转换是否会使用更少的内存?
1 个解决方案
#1
3
To be honest, it may be better not to use that method at all, but to instead use BULK INSERT as specified here:
说实话,最好不要使用该方法,而是使用BULK INSERT,如下所示:
Handling Bulk Insert from CSV to SQL
处理从CSV到SQL的批量插入
It is quite simple though:
这很简单:
BULK INSERT dbo.TableForBulkData
FROM 'C:\BulkDataFile.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
If you're doing it through C#, then you can use the SqlBulkCopy library, or if you need to do it from command line, you can always use BCP.
如果您是通过C#进行的,那么您可以使用SqlBulkCopy库,或者如果您需要从命令行执行此操作,则可以始终使用BCP。
Note, the method you're currently using is up to 10 times slower:
请注意,您当前使用的方法速度最慢要慢10倍:
QUOTE FROM ARTICLE:
文章引用:
Data can be inserted to the database from a CSV file using the conventional SQLCommand class. But this is a very slow process. Compared to the other three ways I have already discussed, this process is at least 10 times slower. It is strongly recommended to not loop through the CSV file row by row and execute SqlCommand for every row to insert a bulk amount of date from the CSV file to the SQL Server database.
可以使用传统的SQLCommand类从CSV文件将数据插入数据库。但这是一个非常缓慢的过程。与我已经讨论过的其他三种方法相比,这个过程至少慢了10倍。强烈建议不要逐行循环访问CSV文件,并为每一行执行SqlCommand,以便将大量日期从CSV文件插入SQL Server数据库。
#1
3
To be honest, it may be better not to use that method at all, but to instead use BULK INSERT as specified here:
说实话,最好不要使用该方法,而是使用BULK INSERT,如下所示:
Handling Bulk Insert from CSV to SQL
处理从CSV到SQL的批量插入
It is quite simple though:
这很简单:
BULK INSERT dbo.TableForBulkData
FROM 'C:\BulkDataFile.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
If you're doing it through C#, then you can use the SqlBulkCopy library, or if you need to do it from command line, you can always use BCP.
如果您是通过C#进行的,那么您可以使用SqlBulkCopy库,或者如果您需要从命令行执行此操作,则可以始终使用BCP。
Note, the method you're currently using is up to 10 times slower:
请注意,您当前使用的方法速度最慢要慢10倍:
QUOTE FROM ARTICLE:
文章引用:
Data can be inserted to the database from a CSV file using the conventional SQLCommand class. But this is a very slow process. Compared to the other three ways I have already discussed, this process is at least 10 times slower. It is strongly recommended to not loop through the CSV file row by row and execute SqlCommand for every row to insert a bulk amount of date from the CSV file to the SQL Server database.
可以使用传统的SQLCommand类从CSV文件将数据插入数据库。但这是一个非常缓慢的过程。与我已经讨论过的其他三种方法相比,这个过程至少慢了10倍。强烈建议不要逐行循环访问CSV文件,并为每一行执行SqlCommand,以便将大量日期从CSV文件插入SQL Server数据库。