I encounter with the question "join performs on 3 table and each table has many rows and queries, it returns many responses". live data not required and 2 hours refresh allowed?? which option we use and why?? options:--store procedures or materialized view
Discussion posts and replies are publicly visible
Both will work. But the advantage of a materialized view is that the joins/selections have been pre-executed and all your application needs to request is a read of the data. If you went the stored procedure route the database engine would have to conduct the joins/selects in real-time. If the volume of data was very large then this would impact the performance of any given query.
I thought Appian Cloud / MariaDB didn't support materialized views anyway?
Stored Procedures i've found to be pretty performant even under pretty high pressure. And given the "2 hour refresh" parameter mentioned above, one could design a process that fires every 2 hours and runs a SP to populate a staging table, thus gaining all the efficiency (as far as i know) that a materialized view might offer. (BTW the whole post reads like they're trying to get answers to some sort of test question - i'm not sure what to make of it, really).
Hey Mike, yes you are right that's why I asked this question I marked the materialized view because in SQL it's correct but when I checked in Appian I didn't find the way to use materialized view because MariaDB didn't support but know how to use store procedures. in Appian thanks
Usually the best solution is to just use a standard View, and query the data live from there. This only stops performing well when we're dealing with exceptionally large data sets (hundreds of thousands of rows perhaps) and/or incredibly complex / resource-intensive JOIN operations.
Can you tell us a bit more about your use case and why a standard View wouldn't be sufficient for you?