DB utilization is high for SQL queries

Hi Team,

I have a list retrieved using a complex SQL query which queries JSON data too.

On this list there are many operations that can be performed like user can search using various criteria.

So I have implemented it by using a local variable(queryText) which is passed as an Expand inline parameter to this SQL query. On the basis of the search filter selected by the user I have a switch and accordingly the value is assigned to the querytext. eg. 

SyntaxEditor Code Snippet

"and JSON_VALUE({tablename}.[JSON],'$.info.address.PostCode') LIKE '%"+ EncodeSql(SearchQuery) +"%' " 

and this is passed as an expand inline parameter to the SQL.

(Please note the main query is in the preparation and when the search criteria is added i refresh this same SQL called in the preparation)

I want few suggestions on can this search functionality be implemented in a more optimized way.

Or can this query be cut down into simpler queries instead putting as one query.


Did you create an index on the table for search columns

Indexes are special lookup tables that the database search engine can use to speed up data retrieval