我不明白这个“服务器超时” [英] I don't understand this "server timeout"
问题描述
我在SQL Server 2000中做更新语句。
我有一个超过1600万行的表。
它来自于几百个分隔的文本文件，
和两个列是文件ID（int）和Line＃（int）
结构是X12（835）。对于那些不熟悉的人，
每个文件都有一对多的BPR行;每条BPR线路都有许多CLP线路，每条线路都有零到几美元的SVC线路，每条线路都有零到多条CAS线路。
通过企业管理器MMC处理这个问题，
a我试过的很多东西都有超时。
所以，我索引文件ID，行号和行类型，以及
创建了一个新表，其中只包含我知道的列
我将在最终输出中需要 - 来自
提到的一些行类型，加上行号
和这些行的公共文件ID。
我索引每一列那张桌子，我以为我可能会在
上搜索。
我用
上的选择加载了31,000行CAS行的子集。这花了不到一分钟。
我用最高的BPR行号更新了每行，而不是CASE行号。大约一分钟。还不错，因为有最后的情况，比较的数字是1600万次，是31000次。
当然，索引应该有所帮助加上它可以通过
相同文件和BPR＃< CAS＃标准。
但理论上下一次更新应该更快：每一行现在都有
a BPR＃和一个CAS＃我告诉它要查找最高的CLP数量
BETWEEN这两个。所以它应该有一小部分来搜索
到。然而它认为大约五分钟然后宣布暂停。
有什么建议吗？
-
Wes Groleau
用千分尺测量，用粉笔标记，用斧头切割。
I was doing update statements in SQL Server 2000.
I have a table with over 16 million rows.
It came from several hundred delimited text files,
and two of the columns are file ID (int) and Line # (int)
Structure is X12 (835). For those unfamiliar with that,
each file has one to many BPR lines; each BPR line has
zero to many CLP lines, each of those has zero to many
SVC lines, each of those has zero to many CAS lines.
Working with this through the Enterprise Manager MMC,
a lot of things I tried got timeouts.
So, I indexed File ID, Line number, and line type, and
created a new table containing only the columns I knew
I would need in the final output--selected fields from
some of the line types mentioned, plus the line numbers
and common file ID for those rows.
I indexed every column in that table that I thought I might
search on.
I loaded it with 31 thousand rows using a select on a
subset of the CAS rows. That took far less than a minute.
I updated each row with the highest BPR line number not higher
than the CASE line number. About a minute. Not bad, with having
the worst case number of comparisons being 16 million times 31 thousand.
Of course, the indexing should help plus it can be narrowed down by
the "same file" and BPR # < CAS # criteria.
But the next update should theoretically be faster: each row now has
a BPR # and a CAS # and I am telling it to find the highest CLP number
BETWEEN those two. So it should have a MUCH smaller set of to search
through. Yet it thinks for about five minutes and then announces a timeout.
Any suggestions?
--
Wes Groleau
Measure with a micrometer, mark with chalk, and cut with an axe.
推荐答案
Wes Groleau（gr ********* *@freeshell.org）写道：
Wes Groleau (gr**********@freeshell.org) writes:
通过企业管理器MMC使用它，
a很多东西我试过超时。
所以，我索引了文件ID，行号和行类型，并且
创建了一个只包含我知道的列
我需要在最终输出中 - 选择字段来自
提到的一些线型，加上行号
这些行的常用文件ID。
我将该表中的每一列编入索引，我认为我可能会搜索
。
我使用CAS行的
子集上的select加载了31,000行。这花了不到一分钟。
我用最高的BPR行号更新了每行，而不是CASE行号。大约一分钟。还不错，因为有最后的情况，比较的数字是1600万次，是31000次。
当然，索引应该有所帮助加上它可以通过
相同文件和BPR＃< CAS＃标准。
但理论上下一次更新应该更快：每行现在都有一个
BPR＃和一个CAS＃我告诉它找到最高的CLP号码
BETWEEN这两个。所以它应该有一小部分来搜索
到。然而它认为大约五分钟然后宣布一个
超时。
Working with this through the Enterprise Manager MMC,
a lot of things I tried got timeouts.
So, I indexed File ID, Line number, and line type, and
created a new table containing only the columns I knew
I would need in the final output--selected fields from
some of the line types mentioned, plus the line numbers
and common file ID for those rows.
I indexed every column in that table that I thought I might
search on.
I loaded it with 31 thousand rows using a select on a
subset of the CAS rows. That took far less than a minute.
I updated each row with the highest BPR line number not higher
than the CASE line number. About a minute. Not bad, with having
the worst case number of comparisons being 16 million times 31 thousand.
Of course, the indexing should help plus it can be narrowed down by
the "same file" and BPR # < CAS # criteria.
But the next update should theoretically be faster: each row now has a
BPR # and a CAS # and I am telling it to find the highest CLP number
BETWEEN those two. So it should have a MUCH smaller set of to search
through. Yet it thinks for about five minutes and then announces a
timeout.
不幸的是，这里的工作很少。 X12告诉我
没什么。无论如何，你添加了许多我不知道的索引。但是，让我指出一点：索引单列是
远远不够。通常你需要复合索引。
为了能够说出更有用的东西，我将能够看到表格的CREATE TABLE语句
。 （或者只有一个？），
以及索引，包括密钥。当然，我需要
才能知道你的UPDATE语句。如果有任何触发器，我还需要查看这些触发器。
-
Erland Sommarskog，SQL Server MVP， es****@sommarskog.se
SQL Server联机丛书2005年
http：// www.microsoft.com/technet/pro...ads/books.mspx
SQL Server 2000联机丛书
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Unforunately there is very little here to work from. X12 tells me
nothing. And in any case you have added a number of indexes that are
unknown to me. But let me point out thing: indexing single columns is
far from always sufficient. Often you need composite indexes.
To be able to say something more useful, I would be able to see
the CREATE TABLE statements for the tables. (Or is there only one?),
as well as the indexes, including keys. And of course I would need
to know your UPDATE statements. And if there are any triggers, I
need to see those as well.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Erland Sommarskog写道：
Erland Sommarskog wrote:
不幸的是，这里的工作很少。 X12告诉我
没什么。无论如何，你添加了许多我不知道的索引。但是，让我指出一点：索引单列是
远远不够。通常你需要复合索引。
为了能够说出更有用的东西，我将能够看到表格的CREATE TABLE语句
。 （或者只有一个？），
以及索引，包括密钥。当然，我需要
才能知道你的UPDATE语句。如果有任何触发器，我还需要查看这些触发器。
Unforunately there is very little here to work from. X12 tells me
nothing. And in any case you have added a number of indexes that are
unknown to me. But let me point out thing: indexing single columns is
far from always sufficient. Often you need composite indexes.
To be able to say something more useful, I would be able to see
the CREATE TABLE statements for the tables. (Or is there only one?),
as well as the indexes, including keys. And of course I would need
to know your UPDATE statements. And if there are any triggers, I
need to see those as well.
没有触发器。我试图编写表格脚本（实际上我试图编写脚本
一个类似的表来保存自己的一些打字）但是向导没有保存文件
并且没有给出任何错误消息。所以我复制了表格并使用了GUI来
去除我不需要的字段/添加其他一些
所有引用的字段UPDATE语句被编入索引。
更新语句几乎相同 - 区别在于
UPDATE输出SET xyz =（SELECT Max（Seg_Nbr）FROM Raw_Segs
WHERE Output.FID = Raw_Segs AND Seg_Nbr BETWEEN abc和pqr）
xyz，abc，& pqr是不同的列，这样pqr - abc是
更广泛的范围;
因超时而死亡的那个更窄。
-
Wes Groleau
A悲观主义者说玻璃杯已经空了一半。
一位乐观主义者说玻璃杯是半满的。
工程师说有人制作玻璃杯/>
是它需要的两倍大。
No triggers. I tried to script the table (actually I tried to script
a similar table to save myself some typing) but the wizard saved no file
and gave no error message. So I copied the table and used the GUI to
strip out the fields I didn''t need/add a few others
All the fields referenced in the UPDATE statements are indexed.
The update statements are almost identical--the difference is that in
UPDATE Output SET xyz = (SELECT Max(Seg_Nbr) FROM Raw_Segs
WHERE Output.FID = Raw_Segs AND Seg_Nbr BETWEEN abc and pqr)
xyz, abc, & pqr are different columns, such that pqr - abc is
a wider range in the one that works; narrower in the one that
dies with timeout.
--
Wes Groleau
A pessimist says the glass is half empty.
An optimist says the glass is half full.
An engineer says somebody made the glass
twice as big as it needed to be.
Erland Sommarskog写道：
Erland Sommarskog wrote:
为了能够说出更有用的内容，我将能够看到表格的CREATE TABLE语句
。 （或者只有一个？），
以及索引，包括密钥。当然，我需要
才能知道你的UPDATE语句。如果有任何触发器，我还需要查看这些触发器。
To be able to say something more useful, I would be able to see
the CREATE TABLE statements for the tables. (Or is there only one?),
as well as the indexes, including keys. And of course I would need
to know your UPDATE statements. And if there are any triggers, I
need to see those as well.
很抱歉发送太快了。
正如我所说，我用GUI进行了表格设计但是它相当于
（CAS_Seg int，
SVC_Seg int，
CLP_Seg int，
BPR_Seg int，
其他字段）
首先加载CAS_Seg，从Raw_Segs视图中插入INSERT。
然后BPR_Seg使用最高的Seg_Nbr lass比
中的CAS_Seg更新相同的文件。工作。
然后我刚发送的一个更新被尝试并超时。
-
Wes Groleau
------
大多数女性宁愿拥有美丽而不是大脑的原因是
他们知道大多数男人可以看得比他们想象的要好。
- 詹姆斯·多布森
Sorry for the too-soon send.
As I said, I did the table design with the GUI but it would be equivalent to
( CAS_Seg int,
SVC_Seg int,
CLP_Seg int,
BPR_Seg int,
other fields )
CAS_Seg is loaded first, with an INSERT from a view of Raw_Segs.
Then BPR_Seg is updated with the highest Seg_Nbr lass than CAS_Seg in
the same file. Works.
Then one of the updates I just sent is tried and times out.
--
Wes Groleau
------
"The reason most women would rather have beauty than brains is
they know that most men can see better than they can think."
-- James Dobson
这篇关于我不明白这个“服务器超时”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
