# Performance as a sql result cache

**URL:** <https://discuss.elastic.co/t/performance-as-a-sql-result-cache/18049>\
**Category:** Elasticsearch\
**Created:** [June 11, 2014, 3:22pm UTC](https://discuss.elastic.co/t/performance-as-a-sql-result-cache/18049 "2014-06-11T15:22:41Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Dean\_Pehrsson\_Chapma](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dean_pehrsson_chapma/32/1496_2.png) [@Dean\_Pehrsson\_Chapma](https://discuss.elastic.co/u/Dean_Pehrsson_Chapma)\
**Post date:** [June 11, 2014, 3:22pm UTC](https://discuss.elastic.co/t/performance-as-a-sql-result-cache/18049/1 "2014-06-11T15:22:41Z")

</div>

Hi,

I'm assessing ES as a tool to help with a specific problem. We have some  
expensive sql to run against a database that takes 300ms. there are 1000  
or so rows, so we need to page over these results. I though ES would be a  
perfect tool to do this, but the results I've seen, while being reasonable,  
are not compelling. I'm using the bulk update to insert each row as a doc:

```
        BulkRequestBuilder bulkRequest = c.prepareBulk();

        for (int i = 0; i < 1000; i++) {
            bulkRequest.add(c.prepareIndex("pages", "row")
                            .setSource(new 

```

JsonObject().putString("user1", "kimchy")  
.putString("user2", "kimchy")  
.putString("user3", "kimchy")  
.putString("user4", "kimchy")  
.putString("user5", "kimchy")  
.putString("user6", "kimchy")  
.putString("user7", "kimchy")  
.putString("user8", "kimchy")  
.putString("user9", "kimchy")  
.putNumber("rowcount", i)  
.putNumber("rs", rs)  
.encode())  
);  
}  
BulkResponse bulkResponse = bulkRequest.execute().actionGet();

The bulk execute takes somewhere around 300ms.

```
        QueryBuilder queryStringBuilder =
                QueryBuilders.queryString("rs:" + rs);

        SearchRequestBuilder requestBuilder =
                c.prepareSearch("pages")
                        .setTypes("row")
                        .setQuery(queryStringBuilder)
                        .addSort("rowcount", SortOrder.DESC)
                .setSize(10).setFrom(100);

        SearchResponse response = requestBuilder.execute().actionGet();

```

The search execute takes between 50 and 60ms.

Am I doing it wrong, or does this seem like reasonable performance? I'm  
using an intel mac pro with SSD.

Cheers!

--  
You received this message because you are subscribed to the Google Groups "elasticsearch" group.  
To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com).  
To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/10fbfe7e-b12c-49b3-94b4-4ccea13ebd8a%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/10fbfe7e-b12c-49b3-94b4-4ccea13ebd8a%40googlegroups.com).  
For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).

---

<div class="post-metadata">

