Performance as a sql result cache


(Dean Pehrsson-Chapman) #1

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.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/10fbfe7e-b12c-49b3-94b4-4ccea13ebd8a%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.


(Jörg Prante) #2

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> 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.
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?utm_medium=email&utm_source=footer
.
For more options, visit 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.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/CAKdsXoFgZBp6cR%2B5UTNKmEPD%3Ds6q7SVXe5taFzeT9_N7XL4P8w%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.


(Dean Pehrsson-Chapman) #3

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.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/028c5be9-5561-4c20-8208-4b4bedbd912a%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.


(Dean Pehrsson-Chapman) #4

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.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/ffb9c72a-6be7-4d71-9cfc-56e302305be7%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.


(system) #5