调查 - SQL Server最慢的选择？ [英] Survey - SQL Server the slowest choice?

问题描述

我经常需要从多个来源导入数据，包括文本和XML文件。有时我问自己，为什么SQL服务器这么慢？目前，SQL bulkcopy命令在几秒钟后冻结我的应用程序（在15,000！11MB csv文件中大约600个数据集），而加载，解析和将数据转换为数据集消耗的时间很少，甚至连注意。唯一的瓶颈是sql bulkcopy命令。烦！当然，XML数据库不能替代关系数据库，它也具有某些优点。 SQL Server无法被忽略 - 考虑事务概念，标准化查询语法，存储过程和其他高级/专用功能。但是，考虑到与读取csv文件相比，未经调优/优化的SQL Server的性能，对我来说似乎是一个不好的笑话。在本地文件系统上，尽管XML语法开销很大，但XML数据库的执行情况要好得多。通过SAN访问的简单文本文件的使用超出了任何数据库解决方案的反应和事务速度和大小，以及随着时间推移的总内存使用量（我的经验和我谈论的是一个愚蠢和非缓存的解决方案）。 />


所以我的问题是，你对这个问题说了什么，你使用了什么替代品（Apache Derby，Berkeley，内存和XML，IBM DB2 pureXML）等等）并且有人曾经对此做过一些测试。是否有一个完整的实体框架支持的.net数据库？



好​​的，伙计们，我很高兴收到你的消息并得到它......

I often have to import data from multiple sources, including text and XML files. Sometimes I ask myself, why is the SQL server so extremely slow? Currently, a SQL bulkcopy command is freezing my application after a few seconds (at about 600 data sets out of 15,000! 11MB csv file), while loading, parsing and converting the data into a dataset consumes so little time, that you do not even notice. The only bottleneck is the sql bulkcopy command. Annoying! Of course, XML databases are not a substitute for relational databases, which also have certain advantages. SQL Servers can not be dismissed - think of the transaction concept, standardized query syntax, stored procedures and other advanced / specialized features. But, considering the performance of a non-tuned / optimized SQL Server compared to reading a csv- file, seems like a bad a joke to me. On the local file system, XML databases perform much better - despite the huge XML syntax overhead. The usage of simple text files, accessed over SAN, exceeds any DB Solution in reaction and transaction speed and size, as well as total memory usage over time (my experiences and I am talking about a dumb and non- cached solution).

So my question to you would be, what du you say on the subject, what alternatives do you use (Apache Derby, Berkeley, in-memory and XML, IBM DB2 pureXML, etc.) and has anyone ever done some test on this. Is there a .net Database with full Entity- Framework support out there?

OK, guys, I am glad hearing from you and get it going...

推荐答案

问题通常不是SQL Server是批量插入的不良选择，问题实际上在于必须在插入每个记录后重新平衡索引。您拥有的索引越多，数据库引擎处理它的工作就越多。一个常见的解决方案是删除索引，批量上传数据然后重新应用索引 - 重新创建索引一次要快得多。



这个问题没有只是说谎SQL Server，只是批量上传到任何大型索引数据库都有同样的效果。

The problem generally isn't that SQL Server is a poor choice for bulk inserts, the problem actually lies in having to rebalance the indexes after each record is inserted. The more indexes you have, the more work the database engine has to do to cope with it. A common solution to this is to drop the indexes, bulk upload the data and then reapply the indexes - recreating the indexes once is much faster.

This problem doesn't just lie with SQL Server, just doing a bulk upload into any of the big Indexed databases has the same effect.

试试 RaptorDB - 文档存储 [ ^ ]

我认为您已经过早地停止调查此问题。正如Pete所指出的，有些因素会影响整体表现。一个是索引，当然网络可能会导致延迟，数据类型转换（特别是隐含）等等。



所以我的建议不是要改变在深入挖掘并找出根本原因之前的产品。而不是使用批量复制，如果您使用类型定义，性能可能（或可能不）更好。为此，请查看创建类型 [ ^ ]或示例 [ ^ ]

I think that you have stopped investigating the problem too early. As Pete pointed out, there are factors that affect the overall performance. One is the indexing, of course the network may cause delay, data type conversions (especially implicit) and so on.

So my advice wouldn't be to change the product before you dig deeper and find out the root cause. Also instead of using bulk copy, the performance may (or may not) be better if you use type definition instead. For that, have a look at CREATE TYPE[^] or for an example How to pass multiple records to a Stored Procedure[^]

这篇关于调查 - SQL Server最慢的选择？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！