📄 increasingbatchuptaesspeed.htm
字号:
<html>
<head>
<title>Increasing Inserts and Updates Speed</title>
</head>
<!--#include virtual="/inc/header.php"-->
<table width="100%" border="0" cellspacing="0" cellpadding="2" bgcolor="#FFFFFF">
<tr>
<td align="left">
<span style="font-family:Helvetica,Arial; font-size:12pt; color:#000000"><b>Increasing Inserts and Updates Speed
<br>
</b></span>
</td>
<td align="right">
<font face="Arial" size="2">
<a href="increasesqlperformance.htm">Previous</a>
<a href="overview.htm">Top</a>
<a href="internationalizationover.htm">Next</a>
</font>
</td>
</tr>
</table>
<br><br>
<span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000">
<br>
<b>The fastest way of inserting and updating</b>
<br>
<br>
<span style="font-family:Helvetica,Arial; font-size:10pt; color:#010100">The fastest way of batch inserting / updating / deleting is a buffered transaction. We recommend to call TABSDatabase.</span><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000"><span style="font-family:Helvetica,Arial; font-size:10pt; color:#010100"><a href=tabsdatabase_starttransaction.htm>StartTransaction</a></span></span><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000"><span style="font-family:Helvetica,Arial; font-size:10pt; color:#010100"> before bulk inserts and TABSDatabase.</span></span><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000"><span style="font-family:Helvetica,Arial; font-size:10pt; color:#010100"><a href=tabsdatabase_commit.htm>Commit</a></span></span><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000"><span style="font-family:Helvetica,Arial; font-size:10pt; color:#010100">(</span></span><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000"><span style="font-family:Helvetica,Arial; font-size:10pt; color:#010100"><b>False</b></span></span><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000"><span style="font-family:Helvetica,Arial; font-size:10pt; color:#010100">) after the end of the batch operation. The use of transaction can significantly increase performance of the batch operation.
<br>
<br>
The following example illustrates the fastest way of inserting 2000 records:
<br>
<br>
<table width="100%" border="0" cellpadding="0" cellspacing="0"><tr valign="top"><td width="14"></td><td><span style="font-family:Helvetica,Arial; font-size:10pt; color:#010100"></span></span></span><span style="font-family:Courier New; font-size:8pt; color:#000000"><span style="font-family:Courier New; font-size:8pt; color:#000080"><i>// insert data by portions of 2000 records</i></span></span><span style="font-family:Courier New; font-size:8pt; color:#000000">
<br>
</span></td></tr></table><table width="100%" border="0" cellpadding="0" cellspacing="0"><tr valign="top"><td width="14"></td><td><span style="font-family:Courier New; font-size:8pt; color:#000000">ABSDatabase1.StartTransaction;
<br>
</span></td></tr></table><table width="100%" border="0" cellpadding="0" cellspacing="0"><tr valign="top"><td width="14"></td><td><span style="font-family:Courier New; font-size:8pt; color:#000000"><b>for</b> i:=<span style="font-family:Courier New; font-size:8pt; color:#000080">1</span></span><span style="font-family:Courier New; font-size:8pt; color:#000000"> <b>to</b> <span style="font-family:Courier New; font-size:8pt; color:#000080">2000</span></span><span style="font-family:Courier New; font-size:8pt; color:#000000"> <b>do</b>
<br>
</span></td></tr></table><table width="100%" border="0" cellpadding="0" cellspacing="0"><tr valign="top"><td width="14"></td><td><span style="font-family:Courier New; font-size:8pt; color:#000000"> <b>with</b> ABSTable1 <b>do</b>
<br>
</span></td></tr></table><table width="100%" border="0" cellpadding="0" cellspacing="0"><tr valign="top"><td width="14"></td><td><span style="font-family:Courier New; font-size:8pt; color:#000000"> <b>begin</b>
<br>
</span></td></tr></table><table width="100%" border="0" cellpadding="0" cellspacing="0"><tr valign="top"><td width="14"></td><td><span style="font-family:Courier New; font-size:8pt; color:#000000"> Insert;
<br>
</span></td></tr></table><table width="100%" border="0" cellpadding="0" cellspacing="0"><tr valign="top"><td width="14"></td><td><span style="font-family:Courier New; font-size:8pt; color:#000000"> FieldByName(<span style="font-family:Courier New; font-size:8pt; color:#000080">'Name'</span></span><span style="font-family:Courier New; font-size:8pt; color:#000000">).AsString := <span style="font-family:Courier New; font-size:8pt; color:#000080">'John'</span></span><span style="font-family:Courier New; font-size:8pt; color:#000000">;
<br>
</span></td></tr></table><table width="100%" border="0" cellpadding="0" cellspacing="0"><tr valign="top"><td width="14"></td><td><span style="font-family:Courier New; font-size:8pt; color:#000000"> Post;
<br>
</span></td></tr></table><table width="100%" border="0" cellpadding="0" cellspacing="0"><tr valign="top"><td width="14"></td><td><span style="font-family:Courier New; font-size:8pt; color:#000000"> <b>end</b>;
<br>
</span></td></tr></table><table width="100%" border="0" cellpadding="0" cellspacing="0"><tr valign="top"><td width="14"></td><td><span style="font-family:Courier New; font-size:8pt; color:#000000">ABSDatabase1.Commit(False);
<br>
</span></td></tr></table><table width="100%" border="0" cellpadding="0" cellspacing="0"><tr valign="top"><td width="14"></td><td><span style="font-family:Courier New; font-size:8pt; color:#000000"></span></td></tr></table><span style="font-family:Courier New; font-size:8pt; color:#000000"></span><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000"><span style="font-family:Helvetica,Arial; font-size:10pt; color:#010100">
<br>
<br>
</span></span><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000"><b>How to speed up an UPDATE query
<br>
<br>
</b>If you are using several subqueries in an UPDATE query, you could try to transfomr your query like in the example below:
<br>
<br>
<b>Before optimization:</b>
<br>
<table width="100%" border="0" cellpadding="0" cellspacing="0"><tr valign="top"><td width="12"></td><td><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000">UPDATE Orders SET ShipToAddr1=(SELECT Addr1 FROM Customer WHERE CustNo=Orders.CustNo), ShipToAddr2= (SELECT Addr2 FROM Customer WHERE CustNo=Orders.CustNo) WHERE CustNo IN (1221, 2156)</span></span><span style="font-family:Courier New; font-size:10pt; color:#000000">
<br>
</span></td></tr></table><table width="100%" border="0" cellpadding="0" cellspacing="0"><tr valign="top"><td width="12"></td><td><span style="font-family:Courier New; font-size:10pt; color:#000000"></span></td></tr></table><span style="font-family:Courier New; font-size:10pt; color:#000000"></span><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000">
<br>
<b>After optimization:
<br>
</b><table width="100%" border="0" cellpadding="0" cellspacing="0"><tr valign="top"><td width="12"></td><td><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000">UPDATE Orders SET (ShipToAddr1,ShipToAddr2) = (SELECT Addr1, Addr2 FROM Customer WHERE CustNo=Orders.CustNo) WHERE CustNo IN (1221, 2156)</span></span><span style="font-family:Courier New; font-size:10pt; color:#000000">
<br>
</span></td></tr></table><table width="100%" border="0" cellpadding="0" cellspacing="0"><tr valign="top"><td width="12"></td><td><span style="font-family:Courier New; font-size:10pt; color:#000000"></span></td></tr></table><span style="font-family:Courier New; font-size:10pt; color:#000000"></span><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000">
<br>
</span></span>
<!--#include virtual="/inc/footer.php"-->
</html>
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -