执行计划提示 [英] Execution Plan Hinting

问题描述

我们有一个稍微不同寻常的情况发生，声明是传递给SQL的
，它由两部分组成。


BEGIN TRANSACTION

DELETE * FROM Whatever

BULK INSERT INTO Whatever ...（etc）

COMMIT TRANSACTION


第一个是删除数据，第二个是批量插入

的替换数据到该表中。我们看到的错误是违反主键（复合）的

。


只有当我们同时运行两个进程时才会发生违规。如果我们运行

一个，那么另一个，它运行正常。如果我们逐行设置，那么

工作正常。


我怀疑正在运行的执行计划是最多的

可能并行处理这两个部分，并且在插入发生时，记录仍然存在。截断不是

选项。出于性能原因添加了批量插入物。尝试批量插入有一个

选项，如果失败，请按行插入

行，但这远非理想。


我想我们可以把它包装成两个单独的交易

在一个声明中如下：


BEGIN TRANSACTION

DELETE * FROM Whatever

COMMIT TRANSACTION


BEGIN TRANSACTION

BULK INSERT INTO Whatever ... （等）

COMMIT TRANSACTION


这会给SQL提供足够的提示，告诉它处理它的订单

以便它完成我们打算而不是因为它看起来是最有效的b $ b b方法吗？


或者，有更好的方法吗？


我已经看到一些提示可以传递给SQL进行优化，但是我的理解是，最好相信优化器和

根据需要重新处理查询。


随着服务器的发展g两个处理器，一个人正在做一个部分是可行的吗？另一个处理器是另一个并行的处理器吗？

告诉它使用单个处理器值得看吗？ MAXDOP

1？


最后，我想，插入比

删除更快。这是正确的吗？


谢谢


Ryan

解决方案

> BEGIN TRANSACTION

DELETE * FROM WOUT
BULK INSERT INTO Whatever ...（etc）
COMMIT TRANSACTION


这些语句连续运行而不是平行的。你不应该得到一个

PK违规，除非文件中包含重复的数据，所以它看起来像你发现了一个错误。

。我可以在SQL 2000下重复这个但在SQL下没有问题

2005.


似乎有几种解决办法。一个是使用TRUNCATE：


BEGIN TRANSACTION

TRUNCATE TABLE Whatever

BULK INSERT INTO Whatever ...（etc）

COMMIT TRANSACTION


另一种是避免显式交易，以便每个语句都在

个别交易中：


DELETE FROM Whatever

BULK INSERT INTO Whatever ...（etc）


-

希望这会有所帮助。


Dan Guzman

SQL Server MVP


" Ryan" < RY ******** @ hotmail.com>在消息中写道

新闻：11 ********************** @ i39g2000cwa.googlegr oups.com ...我们已经得到了一个稍微不同寻常的场景，其中一个声明传递给SQL，它由两部分组成。

开始交易
删除*来自无论什么
BULK INSTO INTO Whatever。 ..（等）
COMMIT TRANSACTION

第一个是删除数据，第二个是将替换数据批量插入到该表中。我们看到的错误是违反主键（复合）。

只有在我们同时运行这两个进程时才会发生违规。如果我们运行
一个，那么另一个，它运行正常。如果我们逐行插入，它就可以正常工作。

我怀疑正在运行的执行计划最有可能并行处理这两个部分。记录仍然存在于插入发生的位置。截断不是
选项。出于性能原因添加了批量插入物。有一个尝试批量插入的选项，如果失败了，可以通过
线插入来完成这一行，但这远非理想。

我想我们可以将这个包含在两个单独的交易中，如下所示：

BEGIN TRANSACTION
DELETE * FROM Whatever
COMMIT TRANSACTION

> BEGIN TRANSACTION
BULK INSTO INTO Whatever ...（etc）
COMMIT TRANSACTION

这会给SQL提供足够的提示，告诉它处理它的顺序
这样它是否按照我们的意图完成而不是因为它看起来是最有效的方法？

或者，有没有更好的方法呢？

我已经看到一些提示可以传递给SQL进行优化，但我的理解是，最好相信优化器并根据需要重新处理查询。

如果服务器有两个处理器，那么一个人正在做一个部分而另一个处理器是可行的其他部分并行？
告诉它使用单个处理器值得看吗？ MAXDOP
1？

最后，我认为插入的处理比删除更快。这是正确的吗？

谢恩

应该提到这是在2000年。


一个问题是我们不能截断数据，因为它不是全部的，而是被替换的
，只是一小部分。这两个陈述是按顺序调用的，而且最近只是把它抬起来了。我们确实如我所提到的那样
可以解决这个问题，但是必须有一个更好的

方法而不是调用第二种方法如果有的话

错误。


数据不重复，因为我可以单独运行这两个部分并且

它们没问题。手动检查也证实了这一点。


Ryan

Ryan，


另一种可能的解决方法可能是：


批量插入临时表


BEGIN TRANSACTION

DELETE * FROM Whatever

