Digital Success. One and Done.TM
Experience the best of everything Caspio has to offer.
Start Your Free Trial in 30 Seconds
Learn to Build Caspio Apps for FREE
Stretch the Limits of Low-Code: Mobile Apps, Messaging, Integrations & More
Stand With Ukraine
A number of things affect the performance of your search applications. Aside from infrastructure factors such as server load and bandwidth, database size and application design are two major areas under your control.
In this blog series, we offer several ideas and best practices to help you design your app for maximum performance. You may not be able to apply all these ideas to your online database application and that’s okay. Each option can give your app a little boost. The more you can apply, the faster your app will run.
This article is the first of a three-part series about improving the performance of your apps. This first article focuses only on the search portion of your application.
You may not realize it, but whenever you create an app using the point-and-click wizards, the Caspio App Engine analyzes the DataPage behind-the-scenes and looks for ways to fine-tune your data for the best possible performance. For example, it applies database indexes on relevant fields that are being used for searching or sorting. Also, if you try to create a lookup dropdown based on a large table, it automatically recommends an alternative method that gives you the same functionality with much faster performance.
When creating a large lookup, Caspio recommends generating a Summary Lookup Table to improve performance.
In addition to the many built-in optimizations that Caspio does automatically, you may want to consider the following best practices:
“Contains” is a handy comparison type that allows your end users to search anywhere within the text of a field. The downside is that it is a CPU-intensive search and there is very little the engine can do to speed it up. The problem grows even more if:
What to do:
Instead of “Contains”, consider using “Equal” or “Begins With” comparison types.
Example of The San Francisco Chronicle’s recipe guide offering an “Advanced Search” in addition to the basic search.
Any time the search form is displayed to one of your users, the Caspio App Engine has to render the search form while populating the values of each lookup by querying the relevant table or view. There are as many queries as you have dropdowns and listboxes.
Cascading dropdowns and listboxes require even more complex and CPU-intensive queries in order to display filtered results. Similarly, lookups that are based on complex views take more time than table lookups.
While the Caspio App Engine proactively optimizes these database-intensive form elements, you can apply additional best practices for improved performance.
What to do:
We want you to use as many lookups and cascades as you want, but it’s best to create separate tables for them. Avoid using a table with tens of thousands of records for generating a lookup of a few dozen values. Place the lookup values in their own table.
If you have too many options in a dropdown or a listbox, consider using the AutoComplete feature that recommends choices already in the database.
Caspio’s AutoComplete feature displays a selection box as the user types keywords (similar to Google search).
Views are virtual. They only exist as a definition until someone asks for the data. When your search application runs on a view, the view has to be put together before each search is performed. This is fine for two or three tables, but becomes complex with some join types based on too many tables, too many fields, or too much data.
Views that join more than 2 or 3 tables can hinder search performance.
What to do:
Sometimes you can’t do much about this, but if your application logic allows, consider avoiding views for search or restructure your app to use less complex views.
Let us know if these ideas work for you and if you have other suggestions.
Read Part 2: Make Your Search Results Page Load Faster
Caspio is the world’s leading cloud platform for building online database applications without coding. Start a free trial today and experience the power of no-code.
© 2022 Caspio, Inc. Sunnyvale, California. All rights reserved.