Timeout issue with Query Entity

sanghapriyab over 8 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

0 Tom Ryan
Appian Employee
over 8 years ago

Are you seeing any errors in the app server log? If so please attach the complete log.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Sally Mason
Appian Employee
over 8 years ago

In the first example, there are two query filters that must complete for the "OR" to be evaluated. In the second, there is only one query filter to evaluate. The "IN" can be expensive in terms of time. For some ideals on how to improve your queries performance, please see forum.appian.com/.../Database_Performance_Best_Practices.html
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 benjamins over 8 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
0 Arun Theja Muthyalu
Certified Lead Developer
over 8 years ago

@sanghapriyab

Hi,

Can you try including null() in the value array in the same query filter with IN operator?

Also, Consider creating "Index" on the column mapped to "text_column1".

I believe having null is better, instead of empty string. So, have a thought about this too.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Arun Theja Muthyalu
Certified Lead Developer
over 8 years ago

Having said that, you can have a single filter with IN operator checking for {"", null()} on the column which is now indexed.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 kondetiv over 8 years ago

@sanghapriyab Appian documentation says

When saving the values of a CDT to a database, a NULL value is not stored as NULL - instead, it is stored as an empty string ‘’.

Appian converts any NULL values of a CDT text field into an empty string. Writing an empty string to the database is safer than writing NULL value; a NULL value if not handled correctly can lead to NULL pointer exceptions.

When comparing values returned from a data store, use lenb() to check the length in bytes is 0 which would be the equivalent of a NULL value.

According to this write your query entities and and use lenb() function to know returned value is null or not.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 sanghapriyab over 8 years ago

We were not able to combine both the NULL and blank string compare in the same query. What we did instead as @benjamins suggested is we ensured is that all the nulls are converted to a blank string.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel