Query Efficiency - Trying to make my app lightweight

App Building
1

I've created a working app used for logging issues in our company and emailing specific issues to clients. I am now working on version 2 of this app as the original has a lot (30 or so queries) that run both on load, on input change, in manual trigger and periodically. I want to build this version a lot more slimmed down.

I'm using the below select components to filter out "distinct" lists. The user works through a series of lists until they land on a "Comment" which gets sent to the customer & written to the database log.

I'm using queries like:

Import MS SQL database
select * from dbo.database

And from here onwards i'm using Query JSON with SQL

Select component 1 uses (Class1) the below:
select DISTINCT Class1 from {{formatDataAsArray(database.data)}}

Select component 2 (Class2) uses the below:
select DISTINCT Class1, Class2, from {{formatDataAsArray(database.data)}} where Class1 = {{select1.value}}

Select component 3 (Class3) uses the below:
select DISTINCT Class1, Class2, Class3 from {{formatDataAsArray(database.data)}} where Class1 = {{select1.value}} and Class2 = {{select2.value}}

Select component 4 (Comment)
select DISTINCT Class1, Class2, Class3, Comment from {{formatDataAsArray(database.data)}} where Class1 = {{select1.value}} and Class2 = {{select2.value}} and Class3 = {{select3.value}}

Final data that gets emailed to customer & written to the database log
select DISTINCT * from {{formatDataAsArray(database.data)}} where Class1 = {{select1.value}} and Class2 = {{select2.value}} and Class3 = {{select3.value}} and Comment = {{select4.value}}

image

The problem is that I am running a different query on each select component based on the previous select components value. My original app done server requests each time the query ran so this time around I've done a single server request and I'm querying that request with "JSON with SQL" to reduce the number of server requests but I've still ended up with a similar amount of queries and although it runs faster because the request is local it still doesn't solve my biggest gripe, being the volume of queries in the app. In this example forum post i'm using 6 queries to pull out a unique log entry from the database.

With that in mind and looking at the below screenshot of test data how would you build this type of app using the least amount of queries?

image

2

@rcanpolat I don't see a way around it as it first I was thinking about how you could use a CASE statement in a SQL query but there are just too many combinations...
You might be able to dump all the data in a table component and have the row selected use the comment when the row is selected to fire off the email using that row selected data? Maybe someone else will have a better idea... :slight_smile:

1 Like
3

Thank you for your input. Unfortunately my actual table too large for the user to find the correct entry without performing some sort of filters. I already tried running filters on the table but its too cumbersome, the select components make it much more simple to filter down the list.

4

Not sure I understand since you are always querying on {{formatDataAsArray(database.data)}} you could simply use the last query against a table component, so as the person selects the value for each class the table would filter and display the values for each successive value.
Populate the table component with all of the data from the query below.

Arrange each of the class and comment fields horizontally above the table and then use the query below. The table should always be populated with the query below and should always show data because of the OR options.

The query below should run after you run the initial query to get all of the data.

SELECT * FROM {{formatDataAsArray(database.data)}}
WHERE (Class1 = ANY({{select1.value}}) OR {{select1.value == ''}})  
AND (Class2 = ANY({{select2.value}}) OR {{select2.value == ''}}) AND (Class3 = ANY({{select3.value}}) OR associated_company_id = ANY({{select3.value}}) OR {{select3.value == ''}}) AND (Comment = ANY({{select4.value}}) OR {{select4.value == ''}})

And set this query to run when inputs change.

2 Likes