# Migrate bulk lookup from Oracle - performance problem

**URL:** <https://discuss.elastic.co/t/migrate-bulk-lookup-from-oracle-performance-problem/46261>\
**Category:** Elasticsearch\
**Created:** [April 4, 2016, 4:14pm UTC](https://discuss.elastic.co/t/migrate-bulk-lookup-from-oracle-performance-problem/46261 "2016-04-04T16:14:04Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![rasmushalland](https://avatars.discourse-cdn.com/v4/letter/r/bb73d2/32.png) [@rasmushalland](https://discuss.elastic.co/u/rasmushalland)\
**Post date:** [April 4, 2016, 4:14pm UTC](https://discuss.elastic.co/t/migrate-bulk-lookup-from-oracle-performance-problem/46261/1 "2016-04-04T16:14:04Z")

</div>

Hi,

I'm trying to migrate a text search tool from oracle to elasticsearch. It's working, but unfortunately searching is a lot slower than the oracle version, and I'm hoping for clues as to what can be done about it.

We're indexing millions of text documents, but rather than indexing the text directly, we index a number of fingerprints for each document. Each fingerprint is an integer of appr. 50 bits, and each document produces in the order of hundreds of fingerprints (thousands, occasionally).

The query part is to find documents that are similar to an input document. Two documents are similar if there is any overlap in their fingerprints. So the query is a terms query with the terms from the input document.  
This query is the slow part.

The problem is mainly CPU usage. When oracle and ES/windows has the data in the cache, a single query takes oracle 5 to 25 ms, while it takes ES 200 to 350 ms.

Suggestions as to how to make it faster will be much appreciated.

Setup:  
Windows server 2012r2.  
Single node ES cluster  
Elasticsearch 2.2.0  
ES memory pool: 10GB  
Client: NEST 1 / .NET  
CPU: E5-2620 (same for oracle and ES)

Sample query:

```auto
POST /myindex-v8/document/_search
{ "size": 5000,
"fields": ["properties"],
"filter": { "terms": { "fingerprints": [ 458555129998123,
426113387683010,
1047636941817882,
339603061195725,
496322075763366,
898270748861439,
715145004425481,
1083745791071972,
364744235080270,

[quite a few more of these]

1042455538904631,
582951484394277,
264684524292891,
827184922645852 ] } } }

```

Mapping:

```auto
{
  "myindex-v8": {
    "mappings": {
      "document": {
        "_all": {
          "enabled": false
        },
        "properties": {
          "fingerprints": {
            "type": "double",
            "precision_step": 2147483647
          },
          "properties": {
            "type": "string",
            "index": "no"
          },
          "refKey": {
            "type": "string",
            "index": "not_analyzed"
          }
        }
      }
    }
  }
}

```

Mapping notes:  
The "precision\_step" setting in the mapping provided a substantial indexing performance boost.

Query notes:  
Most of these queries return 1 to 20 results.

---

<div class="post-metadata">

**Author:** ![abeyad](https://avatars.discourse-cdn.com/v4/letter/a/278dde/32.png) [@abeyad](https://discuss.elastic.co/u/abeyad)\
**Post date:** [April 4, 2016, 6:49pm UTC](https://discuss.elastic.co/t/migrate-bulk-lookup-from-oracle-performance-problem/46261/2 "2016-04-04T18:49:48Z")

</div>

One thing to try is using a `constant_score` query and move your term filter inside of it, as so: [https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-constant-score-query.html](https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-constant-score-query.html)

In general, filtering against several thousands of terms is likely not going to be so performant. it may be worth re-thinking what you are trying to accomplish and perhaps there is a better way to go about it.

Also, we would recommend not having a `double` type for your fingerprints when the values are `integers`. Using an `integer` instead will greatly speed up indexing time and reduce the amount of disk space needed for `doc-values`, because doubles don't get the same compression benefits that integers have. We would also recommend not using `fields` but rather use `_source` filtering: [https://www.elastic.co/guide/en/elasticsearch/reference/current/search-request-source-filtering.html](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-request-source-filtering.html)

---

<div class="post-metadata">

**Author:** ![rasmushalland](https://avatars.discourse-cdn.com/v4/letter/r/bb73d2/32.png) [@rasmushalland](https://discuss.elastic.co/u/rasmushalland)\
**Post date:** [April 5, 2016, 8:03pm UTC](https://discuss.elastic.co/t/migrate-bulk-lookup-from-oracle-performance-problem/46261/3 "2016-04-05T20:03:47Z")

</div>

Thank you for your suggestions.

Using `constant_score` makes the queries go twice as fast, so that's a nice start 🙂.

I'm building a new index with `long` fingerprints, we'll see what effect that will have.

Using `_source` filtering does not seem to make a difference.

---

<div class="post-metadata">

**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\
**Post date:** [April 6, 2016, 5:29am UTC](https://discuss.elastic.co/t/migrate-bulk-lookup-from-oracle-performance-problem/46261/4 "2016-04-06T05:29:47Z")

</div>

It sounds to me like the current approach was designed in order to get around the fact that a relational database does not do full text search very well. If that is the case I do not understand the rationale of trying to move this approach as it is directly to Elasticsearch, as it goes against the way Elasticsearch typically handles these kind of problems.

As Elasticsearch is a search engine, it has a lot of tools to allow flexible search, e.g. [more like this queries](https://www.elastic.co/guide/en/elasticsearch/reference/2.3/query-dsl-mlt-query.html), which allows you to find documents that are similar to a specific set of documents. It does this by indexing and analysing the text of the documents.

In order to migrate this successfully to Elasticsearch I would recommend taking a step back and looking at the requirements and see how you can use Elasticsearch rather rich feature set to achieve this the Elasticsearch way.

---

<div class="post-metadata">

**Author:** ![nik9000](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/nik9000/32/44947_2.png) [@nik9000](https://discuss.elastic.co/u/nik9000)\
**Post date:** [April 6, 2016, 2:51pm UTC](https://discuss.elastic.co/t/migrate-bulk-lookup-from-oracle-performance-problem/46261/5 "2016-04-06T14:51:52Z")

</div>

> [@rasmushalland](#):
>
> The "precision\_step" setting in the mapping provided a substantial indexing performance boost.

This makes me think that these are not "doubles" at all, but really terms. You'll likely get much better performance by making them not\_analyzed strings. They'll take up more space I expect, unless you encode them nicely. Numerics aren't a fast as exact term matches, IIRC.

I agree with @Christian_Dahlqvist that it looks like you are trying to shoe horn a search solution designed for another system into Elasticsearch. You are probably better off designing one for Elasticsearch. But if you can't because you want to limit the scope of your project and you really just want to not have Oracle licenses any more then I understand wanting to continue down the route you are using. But just remember "we told you this is funky" for when things don't work super well.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [July 5, 2017, 11:01pm UTC](https://discuss.elastic.co/t/migrate-bulk-lookup-from-oracle-performance-problem/46261/6 "2017-07-05T23:01:49Z")

</div>