应用程序在执行更新时超时 [英] Application times out while doing the updates

问题描述

这里是场景..


我正在运行DTS来收集Oracle数据库中的汇总信息

到SQL服务器。然后我有一个更新工作，从汇总表更新我的

交易表。


更新需要很长时间（约3分钟），即使它有大约1500行的
导致应用程序超时。我希望这个

工作能在不到一分钟的时间内完成。


关于提高绩效的想法。存储过程是一种方法吗？

（我已经使用了隔离，行提示等等。似乎没有什么工作）


AJ

解决方案

我建​​议你查看UPDATE查询的执行计划。或许

额外的索引或查询更改将大大加快它的速度。如果您需要额外的帮助，请发布表DDL（包括约束，

索引和触发器）以及查询和示例数据。

简单地将查询封装在proc中不太可能提高性能。


-

希望这会有所帮助。


Dan Guzman

SQL Server MVP

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

news：60 ************************** @ posting.google.c om ...

这里是场景..

我正在运行DTS来将Oracle数据库中的摘要信息收集到SQL服务器中。然后我有一个更新工作，它从汇总表中更新我的
事务表。

更新需要很长时间（约3分钟），即使它已经大约1500导致应用程序超时的行。我希望这项工作能在不到一分钟的时间内完成。

关于提高绩效的想法。存储过程是一种方法吗？
（我已经使用了隔离，行提示等等。似乎没有工作）

AJ

谢谢Dan，

这里是整个结构和查询


表A有这些栏目


a_id（pkey），ftq，break_offs，完成，已分配，s_id，end_date（已编入索引）

和其他几列


ftq，break_offs，completed，assigned是汇总的cols。从summary_table每小时更新一次

。


汇总表有这些cols。


a_id，ftq break_offs，完成，分配。


我发布此更新声明


更新套件

break_offs =（当break_offs< 0然后0，其他break_offs结束时选择案例

来自summary_table的break_offs，其中A.ano = summary_table.ano），

ftq =（从摘要中选择ftq，其中A. ano = summary_table.ano），

actual_assigned =（从summary_table中选择member_assigned

A.ano = summary_table.ano），

qualified_completes = （从summary_table中选择member_completes，其中

A.ano = summary_table.ano）

WHERE EXISTS（从summary_table中选择break_offs，ftq，其中A.ano =

summary_table.ano）和

end_date> = getdate（）。

-------------------- -----------------


除此之外我们还有存储过程，触发器相同的

table.But事情是工作运行时间超过15分钟和

停止一切。


再次感谢


AJ

AJ（aj*****@hotmail.com）写道：

这里是整个结构和查询

表A有这些列
（pkey），ftq，break_offs，完成，分配，s_id，end_date（索引）<很少有其他专栏

ftq，break_offs，完成，分配是汇总的cols。
每小时都会从summary_table更新。

摘要表中包含这些列。

a_id，ftq break_offs，完成，已分配。


请注意：Dan要求提供DDL。通过这个他意味着CREATE TABLE

语句。如果你想测试一个查询，它们很有用，而且它们比自由格式列表更容易阅读。在这种情况下，无论如何，

治愈似乎很简单：

UPDATE A SET
break_offs =（选择break_offs< 0然后0 else break_offs end ftq =（从摘要中选择ftq，其中A.ano = summary_table.ano），
actual_assigned =（从summary_table中选择member_assigned其中
A.ano = summary_table.ano），
qualified_completes =（从summary_table中选择member_completes，其中
A.ano = summary_table.ano）
WHERE EXISTS（从summary_table中选择break_offs，ftq，其中A .ano =
summary_table.ano）和
end_date> = getdate（）。

这应该更快：


UPDATE A

SET break_offs = s.break_offs< 0然后0

其他s.break_offs

结束，

ftq = s.ftq，

actual_assigned = s .member_assigned，

qualified_completes = s.member_completes，

来自A

加入summary_table s on a.ano = s.ano

和A.end_date> = getdate（）

这是使用MS SQL Server和Sybase专有的语法，

但是它是非常有效的，因为在你的版本中每个子查询

是单独评估的。


请允许我注意最后一个条件看起来很时髦。 getdate（）

返回当前时间，因此如果A.end_date只是一个日期，则此条件

可能无法达到预期效果。也就是说，end_date =今天的行将不会更新。


-

