# Elasticsearch 7.0 SQL ACCESS request cache **URL:** \ **Category:** Elasticsearch\ **Created:** [June 19, 2019, 12:38pm UTC](https://discuss.elastic.co/t/elasticsearch-7-0-sql-access-request-cache/186467 "2019-06-19T12:38:44Z")\ **Posts on this page:** 13\ **Page:** 1
**Author:** ![c81b4c93b1f70af5398f](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/c81b4c93b1f70af5398f/32/45070_2.png) [@c81b4c93b1f70af5398f](https://discuss.elastic.co/u/c81b4c93b1f70af5398f)\ **Post date:** [June 19, 2019, 12:38pm UTC](https://discuss.elastic.co/t/elasticsearch-7-0-sql-access-request-cache/186467/1 "2019-06-19T12:38:44Z")
Looks like SQL query is not cached, and equivalent dsl qeury is cached. Every single time this SQL query is executed, it takes over 3 seconds. (The number of documents is about 6,000,000) ```auto POST /_sql?format=json { "query": "SELECT HISTOGRAM(\"batchDate\", INTERVAL 1 DAY) AS \"h_date\", SUM(\"aggrCount\") AS \"cnt\" FROM \"my.index-*\" WHERE \"batchDate\" < 1560988799999 AND \"batchDate\" >= 1560340034000 GROUP BY \"h_date\"" } ``` And when I'm using QueryDSL using request body got from /\_sql/translate API with the sql query above, it took over 3~5 seconds only first time, and next it took under 1ms. Maybe it might be cached. ```auto POST /my.index-*/_search { "size" : 0, "query" : { "range" : { "batchDate" : { "from" : 1560340034000, "to" : 1560988799999, "include_lower" : true, "include_upper" : false, "boost" : 1.0 } } }, "_source" : false, "stored_fields" : "_none_", "aggregations" : { "groupby" : { "composite" : { "size" : 1000, "sources" : [ { "25579" : { "date_histogram" : { "field" : "batchDate", "missing_bucket" : true, "value_type" : "date", "order" : "asc", "interval" : 86400000, "time_zone" : "Z" } } } ] }, "aggregations" : { "25580" : { "sum" : { "field" : "aggrCount" } } } } } } ``` ---
**Author:** ![Andrei\_Stefan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/andrei_stefan/32/47533_2.png) [@Andrei\_Stefan](https://discuss.elastic.co/u/Andrei_Stefan)\ **Post date:** [June 24, 2019, 9:18am UTC](https://discuss.elastic.co/t/elasticsearch-7-0-sql-access-request-cache/186467/2 "2019-06-24T09:18:32Z")
@c81b4c93b1f70af5398f the json we generate as a query can be different every time you call `translate`. The relevant parts of the query will not be different, obviously, but the name of the aggregations, for example, can be different - `25579` and `25580` from your example. And I think caching works on the body of the query (the json itself). If you generate the query once, run it several times, and then generate it **again** with `translate` and run it, does it take seconds or returns instantly? ---
**Author:** ![nuwan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/nuwan/32/42325_2.png) [@nuwan](https://discuss.elastic.co/u/nuwan)\ **Post date:** [June 24, 2019, 11:18am UTC](https://discuss.elastic.co/t/elasticsearch-7-0-sql-access-request-cache/186467/3 "2019-06-24T11:18:38Z")
Hi Have you setup elasticsearch cluster with V 7 at least 3 node cluster ? Nuwan ---
**Author:** ![c81b4c93b1f70af5398f](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/c81b4c93b1f70af5398f/32/45070_2.png) [@c81b4c93b1f70af5398f](https://discuss.elastic.co/u/c81b4c93b1f70af5398f)\ **Post date:** [June 24, 2019, 11:26am UTC](https://discuss.elastic.co/t/elasticsearch-7-0-sql-access-request-cache/186467/4 "2019-06-24T11:26:58Z")
The name of aggregations depends like you mentioned in translate API. I repeated the query dsl request several times after generating translated json body(dsl) once, and it got much faster after several times. But it gets fast only with translated dsl that has same name of aggregations. If I generate new translated json body(with another aggregation name), it takes long again. When it comes with SQL, It takes over 3 seconds even after several times. Can I fix the name of aggregations when requesting by SQL? ---
**Author:** ![c81b4c93b1f70af5398f](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/c81b4c93b1f70af5398f/32/45070_2.png) [@c81b4c93b1f70af5398f](https://discuss.elastic.co/u/c81b4c93b1f70af5398f)\ **Post date:** [June 24, 2019, 11:28am UTC](https://discuss.elastic.co/t/elasticsearch-7-0-sql-access-request-cache/186467/5 "2019-06-24T11:28:19Z")
Hi nuwan. My cluster has 9 nodes and every node is V 7.0.0 ---
**Author:** ![nuwan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/nuwan/32/42325_2.png) [@nuwan](https://discuss.elastic.co/u/nuwan)\ **Post date:** [June 24, 2019, 11:47am UTC](https://discuss.elastic.co/t/elasticsearch-7-0-sql-access-request-cache/186467/6 "2019-06-24T11:47:40Z")
Hi Thank you very much for quick respond my configuration is look like below . appciate if you can help me to setup 3 node cluster . I'm going go update my 3 node production cluster from v 6.5.1 to v 7.1.1 [root@orc-app1 ~]# cat /etc/elasticsearch/elasticsearch.yml cluster.name: ElasticDemO node.name: ${HOSTNAME} network.host: 192.168.60.4 xpack.security.enabled: false bootstrap.system\_call\_filter: true path.data: /var/lib/elasticsearch path.logs: /var/log/elasticsearch discovery.seed\_hosts: - 192.168.60.4:9300 - 192.168.60.5:9300 - 192.168.60.6:9300 cluster.initial\_master\_nodes: ["192.168.60.4","192.168.60.5","192.168.60.6"] http.cors.enabled: true discovery.zen.minimum\_master\_nodes: 2 node.master: true [root@orc-app1 ~]# [http://192.168.60.4:9200/\_cat/nodes?v](http://192.168.60.4:9200/_cat/nodes?v) ip heap.percent ram.percent cpu load\_1m load\_5m load\_15m node.role master name 192.168.60.4 25 94 5 0.00 0.02 0.05 mdi \* orc-app1.dev ---
**Author:** ![nuwan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/nuwan/32/42325_2.png) [@nuwan](https://discuss.elastic.co/u/nuwan)\ **Post date:** [June 24, 2019, 11:49am UTC](https://discuss.elastic.co/t/elasticsearch-7-0-sql-access-request-cache/186467/7 "2019-06-24T11:49:04Z")
appreciate if you can send me sample configuration file or if you can go through my configuation file and let me know what need to be modified ---
**Author:** ![Andrei\_Stefan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/andrei_stefan/32/47533_2.png) [@Andrei\_Stefan](https://discuss.elastic.co/u/Andrei_Stefan)\ **Post date:** [June 24, 2019, 12:06pm UTC](https://discuss.elastic.co/t/elasticsearch-7-0-sql-access-request-cache/186467/8 "2019-06-24T12:06:15Z")
@nuwan please, open a new thread of discussion and keep this discussion focused on the initial issue. Thank you. ---
**Author:** ![nuwan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/nuwan/32/42325_2.png) [@nuwan](https://discuss.elastic.co/u/nuwan)\ **Post date:** [June 24, 2019, 12:14pm UTC](https://discuss.elastic.co/t/elasticsearch-7-0-sql-access-request-cache/186467/9 "2019-06-24T12:14:12Z")
yes I did still I'm unable to get the answer for that appreciate if any one help me regarding my concerns [Any one setup 3 node cluster with elasticsearch v 7](https://discuss.elastic.co/t/any-one-setup-3-node-cluster-with-elasticsearch-v-7/187109) Thank you ---
**Author:** ![nuwan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/nuwan/32/42325_2.png) [@nuwan](https://discuss.elastic.co/u/nuwan)\ **Post date:** [June 24, 2019, 12:15pm UTC](https://discuss.elastic.co/t/elasticsearch-7-0-sql-access-request-cache/186467/10 "2019-06-24T12:15:16Z")
> [@03 node ELK cluster setup with v 7.1.x](https://discuss.elastic.co/t/03-node-elk-cluster-setup-with-v-7-1-x/186623): > > Hi Team, When I create 3 node cluster what would be the elaticsearch.yml and Kibana.yml configuration look like? My current configuration Node Host IP Node 1 app1 192.168.60.4 Node 2 app2 192.168.60.5 Node 3 DB 192.168.60.6 [root@orc-app1 ~]# cat /etc/elasticsearch/elasticsearch.yml cluster.name: ElasticDemO node.name: {HOSTNAME} network.host: 192.168.60.4 discovery.zen.minimum\_master\_nodes: 1 xpack.security.enabled: false bootstrap.system\_call\_filter: true path.data: /var/li… ---
**Author:** ![Andrei\_Stefan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/andrei_stefan/32/47533_2.png) [@Andrei\_Stefan](https://discuss.elastic.co/u/Andrei_Stefan)\ **Post date:** [June 24, 2019, 12:29pm UTC](https://discuss.elastic.co/t/elasticsearch-7-0-sql-access-request-cache/186467/11 "2019-06-24T12:29:24Z")
@c81b4c93b1f70af5398f no, you cannot control the name of the generated aggregation in a query, everything is transparent and ES SQL should behave just like any other SQL facing tool. I've created a github issue and we'll discuss internally if this is feasible to implement: [https://github.com/elastic/elasticsearch/issues/43531](https://github.com/elastic/elasticsearch/issues/43531) ---
**Author:** ![c81b4c93b1f70af5398f](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/c81b4c93b1f70af5398f/32/45070_2.png) [@c81b4c93b1f70af5398f](https://discuss.elastic.co/u/c81b4c93b1f70af5398f)\ **Post date:** [June 26, 2019, 8:31am UTC](https://discuss.elastic.co/t/elasticsearch-7-0-sql-access-request-cache/186467/12 "2019-06-26T08:31:31Z")
Thanks for your follow up. Hope to be fixed soon. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [July 24, 2019, 8:31am UTC](https://discuss.elastic.co/t/elasticsearch-7-0-sql-access-request-cache/186467/13 "2019-07-24T08:31:32Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.