如何:将实际执行方法从“行"更改为“分批" -Azure SQL服务器 [英] How to: Change actual execution method from "row" to "batch" - Azure SQL Server
问题描述
我遇到了一些重大问题.将数据插入数据库时，我使用的是INSTEAD OF INSERT触发器，该触发器执行查询.
I am having some major issues. When inserting data into my database, I am using an INSTEAD OF INSERT trigger which performs a query.
在我的TEST数据库上，此查询只需要不到1秒的时间即可插入单行.但是，在生产中，此查询花费的时间更长(1行> 30秒).
On my TEST database, this query takes much less than 1 second for insert of a single row. In production however, this query takes MUCH longer (> 30 seconds for 1 row).
在比较两者的执行计划时，似乎存在一些CLEAR差异:
When comparing the Execution plans for both of them, there seems to be some CLEAR differences:
- 测试具有:实际执行方法:批处理"
- 产品具有:实际执行方法:行"
- 测试具有:实际行数:1"
- 产品具有:实际行数92.000.000"
不到一周前，生产运行类似于测试.但现在不再了-可悲的是.
Less than a week ago production was running similar to test. But not anymore - sadly.
你们中的任何一个可以帮助我找出原因吗?
Can any of you help me figure out why?
我相信，如果我能为两者获得相同的执行计划，那应该没问题.
I believe, if I can just get the same execution plan for both, it should be no problem.
推荐答案
有时使用查询提示OPTION(哈希联接)有助于强制查询计划使用批处理模式.以下使用AdventureWorks2012示例数据库的查询演示了我在说什么.
Sometimes using query hint OPTION(hash Join) helps to force a query plan to use batch processing mode. The following query that uses AdventureWorks2012 sample database demonstrates what I am saying.
SELECT s.OrderDate, s.ShipDate, sum(d.OrderQty),avg(d.UnitPrice),avg(d.UnitPriceDiscount)
FROM Demo d
join Sales.SalesOrderHeader s
on d.SalesOrderID=s.SalesOrderID
WHERE d.OrderQty>500
GROUP BY s.OrderDate,s.ShipDate
以上查询使用行模式.带有查询提示，然后使用批处理模式.
The above query uses row mode. With the query hint it then uses batch mode.
SELECT s.OrderDate, s.ShipDate, sum(d.OrderQty),avg(d.UnitPrice),avg(d.UnitPriceDiscount)
FROM Demo d
join Sales.SalesOrderHeader s
on d.SalesOrderID=s.SalesOrderID
WHERE d.OrderQty>500
GROUP BY s.OrderDate,s.ShipDate
OPTION(hash Join)
这篇关于如何:将实际执行方法从“行"更改为“分批" -Azure SQL服务器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