Erland Sommarskog，SQL Server MVP， es****@sommarskog.se


SQL Server SP3联机丛书
http://www.microsoft。 com / sql / techinf ... 2000 / books.asp

here''s the scenario..

I am running a DTS to collect the summarized info from Oracle database
into SQL server. I then have a update job which updates my
transactional table from the summarized table.

The update takes a very long time (~ 3 minutes)even though it has
around 1500 rows which causes the application to timeout. I want this
job to be done in less than a minute.

Thoughts on improving performance. Is stored procedure a way to go?
(I have used Isolation,row hints etc etc..nothing seems to be working)

AJ

解决方案

I suggest you review the execution plan of the UPDATE query. Perhaps
additional indexes or query changes will speed it up substantially. If you
need additional help, please post you table DDL (including constraints,
indexes and triggers) along with you query and sample data.

Simply encapsulating the query in a proc is unlikely to improve performance.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"AJ" <aj*****@hotmail.com> wrote in message
news:60**************************@posting.google.c om...

here''s the scenario..

I am running a DTS to collect the summarized info from Oracle database
into SQL server. I then have a update job which updates my
transactional table from the summarized table.

The update takes a very long time (~ 3 minutes)even though it has
around 1500 rows which causes the application to timeout. I want this
job to be done in less than a minute.

Thoughts on improving performance. Is stored procedure a way to go?
(I have used Isolation,row hints etc etc..nothing seems to be working)

AJ

Thanks Dan,
Here''s the entire structure and query

Table A has these columns

a_id(pkey),ftq,break_offs,completes,assigned,s_id, end_date (Indexed)
and few other columns

ftq,break_offs, completes,assigned are the summarized cols. which are
updated every hour from the summary_table.

Summary table has these cols.

a_id,ftq break_offs,completes,assigned.

I am issuing this update statement

UPDATE A SET
break_offs = (select case when break_offs<0 then 0 else break_offs end
break_offs from summary_table where A.ano = summary_table.ano),
ftq=(select ftq from summary_table where A.ano=summary_table.ano),
actually_assigned=(select member_assigned from summary_table where
A.ano=summary_table.ano),
qualified_completes=( select member_completes from summary_table where
A.ano=summary_table.ano)
WHERE EXISTS (select break_offs,ftq from summary_table where A.ano =
summary_table.ano) and
end_date>=getdate().
-------------------------------------

Apart from this we also have stored procs, trigger on the same
table.But the thing is the jobs runs 15 minutes past the hour and
stalls everything.

Thanks again

AJ

AJ (aj*****@hotmail.com) writes:

Here''s the entire structure and query

Table A has these columns

a_id(pkey),ftq,break_offs,completes,assigned,s_id, end_date (Indexed)
and few other columns

ftq,break_offs, completes,assigned are the summarized cols. which are
updated every hour from the summary_table.

Summary table has these cols.

a_id,ftq break_offs,completes,assigned.
Note here: Dan asked for the DDL. By this he means the CREATE TABLE
statements. These are useful if you want to test a query, and they
are also easier to read than a free-form list. In this case, the
cure appears simple enough anyway:
UPDATE A SET
break_offs = (select case when break_offs<0 then 0 else break_offs end
break_offs from summary_table where A.ano = summary_table.ano),
ftq=(select ftq from summary_table where A.ano=summary_table.ano),
actually_assigned=(select member_assigned from summary_table where
A.ano=summary_table.ano),
qualified_completes=( select member_completes from summary_table where
A.ano=summary_table.ano)
WHERE EXISTS (select break_offs,ftq from summary_table where A.ano =
summary_table.ano) and
end_date>=getdate().

This should be faster:

UPDATE A
SET break_offs = case when s.break_offs < 0 then 0
else s.break_offs
end,
ftq = s.ftq,
actually_assigned = s.member_assigned,
qualified_completes = s.member_completes,
FROM A
JOIN summary_table s ON a.ano = s.ano
and A.end_date >= getdate()

This is uses syntax that is proprietary to MS SQL Server and Sybase,
but it is a lot more effecient, since in your version each subquery
is evaluated separately.

Permit me also to note that the last condition looks funky. getdate()
returns the current time, so if A.end_date is a date only, this condition
may not do what you expect. That is, rows where end_date = today will
not be updated.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

这篇关于应用程序在执行更新时超时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！