**Author:** ![jprante](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jprante/32/44941_2.png) [@jprante](https://discuss.elastic.co/u/jprante)\
**Post date:** [June 11, 2014, 3:46pm UTC](https://discuss.elastic.co/t/performance-as-a-sql-result-cache/18049/2 "2014-06-11T15:46:12Z")

</div>

You should run your search query more than just once. The first time  
executed, ES will load the Lucene index fields, and ramp up internal  
resources, which adds some overhead. Subsequent queries will be faster  
(around 1ms on my MacBook Pro with SSD but SSD is not important, it is the  
filesystem cache)

Jörg

On Wed, Jun 11, 2014 at 5:22 PM, Dean Pehrsson-Chapman \<  
[damnpanache@gmail.com](mailto:damnpanache@gmail.com)\> wrote:

> Hi,
> 
> I'm assessing ES as a tool to help with a specific problem. We have some  
> expensive sql to run against a database that takes 300ms. there are 1000  
> or so rows, so we need to page over these results. I though ES would be a  
> perfect tool to do this, but the results I've seen, while being reasonable,  
> are not compelling. I'm using the bulk update to insert each row as a doc:
> 
> ```
> BulkRequestBuilder bulkRequest = c.prepareBulk();
> 
> for (int i = 0; i < 1000; i++) {
> bulkRequest.add(c.prepareIndex("pages", "row")
> .setSource(new
> 
> ```
> 
> JsonObject().putString("user1", "kimchy")  
> .putString("user2", "kimchy")  
> .putString("user3", "kimchy")  
> .putString("user4", "kimchy")  
> .putString("user5", "kimchy")  
> .putString("user6", "kimchy")  
> .putString("user7", "kimchy")  
> .putString("user8", "kimchy")  
> .putString("user9", "kimchy")  
> .putNumber("rowcount", i)  
> .putNumber("rs", rs)  
> .encode())  
> );  
> }  
> BulkResponse bulkResponse = bulkRequest.execute().actionGet();
> 
> The bulk execute takes somewhere around 300ms.
> 
> ```
> QueryBuilder queryStringBuilder =
> QueryBuilders.queryString("rs:" + rs);
> 
> SearchRequestBuilder requestBuilder =
> c.prepareSearch("pages")
> .setTypes("row")
> .setQuery(queryStringBuilder)
> .addSort("rowcount", SortOrder.DESC)
> .setSize(10).setFrom(100);
> 
> SearchResponse response = requestBuilder.execute().actionGet();
> 
> ```
> 
> The search execute takes between 50 and 60ms.
> 
> Am I doing it wrong, or does this seem like reasonable performance? I'm  
> using an intel mac pro with SSD.
> 
> Cheers!
> 
> --  
> You received this message because you are subscribed to the Google Groups  
> "elasticsearch" group.  
> To unsubscribe from this group and stop receiving emails from it, send an  
> email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com).  
> To view this discussion on the web visit  
> [https://groups.google.com/d/msgid/elasticsearch/10fbfe7e-b12c-49b3-94b4-4ccea13ebd8a%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/10fbfe7e-b12c-49b3-94b4-4ccea13ebd8a%40googlegroups.com)  
> [https://groups.google.com/d/msgid/elasticsearch/10fbfe7e-b12c-49b3-94b4-4ccea13ebd8a%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/10fbfe7e-b12c-49b3-94b4-4ccea13ebd8a%40googlegroups.com?utm_medium=email&utm_source=footer)  
> .  
> For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).

--  
You received this message because you are subscribed to the Google Groups "elasticsearch" group.  
To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com).  
To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/CAKdsXoFgZBp6cR%2B5UTNKmEPD%3Ds6q7SVXe5taFzeT9\_N7XL4P8w%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CAKdsXoFgZBp6cR%2B5UTNKmEPD%3Ds6q7SVXe5taFzeT9_N7XL4P8w%40mail.gmail.com).  
For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).

---

<div class="post-metadata">

**Author:** ![Dean\_Pehrsson\_Chapma](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dean_pehrsson_chapma/32/1496_2.png) [@Dean\_Pehrsson\_Chapma](https://discuss.elastic.co/u/Dean_Pehrsson_Chapma)\
**Post date:** [June 11, 2014, 4:30pm UTC](https://discuss.elastic.co/t/performance-as-a-sql-result-cache/18049/3 "2014-06-11T16:30:11Z")

</div>

> I was - but only about 20 times. I'll go back and have another look.  
> Thanks.

--  
You received this message because you are subscribed to the Google Groups "elasticsearch" group.  
To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com).  
To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/028c5be9-5561-4c20-8208-4b4bedbd912a%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/028c5be9-5561-4c20-8208-4b4bedbd912a%40googlegroups.com).  
For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).

---

<div class="post-metadata">

**Author:** ![Dean\_Pehrsson\_Chapma](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dean_pehrsson_chapma/32/1496_2.png) [@Dean\_Pehrsson\_Chapma](https://discuss.elastic.co/u/Dean_Pehrsson_Chapma)\
**Post date:** [June 11, 2014, 4:34pm UTC](https://discuss.elastic.co/t/performance-as-a-sql-result-cache/18049/4 "2014-06-11T16:34:08Z")

</div>

ISWYM - down to 6ms now. Thanks again.

On Wednesday, 11 June 2014 17:30:11 UTC+1, Dean Pehrsson-Chapman wrote:

> I was - but only about 20 times. I'll go back and have another look.
> 
> > Thanks.

--  
You received this message because you are subscribed to the Google Groups "elasticsearch" group.  
To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com).  
To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/ffb9c72a-6be7-4d71-9cfc-56e302305be7%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/ffb9c72a-6be7-4d71-9cfc-56e302305be7%40googlegroups.com).  
For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).

---

<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 6, 2017, 1:23am UTC](https://discuss.elastic.co/t/performance-as-a-sql-result-cache/18049/5 "2017-07-06T01:23:13Z")

</div>