Using Database Views with Case Statements to Vastly Improve App Performance

Wappler General How To
#1

I’m working on creating some more complex database queries in my app and I want to make sure I understand the limits of a normal Wappler Database Query before I move to making custom ones.

The question is my mind is whether I can create aggregates in a Database Query which are based on a value returned by a stored function, so to use the Database Query action which works the same as this:

select id, my_stored_function (response, response_type) as this_response from responses where id=:P1;

or to achieve something like this would I need to use a custom query?

#2

Well I’ve been able to answer my own question, and the possibility is amazing!

Basically you can define a “view” in MySQL with as many aggregates and additional columns as you choose, and that View acts like another table… so the view appears as another table in the Database Query action and can be selected from there.

The other thing I’ve discovered is the use of the CASE statement within a select which means I don’t really need to use a stored function.

So here is a simple view example.

We have a contacts table:

first_name   last_name   type_code
=====================================
Charlie      Cole              c
Sally        Smith             s

Referencing a contact_type table

type_code   type_name
=====================
c         Client
s         Suppler
e         Employee

Then we can create a view which does all the joins and aggregate columns that we need like this:

CREATE VIEW view_contacts AS
SELECT 
    contacts.id, contacts.first_name, contacts.last_name, contacts.type_code,
    CASE
        WHEN LENGTH(contacts.type_code)>0 THEN contact_types.type_name
        ELSE "Unspecified"
    END AS type_name
FROM contacts
    LEFT JOIN contact_types ON (contact_types.type_code=contacts.type_code)
WHERE 
    contacts.is_deleted=0;

Having defined this in the database, the table view_contacts is now available to choose in a Database Query and if required to be filtered in the Conditions tab by any of the fields that have been selected in the view.

THIS CAN MAKE A MASSIVE DIFFERENCE TO PERFORMANCE ON THE CLIENT SIDE!

As previous to realising this, I would resolve the display of type_name on the client side using a .where() statement which is very memory intensive!

6 Likes
Performance Optimising Your Wappler App - THE DEFINITIVE THREAD
#3

@UKRiggers, I’m wondering if you know about this technique and if it will help you to speed things up in your app!

Looking for some quick advice
#4

@Antony this is something I read earlier and am considering this and stored procedures. Thanks for your consideration.

1 Like
#5

Pleasure Neil.

I just finished using this technique on the worst performing screen of my app… it has reduced the screen load time from 10 seconds to 0.1 second! :rocket:

I got rid of any cross-referencing of data on the client side and now have a server action that provides the exact text that I need for my repeats directly from a couple of pretty large views. Each view uses joins to reference 7 different tables to get their data and some large CASE statements to create all the extra columns I need to control how the data is displayed. All done on the database server without any additional work on the client side.

It’s like magic!

responses_new
responses_new1167×761 109 KB

3 Likes
#6

Don’t :slight_smile:

1 Like
#7

What’s ironic to me is that your discovery about Views and Cases has been commonly used for years in PHP / MySQL applications.

Using Views which are defined by the SQL query itself and given a Name to refer to & pull information from has been a common solution to solve complicated data constructions for decades.

In all my uses every time I write a query which first names the View
CREATE VIEW short_userlist AS
SELECT blah blah etc

that view is Saved in the targeted database until such time that you specifically Delete it.

So, as you know, Antony –

  • A database can collect a lot of Views in it from different projects over time.

  • If we start a new project which references the same database then every View we have created in other projects that also query this databse are retrievable also by their unique name.

  • So a View can look like a table we call from a Query but that View query got SAVED into the database schema & persists around the clock on the database server.

  • A SQL/MySQL View is simply the Query itself saved and run everytime it is called for by our made up name.

The use of CASE in SQL & MySQL queries is another miracle worker to process Selections with conditionals across tables, when written correctly can knock a huge chunk of time off returning data.

  • Really look to get complicated queries written into VIEWS. They process so much faster straight out of the database engine before being sent to the Front end of an app.
2 Likes
#8

https://www.c-sharpcorner.com/article/how-to-create-a-stored-procedure-in-sql-server-management-studio/

1 Like