INSERT INTO Whatever ... select ... from staging table

COMMIT TRANSACTION


blockquote>

We''ve got as slightly unusual scenario happening whereby a statement is
passed to SQL which consists of two parts.

BEGIN TRANSACTION
DELETE * FROM Whatever
BULK INSERT INTO Whatever...(etc)
COMMIT TRANSACTION

The first is a deletion of the data and the second is the bulk insert
of replacement data into that table. The error that we see is a
violation of the primary key (composite).

The violation only happens if we run both processes together. If we run
one, then the other, it works fine. If we set a line by line insert, it
works fine.

My suspicion is that the execution plan that is being run is most
likely working the two parts in parallel and that the records still
exist at the point that the insert is happening. Truncate is not an
option. The bulk insert was added for performance reasons. There is an
option of trying the bulk insert, and if that fails, do the line by
line insert, but it''s far from ideal.

I think we can probably wrap this into two individual transactions
within the one statement as follows :

BEGIN TRANSACTION
DELETE * FROM Whatever
COMMIT TRANSACTION

BEGIN TRANSACTION
BULK INSERT INTO Whatever...(etc)
COMMIT TRANSACTION

Will this give sufficient hint to SQL about the order it processes it
so that it completes as we intend and not as it sees being the most
efficient method ?

Or, is there a better approach to this ?

I''ve seen that some hints can be passed to SQL for optimizing, but my
understanding was that it was always better to trust the optimiser and
re-work the query as needed.

With the server having two processors, is it feasible that one is doing
one part and the other processor the other part in parallel ? Will
telling it to use a single processor be worthwhile looking at ? MAXDOP
1 ?

Finally, I''d imagine that the insert is quicker to process than the
deletion. Is this correct ?

Thanks

Ryan

解决方案

> BEGIN TRANSACTION

DELETE * FROM Whatever
BULK INSERT INTO Whatever...(etc)
COMMIT TRANSACTION
These statements run consecutively and not in parallel. You shouldn''t get a
PK violation unless the file contains duplicate data so it looks to me like
you found a bug. I can repro this under SQL 2000 but no problem under SQL
2005.

There seem to be couple of work-arounds. One is to use TRUNCATE:

BEGIN TRANSACTION
TRUNCATE TABLE Whatever
BULK INSERT INTO Whatever...(etc)
COMMIT TRANSACTION

Another is to avoid the explicit transaction so that each statement is in an
individual transaction:

DELETE FROM Whatever
BULK INSERT INTO Whatever...(etc)

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Ryan" <ry********@hotmail.com> wrote in message
news:11**********************@i39g2000cwa.googlegr oups.com... We''ve got as slightly unusual scenario happening whereby a statement is
passed to SQL which consists of two parts.

BEGIN TRANSACTION
DELETE * FROM Whatever
BULK INSERT INTO Whatever...(etc)
COMMIT TRANSACTION

The first is a deletion of the data and the second is the bulk insert
of replacement data into that table. The error that we see is a
violation of the primary key (composite).

The violation only happens if we run both processes together. If we run
one, then the other, it works fine. If we set a line by line insert, it
works fine.

My suspicion is that the execution plan that is being run is most
likely working the two parts in parallel and that the records still
exist at the point that the insert is happening. Truncate is not an
option. The bulk insert was added for performance reasons. There is an
option of trying the bulk insert, and if that fails, do the line by
line insert, but it''s far from ideal.

I think we can probably wrap this into two individual transactions
within the one statement as follows :

BEGIN TRANSACTION
DELETE * FROM Whatever
COMMIT TRANSACTION

BEGIN TRANSACTION
BULK INSERT INTO Whatever...(etc)
COMMIT TRANSACTION

Will this give sufficient hint to SQL about the order it processes it
so that it completes as we intend and not as it sees being the most
efficient method ?

Or, is there a better approach to this ?

I''ve seen that some hints can be passed to SQL for optimizing, but my
understanding was that it was always better to trust the optimiser and
re-work the query as needed.

With the server having two processors, is it feasible that one is doing
one part and the other processor the other part in parallel ? Will
telling it to use a single processor be worthwhile looking at ? MAXDOP
1 ?

Finally, I''d imagine that the insert is quicker to process than the
deletion. Is this correct ?

Thanks

Ryan

Should have mentioned this is in 2000.

One problem is that we can''t truncate the data as it''s not ALL of it
that is being replaced, just a small chunk. The two statements are
being called sequentially and has only reared it''s head recently. We do
have a way around it as I mentioned, but there must be a better
approach to this instead of calling a second method if there is an
error.

The data is not duplicated as I can run the two parts seperately and
they are fine. Manual checks also confirm this.

Ryan

Ryan,

another possible workaround might be to :

bulk insert into a staging table

BEGIN TRANSACTION
DELETE * FROM Whatever
INSERT INTO Whatever... select ... from staging table
COMMIT TRANSACTION

这篇关于执行计划提示的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！