Timeout issue with Query Entity

sanghapriyab over 7 years ago

Hi,

We are experiencing problems with a query entity. When we pass a query filter with multiple filters on the same varchar column, the query entity takes around 9 sec to give us the result. When the query entity rule is called from process model it fails 2 out 3 times. The query filter is written like this.

a!queryLogicalExpression( operator: "OR", filters:{ a!queryFilter(field:"text_column1",operator:"IN",value:{""}),a!queryFilter(field:"text_column1",operator:"IS NULL")})

When we remove on of the condition it only take 200 ms.

a!queryLogicalExpression( operator: "OR", filters:{ a!queryFilter(field:"text_column1",operator:"IN",value:{""})})

Is there any reason for this or can we write this query in a different format?

Also, this is not a data threshold issue as the batch size is 1.

Thanks,
Sangho

Discussion posts and replies are publicly visible

Parents

0 benjamins over 7 years ago

Agree that IN and OR operations can be expensive. Is there a chance that the data can be empty and null or can you work around that and ensure that the data is always null. Can you expect db scripts to update the database table and ensure any entries that are blank are set to null?

Setting a batch size to 1 doesn't mean that the database is not going to be doing a full table scan. Look at the indexes on the table as well. If its a large table, you may need to look at tweaking it as well.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel

Reply

0 benjamins over 7 years ago

Agree that IN and OR operations can be expensive. Is there a chance that the data can be empty and null or can you work around that and ensure that the data is always null. Can you expect db scripts to update the database table and ensure any entries that are blank are set to null?

Setting a batch size to 1 doesn't mean that the database is not going to be doing a full table scan. Look at the indexes on the table as well. If its a large table, you may need to look at tweaking it as well.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel

Children

No Data