运行内存密集型查询的问题 [英] problems running memory intensive queries
问题描述
i在SQL上运行内存密集型查询时遇到一个奇怪的问题
服务器。
我正在做在另一张表上更新了900万条记录
表
有50条记录。
我正在运行的查询是
更新表1
设置var1 = b.var2
来自table2 b
其中key1 = b .key1
此查询永远变为现实。我曾经以为我的机器出现了问题,但是一旦出现问题,它就会在16分钟内完成。
我正在运行1 Ghz PIII,512 MB内存。
关于可能出现什么问题的想法?
问候
Rishi
Hi,
i am having a strange problem running memory intensive queries on SQL
server.
I am doing an update on a table with 9 million records from another
table
with 50 records.
the query i am running is
update table1
set var1 = b.var2
from table2 b
where key1=b.key1
this query hanges for ever. I had thought that there was a problem with
my machine...but once out of the blue it ran in 16 minutes.
I am running a 1 Ghz PIII with 512 MB of memory.
Any ideas as to what could be the issue ?
Regards
Rishi
推荐答案
忘了提...数据库的大小约为4 GB。
干杯
Rishi
forgot to mention...the size of the database is around 4 gb.
Cheers
Rishi
>从有关您的情况的有限信息,我会说对于初学者
检查以确保定义了正确的索引。索引是sql生活的关键
索引的一个很好的候选者是你的where子句中的列
和你的set子句中的列....
-dave
>From the limited info about your situation, I would say for starters
check to make sure you have proper indexes defined. Indexes are the key
to life in sql...
A good candidate for your indexes are the column in your where clause
and the column in your set clause....
-dave
(ri ** ********@yahoo.com)写道:
(ri**********@yahoo.com) writes:
我在SQL服务器上运行内存密集型查询时遇到了一个奇怪的问题。
我我正在用一张包含50条记录的另一张表中的900万条记录对表进行更新。
我正在运行的查询是
更新表1
从表2中设置var1 = b.var2
其中key1 = b.key1
此查询永远存在。我曾经以为我的机器出现了问题...但是一旦突然出现它就会在16分钟内运行。
i am having a strange problem running memory intensive queries on SQL
server.
I am doing an update on a table with 9 million records from another
table with 50 records.
the query i am running is
update table1
set var1 = b.var2
from table2 b
where key1=b.key1
this query hanges for ever. I had thought that there was a problem with
my machine...but once out of the blue it ran in 16 minutes.
是否有UPDATE命中全部九百万行?在这种情况下,它肯定需要一些时间来执行查询。如果您要更新的列是聚集索引的一部分,情况会更糟。
。如果桌面上有触发器,它会变得更糟糕。
对于如此巨大的更新,运行更新的情况并不少见批次。
在这种情况下,每个键值可能有一个更新可能是一个想法,如果有均匀的分布,至少
。
-
Erland Sommarskog,SQL Server MVP, es **** @ sommarskog.se
SQL Server SP3联机丛书
http://www.microsoft.com/sql/techinf...2000/books.asp
这篇关于运行内存密集型查询的问题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!