increasesqlperformance.htm

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

&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">the best way to speed it up is to create the following case-sensitive 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">'idxCityState'</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">'City;State',&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>
If you need to get a better performance for 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;customer&nbsp;<b>WHERE</b>&nbsp;Upper(City)='KAPAA&nbsp;KAUAI'
&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">the best way to speed it up is to create the following case-insensitive 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">'idxCity_nocase'</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">'City',&nbsp;[ixCaseInsensitive]</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 JOIN conditions</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>
To improve a JOIN query, please check that each field from JOIN conditions has an index.
<br>
For example if you would like to improve the performance 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&nbsp;Event_Name,Venue&nbsp;FROM&nbsp;Events&nbsp;e&nbsp;JOIN&nbsp;Venues&nbsp;v&nbsp;ON&nbsp;(e.VenueNo&nbsp;=&nbsp;v.VenueNo)</b>
&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 indexes: 
<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">VenuesTable.AddIndex(<span style="font-family:Courier New; font-size:8pt; color:#000080">'idxVenueNo'</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">'VenueNo',&nbsp;[ixPrimary]</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">EventsTable.AddIndex(<span style="font-family:Courier New; font-size:8pt; color:#000080">'idxVenueNo'</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">'VenueNo',&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>Rewriting query with OR conditions as a UNION</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>
Absolute DB cannot use indexes to improve performance of a query with OR conditions. You can speedup your
<br>
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&nbsp;*&nbsp;FROM&nbsp;table&nbsp;WHERE&nbsp;(Field1&nbsp;=&nbsp;'Value1')&nbsp;OR&nbsp;(Field2&nbsp;=&nbsp;'Value2')</b>
&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">by creating indexes on each field in the above conditions and by using a UNION operator instead
<br>
of using OR:
<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"><b>SELECT</b>&nbsp;...&nbsp;<b>WHERE</b>&nbsp;<b>Field1&nbsp;=&nbsp;'Value1'</b>
&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"><b>UNION</b>
&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"><b>SELECT</b>&nbsp;...&nbsp;<b>WHERE</b>&nbsp;<b>Field2&nbsp;=&nbsp;'Value2'</b></span><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000"><span style="font-family:Helvetica,Arial; font-size:10pt; color:#010100">
&nbsp;<br>
</span></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:Helvetica,Arial; font-size:10pt; color:#010100"></span></td></tr></table><span style="font-family:Helvetica,Arial; font-size:10pt; color:#010100"></span><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000"><span style="font-family:Helvetica,Arial; font-size:10pt; color:#010100">