记录插入方面的高效数据库访问

发布于 2024-12-08 00:25:43 字数 621 浏览 0 评论 0原文

我提前为“菜鸟”问题表示歉意，但从根本上来说，我在 SQL 的大多数方面仍然是新手。我的问题源于《SQL 的艺术》第二章的一部分。在标题为“稳定的数据库连接”的文章中，作者提到了向数据库插入大量行的几种方法。这是格式中的相应列表

Test; Results
Connect/Disconnect for each line in turn; 7.4 lines loaded per second  
Connect Once, all candidate lines individually inserted; 1,681 lines loaded per second  
Connect Once, all candidate lines inserted in arrays of 10 lines; 5,914 lines inserted per second  
Connect Once, all candidate lines inserted in arrays of of 100 lines; 9,190 lines inserted per second

此外，作者提到“甚至更快的直接加载技术”。

不幸的是，我不完全理解数组插入的想法（如果有的话）。任何人都可以详细说明作者提到的技术，在插入数组和他提到的其他“直接加载技术”方面吗？

原文

I apologize in advance for the "rookie" question, but I am still fundamentally a novice in most aspects of SQL.
My question stems from part of Chapter Two in The Art of SQL. In the passage titled, "Stable Database Connections", the author mentions several ways to insert a large number of rows into a database. Here is the corresponding list in the format

Test; Results
Connect/Disconnect for each line in turn; 7.4 lines loaded per second  
Connect Once, all candidate lines individually inserted; 1,681 lines loaded per second  
Connect Once, all candidate lines inserted in arrays of 10 lines; 5,914 lines inserted per second  
Connect Once, all candidate lines inserted in arrays of of 100 lines; 9,190 lines inserted per second

In addition, the author mentions "direct-loading techniques that are even faster."

Unfortunately, I do not completely understand the idea of insertion of arrays (if at all). Could anyone elaborate on the techniques the author referring to, in terms of inserting arrays and the other "direct loading techniques" that he referenced?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

娜些时光，永不杰束 2024-12-15 00:25:43

作者表明这

INSERT INTO Table (Column) 
VALUES ('myValue')

只会这么快。

作者表示

INSERT INTO Table (Column) 
VALUES ('myFirstValue'), 
('mySecondValue'), ...

，拥有 10 个 VALUES 项每秒插入 5,914 行，但如果单个 SQL 语句中有 100 个项，则加载率将高达每秒 9,190 行第二。

最后，“直接加载技术”指的是批量插入操作。您可以通过 Google msSQL 批量插入找到大量有关上传文件然后将其处理到表中的教程。

The author is showing that

INSERT INTO Table (Column) 
VALUES ('myValue')

is only going to be so fast.

It will be significantly faster to do

INSERT INTO Table (Column) 
VALUES ('myFirstValue'), 
('mySecondValue'), ...

The author is saying that having 10 VALUES items inserts 5,914 lines per second, but if there 100 items in the single SQL statement, then the load rate goes up to 9,190 lines per second.

Finally, the "direct-loading techniques" are referring to bulk insert operations. You can google msSQL bulk insert to find numerous tutorials for uploading a file and then processing it into a table.

回复收藏 0 原文

城歌 2024-12-15 00:25:43

将大量数据插入 SQLServer 表的最快方法是使用称为“批量插入”的过程。有几种方法可以做到这一点。 SQL Server 附带有一个名为 bcp 的命令行应用程序，有一个 TSQL 命令 BULK INSERT，还有一个 .NET sql 客户端对象 System.Data.SqlClient.SqlBulkCopy。

回复收藏 0 原文

~没有更多了~