How to Make Your Search Applications Perform Faster

April 23, 2012

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.

Part 1: Making Searches Fast

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:

Limit “Contains” Comparison Type

“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:

Multiple search fields are using the Contains comparison type.
Your table is very large.
Your fields contain a large amount of text utilizing the Text(64000) data type.

What to do:

Contains runs faster on Text(255) fields than on Text(64000) fields, even if both have the same amount of text. If possible, consider converting Contains search fields from Text(64000) to Text(255).
Consider using the “Equal” comparison type because it’s enormously faster.
If Equal is not optimal, see if “Begins with” makes sense for your search.

Instead of “Contains”, consider using “Equal” or “Begins With” comparison types.

Reduce the number of Contains instances.
If you have a lot of data in your table, consider creating a view to filter the unnecessary fields and build your search application on this view.
Give your users two search options: “Basic Search” and “Advanced Search”. Make Basic Search the default using very few search fields and reserve the Contains option in the Advanced Search only.

Example of The San Francisco Chronicle’s recipe guide offering an “Advanced Search” in addition to the basic search.

Lookups, Cascading Dropdowns and Listboxes

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).

Searches on Complex Views

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