increasesqlperformance.htm

Absolute Database 5.12 src. Absolute Database lets you forget the Borland Database Engine (BDE). Th

<br>

<br>
</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>Available indexes for ORDER BY clause</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">
<br>

<br>
If you want to speed up a "live" SELECT from a single table with ORDER BY clause, you can create a compound index for ORDER BY fields.
<br>
For example if you would like to increase the speed of the query:
<br>
</span></span><span style="font-family:Helvetica,Arial; font-size:4pt; color:#000000"><span style="font-family:Helvetica,Arial; font-size:4pt; color:#010100">
<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:4pt; color:#010100"></span></span></span><span style="font-family:Courier New; font-size:8pt; color:#000000"><b>SELECT</b>&nbsp;*&nbsp;<b>FROM</b>&nbsp;Employee&nbsp;<b>ORDER</b>&nbsp;<b>BY</b>&nbsp;LastName,&nbsp;FirstName
&nbsp;<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:Courier New; font-size:4pt; color:#000000">
<br>
</span><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000"><span style="font-family:Helvetica,Arial; font-size:10pt; color:#010100">you can do it by creating the following compound index: 
<br>
</span></span><span style="font-family:Helvetica,Arial; font-size:4pt; color:#000000"><span style="font-family:Helvetica,Arial; font-size:4pt; color:#010100">
<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:4pt; color:#010100"></span></span></span><span style="font-family:Courier New; font-size:8pt; color:#000000">ABSTable1.AddIndex(<span style="font-family:Courier New; font-size:8pt; color:#000080">'idxLastNameFirstName'</span></span><span style="font-family:Courier New; font-size:8pt; color:#000000">,&nbsp;'<span style="font-family:Courier New; font-size:8pt; color:#000080">LastName;FirstName',&nbsp;[]</span></span><span style="font-family:Courier New; font-size:8pt; color:#000000">);
&nbsp;<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"><span style="font-family:Helvetica,Arial; font-size:10pt; color:#010100"><b>Available indexes for GROUP BY clause
<br>

<br>
</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">To get a better performance for SELECT from a single table with GROUP BY clause, you can create a compound index for GROUP BY fields.
<br>
For example if you want to speed up the query:
<br>
</span></span><span style="font-family:Helvetica,Arial; font-size:4pt; color:#000000"><span style="font-family:Helvetica,Arial; font-size:4pt; color:#010100">
<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:4pt; color:#010100"></span></span></span><span style="font-family:Courier New; font-size:8pt; color:#000000"><b>SELECT</b>&nbsp;*&nbsp;<b>FROM</b>&nbsp;Employee&nbsp;<b>GROUP</b>&nbsp;<b>BY</b>&nbsp;FirstName
&nbsp;<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:Courier New; font-size:4pt; color:#000000">
<br>
</span><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000"><span style="font-family:Helvetica,Arial; font-size:10pt; color:#010100">you can create the following index: 
<br>
</span></span><span style="font-family:Helvetica,Arial; font-size:4pt; color:#000000"><span style="font-family:Helvetica,Arial; font-size:4pt; color:#010100">
<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:4pt; color:#010100"></span></span></span><span style="font-family:Courier New; font-size:8pt; color:#000000">ABSTable1.AddIndex(<span style="font-family:Courier New; font-size:8pt; color:#000080">'idxFirstName'</span></span><span style="font-family:Courier New; font-size:8pt; color:#000000">,&nbsp;'<span style="font-family:Courier New; font-size:8pt; color:#000080">FirstName',&nbsp;[]</span></span><span style="font-family:Courier New; font-size:8pt; color:#000000">);
&nbsp;<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"><span style="font-family:Helvetica,Arial; font-size:10pt; color:#010100"><b>Select from in-memory tables
<br>

<br>
</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">Your query perofrmance could be increased also if you will move all data from disk tables to </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=in_memorytables.htm>in-memory tables</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"> and you will perform a query using in-memory copies of the disk tables (Set TABSQuery.</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=tabsquery_inmemory.htm>InMemory</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"> property to True before query execution).
<br>
</span></span><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000"><b>
<br>

<br>
</b><span style="font-family:Helvetica,Arial; font-size:10pt; color:#010100"><b>SELECT INTO vs INSERT SELECT
<br>
</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">
<br>
In some cases </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>SELECT ... INTO </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"><b><i>some_table</i></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"> query runs faster than </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>INSERT INTO some_table (SELECT ...)</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"><i>, </i></span></span><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000"><span style="font-family:Helvetica,Arial; font-size:10pt; color:#010100">in another cases</span></span><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000"><span style="font-family:Helvetica,Arial; font-size:10pt; color:#010100"><i> </i></span></span><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000"><span style="font-family:Helvetica,Arial; font-size:10pt; color:#010100">INSERT INTO is faster. Please note that the </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=tabsquery_requestlive.htm>RequestLive</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"><i> </i></span></span><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000"><span style="font-family:Helvetica,Arial; font-size:10pt; color:#010100">property could have an impact on a performance of these queries.
<br>
</span></span><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000"><b>
<br>

<br>
</b><span style="font-family:Helvetica,Arial; font-size:10pt; color:#010100"><b>See also</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"> </span></span><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000"><a href=increasingbatchuptaesspeed.htm>Increasing Inserts and Updates Speed</a><span style="font-family:Helvetica,Arial; font-size:10pt; color:#010100"> and </span></span><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000"><a href=improvingoverallperformance.htm>Improving Overall Performance</a><span style="font-family:Helvetica,Arial; font-size:10pt; color:#010100"> 
<br>
</span></span><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000"><b>
<br>
</b></span></span>

<!--#include virtual="/inc/footer.php"--> 
</html>