为什么Azure数据库在交易中表现更好 [英] Why does Azure Database perform better with transactions

问题描述

我们决定对Azure数据库使用微型微软。由于我们的业务只需要插入和选择，我们决定禁止所有代码管理的 SqlTransaction （数据上没有并发问题）。

然后，我们注意到我们的Azure数据库实例响应很慢。 rpc完成事件发生在运行简单sql语句所需时间的几十倍的延迟。

接下来，我们对我们进行了基准测试代码与EF6，我们看到服务器响应很快。由于EF6实现了一个内置的事务，我们决定在微型orm上恢复 SqlTransaction （ReadCommited），我们注意到一切都很好。

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

编辑：我将发布一些关于我们收集痕迹的更准确的信息。似乎我们的Azure事件日志有时以毫秒为单位表示，有时以毫秒为单位。似乎很奇怪。

解决方案

如果我明白你正在问的是什么，将多个SQL查询批处理成一个事务在任何DBS上给您更好的结果。在每次插入/更新/删除后，在不是为它设计的DBS上（如MyISAM在MySQL上）具有巨大的开销。

甚至可能会导致不良刷新如果你做的太多，磁盘和thrashing。我曾经有一个程序员每分钟将数千个条目提交给我的一个DB，每个作为自己的事务，并且使服务器停止。

InnoDB，一个MySQL的2种最受欢迎​​的数据库格式，每秒只能提交20-30个事务（或者也许是2-3 ...这是很长时间），因为每个都刷新到最后的硬盘符合ACID。

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

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.

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.

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

EDIT : I am going to post some more precise information about the way we collected traces. It seems our Azure events logs sometimes express in nanoseconds, sometimes in milliseconds. Seems so weird.

解决方案

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, 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屋！