<p>I have a query that originally looks like this:</p>

<pre><code>select c.Id, c.Name, c.CountryCode, c.CustomerNumber, cacc.AccountNumber, ca.Line1, ca.CityName, ca.PostalCode
from dbo.Customer as c
left join dbo.CustomerAddress as ca on ca.CustomerId = c.Id
left join dbo.CustomerAccount as cacc on cacc.CustomerId = c.Id
where  c.CountryCode = 'XX' and (cacc.AccountNumber like '%C17%' or c.Name like '%op%'       
or ca.Line1 like '%ae%' or ca.CityName like '%ab%' or ca.PostalCode like '%10%')
</code></pre>

<p>On a database with 90,000 records this query takes around 7 seconds to execute (obviously all the joins and likes are taxing). </p>

<p>I have been trying to find a way to bring the query execution time down with full-text search on the columns concerned. However, I haven't seen an example of a full-text search that has three table joins like this, especially since my join condition is not part of the search term.</p>

<p>Is there a way to do this in full-text search?</p>

<hr>

<p>@David</p>

<p>Yep, there are indexes on the Ids.</p>

<p>I've tried adding indexes on the CustomerAddress stuff (CityName, PostalCode, etc.) and it brought down the query to 3 seconds, but I still find that too slow for something like this.</p>

<p>Note that all of the text fields (with the exception of the ids) are nvarchars, and Line1 is an nvarchar 1000, so that might affect the speed, but still.</p>

[{'answer_id': 35955, 'author': 'wesc', 'author_id': 3738, 'author_profile': 'https://Stackoverflow.com/users/3738', 'pm_score': 0, 'selected': False, 'text': "<p>I <em>think</em> that an unordered_map and hash_map are more or less the same thing. The difference is that the STL doesn't officially have a hash_map (what you're using is probably a compiler specific thing), so unordered_map is the fix for that omission.</p>\n\n<p>unordered_map is just that... unordered. You can't depend on it preserving any ordering on iteration.</p>\n"}, {'answer_id': 35958, 'author': 'martjno', 'author_id': 3373, 'author_profile': 'https://Stackoverflow.com/users/3373', 'pm_score': -1, 'selected': False, 'text': "<p>@wesc: STL has std::map... so what's the difference with unordered_map? I don't think STL would implement twice the same thing and call it differently.</p>\n"}, {'answer_id': 35965, 'author': 'Christopher', 'author_id': 3186, 'author_profile': 'https://Stackoverflow.com/users/3186', 'pm_score': 3, 'selected': True, 'text': '<p><a href="http://www.boost.org/doc/libs/1_36_0/doc/html/unordered.html" rel="nofollow noreferrer">Boost documentation of unordered containers</a></p>\n\n<p>The difference is in the method of how you generate the look up.</p>\n\n<p>In the map/set containers the <code>operator&lt;</code> is used to generate an ordered tree.</p>\n\n<p>In the unordered containers, an <code>operator( key ) =&gt; index</code> is used.</p>\n\n<p>See hashing for a description of how that works.</p>\n'}, {'answer_id': 35966, 'author': 'wesc', 'author_id': 3738, 'author_profile': 'https://Stackoverflow.com/users/3738', 'pm_score': 0, 'selected': False, 'text': "<p>You sure that std::hash_map exists in <em>all</em> STL implementations? SGI STL implements it, however GNU g++ doesn't have it (it's located in the __gnu_cxx namespace) as of 4.3.1 anyway. As far as I know, hash_map has always been non-standard, and now tr1 is fixing that.</p>\n"}, {'answer_id': 35982, 'author': 'wesc', 'author_id': 3738, 'author_profile': 'https://Stackoverflow.com/users/3738', 'pm_score': 2, 'selected': False, 'text': "<p>Sorry, read your last comment wrong. Yes, hash_map is not in STL, map is. But unordered_map and hash_map are the same from what I've been reading.</p>\n\n<p>map -> log (n) insertion, retrieval, iteration is efficient (and ordered by key comparison)</p>\n\n<p>hash_map/unordered_map -> constant time insertion and retrieval, iteration time is not guarantee to be efficient</p>\n\n<p>Neither of these will work for you by themselves, since the map orders things based on the key content, and not the insertion sequence (unless your key contains info about the insertion sequence in it).</p>\n\n<p>You'll have to do either what you described (list + hash_map), or create a key type that has the insertion sequence number plus an appropriate comparison function.</p>\n"}, {'answer_id': 36000, 'author': 'Adam Mitz', 'author_id': 2574, 'author_profile': 'https://Stackoverflow.com/users/2574', 'pm_score': 3, 'selected': False, 'text': '<p>You need to index an associative container two ways:</p>\n\n<ul>\n<li>Insertion order</li>\n<li>String comparison</li>\n</ul>\n\n<p>Try <a href="http://www.boost.org/doc/libs/1_36_0/libs/multi_index/doc/index.html" rel="nofollow noreferrer">Boost.MultiIndex</a> or <a href="http://www.boost.org/doc/libs/1_50_0/doc/html/intrusive.html" rel="nofollow noreferrer">Boost.Intrusive</a>.  I haven\'t used it this way but I think it\'s possible.</p>\n'}, {'answer_id': 36021, 'author': 'user3755', 'author_id': 3755, 'author_profile': 'https://Stackoverflow.com/users/3755', 'pm_score': 4, 'selected': False, 'text': '<p>You\'ve asked for the canonical reason why Boost::MultiIndex was made:  list insertion order with fast lookup by key.  <a href="http://www.boost.org/doc/libs/1_36_0/libs/multi_index/doc/tutorial/basics.html#list_fast_lookup" rel="nofollow noreferrer">Boost MultiIndex tutorial: list fast lookup</a></p>\n'}]
['https://Stackoverflow.com/questions/35954', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/372/']