# Logstash Performance comparison \_update\_by\_query v/s pulling data from DB via SQL

**URL:** <https://discuss.elastic.co/t/logstash-performance-comparison-update-by-query-v-s-pulling-data-from-db-via-sql/225308>\
**Category:** Logstash\
**Created:** [March 26, 2020, 11:13pm UTC](https://discuss.elastic.co/t/logstash-performance-comparison-update-by-query-v-s-pulling-data-from-db-via-sql/225308 "2020-03-26T23:13:30Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![MChat](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mchat/32/46467_2.png) [@MChat](https://discuss.elastic.co/u/MChat)\
**Post date:** [March 26, 2020, 11:13pm UTC](https://discuss.elastic.co/t/logstash-performance-comparison-update-by-query-v-s-pulling-data-from-db-via-sql/225308/1 "2020-03-26T23:13:30Z")

</div>

I have created 2 logstash pipelines to index docs in **Index A** and **Index B**. I am pulling data from 2 tables in Oracle DB and indexing it in Index A & B respectively.

The requirement is to update all docs in **Index B** for a given attribute value term/filter, if the attribute changes in **Index A**

I am trying to trigger one to many bulk updates to docs in Index B using **\_update\_by\_query** & **HTTP** output plugin in logstash i.e. if a doc updates in Index A , I want to update multiple docs in Index B.

**Approach# 1**  
Here is what i have done to achieve this i Index A:

```auto
          filter {
             ruby {
    		code => "		
    			boolArr = [
    				{
              			'term'=> {
    						'rootAttr.childAttr1.keyword' => {
    							'value' => event.get('[rootAttr][childAttr1]')
    						}
    					}
    				},
    				{
              			'term'=> {
    						'rootAttr.childAttr2.division.keyword' => {
    							'value' => event.get('[rootAttr][childAttr2]')
    						}
    					}
    				}
    			]			
    			      		
          		event.set('[query][bool][must]', boolArr )
          		event.set('[script][params][newRootAttr]', event.get('rootAttr') )
          		     		      		      		
    			"
    	}   
      mutate {
        add_field => {
          "[script][lang]" => "painless"
          "[script][source]" => "ctx._source.rootAttr = params.newRootAttr"
        
        }
      }
    }
    output {

      http {
        url => "${elasticsearch_hosts}/index_B/_update_by_query?conflicts=proceed"
        http_method => "post"
        format => "json"
      }
    }

```

**Approach# 2:**  
Pull data from DB based on greatest value of tracking column from both the tables & re-index docs in Index B

I want to understand what is the best approach out of above 2 approaches from performance and error handling perspective.

Thanks,  
Mo

---

<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:** [April 23, 2020, 11:13pm UTC](https://discuss.elastic.co/t/logstash-performance-comparison-update-by-query-v-s-pulling-data-from-db-via-sql/225308/2 "2020-04-23T23:13:40Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.