网站 › 『 Sybase ASE 』 › 高手帮忙，数据库记录过大，查询无响应

henry406 发表于 2008-2-27 14:25:01

高手帮忙，数据库记录过大，查询无响应

<div style="FLOAT: right" align="right"><a title="评分 0" href="http://www.itpub.net/misc.php?action=viewratings&amp;tid=945496&amp;pid=9703950"></a></div>
<div class="t_msgfont" id="message9703950">服务器一张表中记录数达600多万条，我执行查询操作时，半天没有响应<br/><br/>其中索引在建表的时候已建，是不是索引创建的不合理造成的，需不需要重建，重建的话这么大的数据库量会不会影响查询的结果！<br/><br/>SQL语句执行计划如下，可惜小弟看不太明白，请高手指点一二了！！！<br/>STEP 1<br/>&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;The type of query is INSERT.<br/>&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;The update mode is direct.<br/>&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;Worktable1 created, in allpages locking mode, for ORDER BY.<br/><br/>&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;FROM TABLE<br/>&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;ZN3ta03<br/>&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;Nested iteration.<br/>&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;Using Clustered Index.<br/>&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;Index : ZN3ta03_8278629851<br/>&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;Forward scan.<br/>&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;Positioning by key.<br/>&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;Keys are:<br/>&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;offinum&nbsp;&nbsp;ASC<br/>&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;Using I/O Size 8 Kbytes for data pages.<br/>&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;With LRU Buffer Replacement Strategy for data pages.<br/>&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;TO TABLE<br/>&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;Worktable1.<br/><br/>&nbsp; &nbsp; STEP 2<br/>&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;The type of query is SELECT.<br/>&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;This step involves sorting.<br/><br/>&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;FROM TABLE<br/>&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;Worktable1.<br/>&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;Using GETSORTED<br/>&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;Table Scan.<br/>&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;Forward scan.<br/>&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;Positioning at start of table.<br/>&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;Using I/O Size 8 Kbytes for data pages.<br/>&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;With MRU Buffer Replacement Strategy for data pages.<br/><br/></div>

ehxz 发表于 2008-2-27 15:28:41

600W还不算多，检查一下索引（不行就删除重建一下也许会管用），DBCC一下表。。

henry406 发表于 2008-2-27 15:59:33

删除索引重建，不知道对原始数据有没有影响啊

ehxz 发表于 2008-2-27 16:52:26

重建索引，不会对数据有影响的。

诺华 发表于 2008-3-22 22:45:41

<p align="left">可能是索引建的不合适，也可能是索引统计信息需要更新了。最好把SQL语句写出来，还可以使用dbcc trcaeon（302）310，317来看数据库如何选择索引的</p>
<p align="left">我估计是你的SQL语句比较复杂，从查询计划上看，它排序了，这个很耗时间</p>

查看完整版本: 高手帮忙，数据库记录过大，查询无响应