📄 speedupsearchesandfilters.htm
字号:
<html>
<head>
<title>Speeding up Searches and Filters</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>Speeding up Searches and Filters
<br>
</b></span>
</td>
<td align="right">
<font face="Arial" size="2">
<a href="improvingoverallperformance.htm">Previous</a>
<a href="overview.htm">Top</a>
<a href="increasesqlperformance.htm">Next</a>
</font>
</td>
</tr>
</table>
<br><br>
<span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000">
<br>
Absolute Database search and filter optimizations are based on the use of available indexes.
<br>
So, to speed up searching or filtering, you should create an index which corresponds to the search or filter condition.
<br>
<br>
Absolute Database Engine can use both ascending and descending indexes for optimization.
<br>
<br>
If multiple indexes could be used to optimize the search, the Absolute Database Engine estimates the time cost of using each available index and chooses the fastest way.
<br>
<br>
Always create unique (primary) indexes when possible, as the unique index allow much better optimization then non-unique index.
<br>
<br>
To speed up a <b>single-field case-sensitive search</b>, such as
<br>
<span style="font-family:Helvetica,Arial; font-size:4pt; color:#000000">
<br>
</span><span style="font-family:Courier New; font-size:8pt; color:#000000"> ABSTable1.Locate(<span style="font-family:Courier New; font-size:8pt; color:#000080">'Company'</span></span><span style="font-family:Courier New; font-size:8pt; color:#000000">, <span style="font-family:Courier New; font-size:8pt; color:#000080">'Professional Divers, Ltd.'</span></span><span style="font-family:Courier New; font-size:8pt; color:#000000">, []);
<br>
</span><span style="font-family:Helvetica,Arial; font-size:4pt; color:#000000">
<br>
</span><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000">you should create a case-sensitive index on the search field:
<br>
</span><span style="font-family:Helvetica,Arial; font-size:4pt; color:#000000">
<br>
</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">'idxCompany'</span></span><span style="font-family:Courier New; font-size:8pt; color:#000000">, <span style="font-family:Courier New; font-size:8pt; color:#000080">'Company'</span></span><span style="font-family:Courier New; font-size:8pt; color:#000000">, []);</span><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000">
<br>
<br>
To improve peformance of a <b>single-field case-insensitive filter</b>, such as
<br>
</span><span style="font-family:Helvetica,Arial; font-size:4pt; color:#000000">
<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:#000000"></span></span><span style="font-family:Courier New; font-size:8pt; color:#000000">FilterOptions := [foCaseInsensitive];
<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">Filter := <span style="font-family:Courier New; font-size:8pt; color:#000080">'State = '</span></span><span style="font-family:Courier New; font-size:8pt; color:#000000"> + QuotedStr(<span style="font-family:Courier New; font-size:8pt; color:#000080">'CA'</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"></span></td></tr></table><span style="font-family:Courier New; font-size:8pt; color:#000000"></span><span style="font-family:Helvetica,Arial; font-size:4pt; color:#000000">
<br>
</span><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000">you should create a case-insensitive index on the search field:
<br>
</span><span style="font-family:Helvetica,Arial; font-size:4pt; color:#000000">
<br>
</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">'idxState_CaseIns'</span></span><span style="font-family:Courier New; font-size:8pt; color:#000000">, <span style="font-family:Courier New; font-size:8pt; color:#000080">'State'</span></span><span style="font-family:Courier New; font-size:8pt; color:#000000">, [ixCaseInsensitive]);
<br>
</span><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000">
<br>
If you have <b>multiple search (filter) conditions joined by an AND</b> operator, such as
<br>
</span><span style="font-family:Helvetica,Arial; font-size:4pt; color:#000000">
<br>
</span><span style="font-family:Courier New; font-size:8pt; color:#000000"> (FirstName = <span style="font-family:Courier New; font-size:8pt; color:#000080">'Mark'</span></span><span style="font-family:Courier New; font-size:8pt; color:#000000">) <b>AND</b> (LastName = <span style="font-family:Courier New; font-size:8pt; color:#000080">'Brown'</span></span><span style="font-family:Courier New; font-size:8pt; color:#000000">)
<br>
</span><span style="font-family:Helvetica,Arial; font-size:4pt; color:#000000"> </span><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000">
<br>
Absolute DB will use index on <i>FirstName</i> field or on <i>LastName</i> field for optimization if possible, but the best way to optimize this search is to create a compound index on both fields used in joined conditions:
<br>
</span><span style="font-family:Helvetica,Arial; font-size:4pt; color:#000000">
<br>
</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">'idxFirstNameLastName'</span></span><span style="font-family:Courier New; font-size:8pt; color:#000000">, <span style="font-family:Courier New; font-size:8pt; color:#000080">'FirstName;LastName'</span></span><span style="font-family:Courier New; font-size:8pt; color:#000000">, []);
<br>
</span><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000">
<br>
<b>Note:
<br>
</b>To improve peformance of a <b>LIKE condition</b>, you can create an appropriate index, but note that this index can improve performance
<br>
for patterns like 'str%' only:
<br>
<table width="100%" border="0" cellpadding="0" cellspacing="0"><tr valign="top"><td width="14"></td><td width="14"><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000"><font face="Symbol" size="2" color="#000000">·</font></span></span></td><td><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000">an index speeds up </span><span style="font-family:Courier New; font-size:8pt; color:#000000">(<b>Name</b> LIKE <span style="font-family:Courier New; font-size:8pt; color:#000080">'Jo%'</span></span><span style="font-family:Courier New; font-size:8pt; color:#000000">)</span><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000">
<br>
</span></td></tr></table><table width="100%" border="0" cellpadding="0" cellspacing="0"><tr valign="top"><td width="14"></td><td width="14"><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000"><font face="Symbol" size="2" color="#000000">·</font></span></td><td><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000">but an index doesn't speed up </span><span style="font-family:Courier New; font-size:8pt; color:#000000">(<b>Name</b> LIKE <span style="font-family:Courier New; font-size:8pt; color:#000080">'%Jo%'</span></span><span style="font-family:Courier New; font-size:8pt; color:#000000">) or (<b>Name</b> LIKE <span style="font-family:Courier New; font-size:8pt; color:#000080">'_all'</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 width="14"><span style="font-family:Courier New; font-size:8pt; color:#000000"></span></td><td></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">
<br>
<br>
Please note that indexes will not speed up the following search conditions:
<br>
<br>
<table width="100%" border="0" cellpadding="0" cellspacing="0"><tr valign="top"><td width="14"></td><td width="14"><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000"><font face="Symbol" size="2" color="#000000">·</font></span></span></td><td><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000">Conditions joined by the OR operator: </span><span style="font-family:Courier New; font-size:8pt; color:#000000">(State = <span style="font-family:Courier New; font-size:8pt; color:#000080">'CA'</span></span><span style="font-family:Courier New; font-size:8pt; color:#000000">) <b>OR</b> (State = <span style="font-family:Courier New; font-size:8pt; color:#000080">'NY'</span></span><span style="font-family:Courier New; font-size:8pt; color:#000000">)</span><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000">
<br>
</span></td></tr></table><table width="100%" border="0" cellpadding="0" cellspacing="0"><tr valign="top"><td width="14"></td><td width="14"><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000"><font face="Symbol" size="2" color="#000000">·</font></span></td><td><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000">Not Equal operator: </span><span style="font-family:Courier New; font-size:8pt; color:#000000">(State <> <span style="font-family:Courier New; font-size:8pt; color:#000080">'CA'</span></span><span style="font-family:Courier New; font-size:8pt; color:#000000">)</span><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000">
<br>
</span></td></tr></table><table width="100%" border="0" cellpadding="0" cellspacing="0"><tr valign="top"><td width="14"></td><td width="14"><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000"><font face="Symbol" size="2" color="#000000">·</font></span></td><td><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000">IN: </span><span style="font-family:Courier New; font-size:8pt; color:#000000">(Color <b>IN</b> ('red','blue'))</span><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000">
<br>
</span></td></tr></table><table width="100%" border="0" cellpadding="0" cellspacing="0"><tr valign="top"><td width="14"></td><td width="14"><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000"><font face="Symbol" size="2" color="#000000">·</font></span></td><td><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000">Condition with expression: </span><span style="font-family:Courier New; font-size:8pt; color:#000000">(FirstName+LastName = <span style="font-family:Courier New; font-size:8pt; color:#000080">'Mark Brown'</span></span><span style="font-family:Courier New; font-size:8pt; color:#000000">)</span><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000">
<br>
</span></td></tr></table><table width="100%" border="0" cellpadding="0" cellspacing="0"><tr valign="top"><td width="14"></td><td width="14"><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000"></span></td><td></td></tr></table><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000">
<br>
<b>Note:</b> </span><span style="font-family:Times New Roman; font-size:12pt; color:#000000">Each additional index causes slowing down insert / update / delete record operations.
<br>
</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 + -