为什么 Azure 数据库在事务方面表现更好 [英] Why does Azure Database perform better with transactions

问题描述

我们决定对 Azure 数据库使用 micro-orm.由于我们的业务只需要插入"和选择"，我们决定禁止所有代码管理的 SqlTransaction(数据没有并发问题).

We decided to use a micro-orm against an Azure Database. As our business only needs "inserts" and "selects", we decided to suppress all code-managed SqlTransaction (no concurrency issues on data).

然后，我们注意到我们的 Azure 数据库实例响应非常缓慢.rpc completed"事件发生的延迟是运行简单 sql 语句所需时间的数百倍.

Then, we noticed that our instance of Azure Database responded very slowly. The "rpc completed" event occured in delays that are hundreds times the time needed to run a simple sql statement.

接下来，我们使用 EF6 对代码进行了基准测试，我们看到服务器响应非常快.由于 EF6 实现了一个内置事务，我们决定在 micro-orm 上恢复 SqlTransaction (ReadCommited)，我们发现一切都很好.

Next, we benchmarked our code with EF6 and we saw that the server responded very quickly. As EF6 implements a built-in transaction, we decided to restore the SqlTransaction (ReadCommited) on the micro-orm and we noticed everything was fine.

Azure 数据库是否需要显式 SqlTransaction(由代码管理)?SqlTransaction 如何影响 Azure 数据库的性能?为什么要这样实现?

Does Azure Database require an explicit SqlTransaction (managed by code) ? How does the SqlTransaction influence Azure Database performances ? Why was it implemented that way ?

我将发布一些关于我们收集痕迹方式的更准确信息.似乎我们的 Azure 事件日志有时以纳秒为单位，有时以毫秒为单位.看起来很奇怪.

推荐答案

如果我理解您的要求正确，将多个 SQL 查询批处理到一个事务中将为您在任何 DBS 上提供更好的结果.每次插入/更新/删除后提交都会在不是为它设计的 DBS 上产生巨大的开销(例如 MySQL 上的 MyISAM).

If I understand what you are asking correctly, batching multiple SQL queries into one transaction will give you better results on any DBS. Committing after every insert/update/delete has a huge overhead on a DBS that is not designed for it (like MyISAM on MySQL).

如果你做的太多，它甚至会导致错误的磁盘刷新和抖动.我曾经有一个程序员每分钟向我的一个数据库提交数千个条目，每个条目都作为自己的事务，这导致服务器停止.

It can even cause bad flushes to disk and thrashing if you do too much. I once had a programmer committing thousands of entries to one of my DBs every minute, each as their own transactions, and it brought the server to a halt.

InnoDB 是 MySQL 的 2 种最流行的数据库格式之一，每秒只能提交 20-30 个事务(或者可能是 2-3 个......已经很长时间了)，因为每个事务都在以下时间刷新到磁盘ACID 合规性的终结.

InnoDB, one of 2 most popular database formats for MySQL, can only commit 20-30 transactions a second (or maybe it was 2-3... it's been a long time), as each is flushed to the disk at the end for ACID compliance.

这篇关于为什么 Azure 数据库在事务方面表现更好的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！