SQL计算速度问题(100分)

大家好!<br>有两个问题请教一下:<br>1.现有一个单价库,其储存产品单价(为SQL表,有几百条记录),现有一个临时表(格式为.DB,有几十万条记录),现要把名称查找将单价库中的单价套到临时表,目前是两种方案:<br>A.同时打开两个表进行搜索,<br>&nbsp; FOR I:=1 TO TABLE1.RECORDCOUNT DO<br>&nbsp; &nbsp; &nbsp; BEGIN<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ADOQUERY1.LOCATE...<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;IF ... THEN<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; BEGIN<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; END;<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;TABLE1.NEXT; <br>&nbsp; &nbsp; &nbsp; END;<br>B.只打开临时表,然后用ADOQUERY1.SQL去查找,有找到则套入:<br>&nbsp; FOR I:=1 TO TABLE1.RECORDCOUNT DO<br>&nbsp; &nbsp; &nbsp; BEGIN<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ADOQUERY1.SQL.TEXT:='SELECT TOP 1 ...';<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ADOQUERY1.OPEN; &nbsp;<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;IF ADOQUERY1.RECORDCOUNT&gt;0 THEN<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; BEGIN<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; END;<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;TABLE1.NEXT; <br>&nbsp; &nbsp; &nbsp; END;<br>以上两个方法速度太慢,还请各位高手提出更好的建议;<br>2.怎样将一个临时表的记录导入到现有SQL表中(表结构一样)<br>目前也是用循环,太慢<br>FOR I:=1 TO TABLE1.RECORDCOUNT DO<br>&nbsp; &nbsp; BEGIN<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ADOQUERY1.SQL.TEXT:='INSERT TABLE ...';ADOQUERY1.EXECSQL;<br>&nbsp; &nbsp; END;<br><br>谢谢!

用產生臨時表的語句在數據庫執行，然後用遊標解決。涉及到幾十萬條記錄不用在數據庫解決眞服了YOU!

请各位兄弟出高招

放在后台数据库中解决吧，几十万条数据的临时表，你的设计思路就有问题啊。

问题1：<br>tableA : AID, AValue<br>tableB : BID, AID, BValue （AID是两个表关联的依据）<br><br>// 示例，可能有小地方需自行修改。例如字段名称，属性。。。<br>FOR I:=1 TO TABLE1.RECORDCOUNT DO // TABLE1是小表tableA<br>&nbsp; &nbsp; &nbsp; BEGIN<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ADOQUERY1.SQL.text := 'Update TableB set BValue=' + <br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; table1.fieldbyname('AValue').asstring + <br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ' where AID=' + table1.fieldbyname('AID').asstring;<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ADOQUERY1.execsql; <br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;TABLE1.NEXT; <br>&nbsp; &nbsp; &nbsp; END;<br><br>需要循环几百次（小表的记录数），每次是批量更新，整体速度应该很快（表B在AID建索引）。其实，不用循环，做1个SQL也可以，不过语法复杂些，效率也基本没区别。<br><br>问题2：<br>&nbsp; &nbsp;ADOQUERY1.SQL.text := 'Insert into tableA select * from tmptable';<br>&nbsp; &nbsp;ADOQUERY1.execsql; <br>oracle可以使用上面的语句，其他数据库语法应该类似。<br>参考,从已知表创建一个相同结构的表(仅字段结构，主键，索引，触发器等不会创建)<br>create table NewTable as Select * from OldTable<br>OldTable中的所有数据也同时在NewTable中生成，可以用where子句限制，例如<br>create table NewTable as Select * from OldTable where 0=1<br>产生空表