Ir para o conteúdo

Jitterbit App Builder performance tuning

Introduction

Performance tuning is a process of identifying and resolving bottlenecks, starting with app-level optimizations before considering more costly infrastructure changes.

This page covers the main areas of optimization: finding issues, application design, query optimization, and infrastructure.

Finding issues

To fix performance issues, you first need to find them. You can use these tools to diagnose problems:

  • Browser developer tools: Most web browsers have built-in developer tools that can help you find client-side issues, such as network requests that are slow or failing. For more information, see Using Browser Developer Tools.

  • Database-specific tools: Usually, your database system has tools to help you analyze and tune query performance. For example, you can use EXPLAIN ANALYZE in PostgreSQL or the Database Engine Tuning Advisor in Microsoft SQL Server to inspect query execution plans and identify problems.

  • App Builder monitoring: App Builder has built-in monitoring and database logs that can provide insight into what the application is doing.

Application-level optimization

This section covers optimizations you can make within your App Builder application and its underlying database schema.

Indexes

In a database, an index improves query performance by helping the database engine find data faster.

You should examine your app's SQL code and check that all columns used in WHERE, ORDER BY, or JOIN clauses are properly indexed. The goal is to create enough indexes to speed up your SELECT queries without creating so many that you significantly slow down UPDATE, INSERT, or DELETE operations.

Here are the key points about indexes:

  • Balance reads and writes: Adding indexes speeds up queries (reads), but slows down data modification statements (INSERT/UPDATE/DELETE). Find a balance that works for your app.

  • Index for sorting and grouping: Consider indexing columns used for ordering (ORDER BY) and grouping (GROUP BY). You may also benefit from indexing columns used in aggregate functions such as MIN(), MAX(), and COUNT().

  • Covering indexes: A "covering index" includes all the columns required for a specific query. This can be very efficient, as the database can answer the query using only the index, without having to read the table data itself. When creating a covering index, place columns used in equality comparisons (=) before columns used in inequality comparisons (such as > and <.).

  • Find missing indexes: You can use database-native tools to find missing or unused indexes. (For examples using SQL Server, see Find missing and unused indexes in SQL Server.

Queries

The following tips can help improve your query performance:

  • Write index-friendly WHERE clauses: Avoid using functions on indexed columns, as this can prevent the database from using the index. For example:

    This query is slow because the LOWER() function prevents the database from using an index on the FirstName column. The database must scan every row.

    WHERE LOWER(FirstName) LIKE '%bob%'

    This query is fast because it can use an index to find matching rows directly.

    WHERE FirstName LIKE 'Bob%'

  • Select only necessary columns: Avoid using SELECT *. By selecting only the columns your application needs, you reduce the amount of data that has to be processed and sent over the network. This is especially important for subqueries.

  • Simplify complex logic: Review and rewrite queries that have become overly complex or nested. Where possible, reuse subqueries or break complex logic into smaller, more manageable parts.

Business objects

Business objects can sometimes cause performance issues. These are the points to consider when analyzing business objects for suspected performance issues:

  • Data loading: App Builder loads all columns from a business object if the panel uses any of the following:

    • Conditional formatting.

    • A field with Supports Substitution enabled.

    • Any non-static visibility rules.

    Be aware of this behavior when designing complex panels.

  • Field usage:

    • Avoid including binary fields in a business object unless the field is being used by a panel (such as for a file upload or download).

    • Consider creating dedicated business objects that contain binary fields only for panels that specifically need them.

    • Ensure you use the correct data type for columns. For example, use INT for numbers and TIMESTAMP for dates, rather than VARCHAR or TEXT. This reduces storage size and improves query speed.

  • UI impact: Even if a business object's query is fast, the user may experience a slow app behavior. You should check for other things that might impact speed, such as complex sorting rules, or list boxes with large numbers of items.

Infrastructure-level optimization

If you have optimized your apps as much as you can and still need better performance, you can consider upgrading your infrastructure. Here are the options:

  • Horizontal scaling with load balancing: As the number of users grows, a single web server may not be able to handle the load. Horizontal scaling involves adding more web servers to process requests, and using a load balancer to distribute requests evenly among them. This not only improves performance but also increases reliability, as traffic can be rerouted if a server fails.

    Note

    Auto-scaling and load balancing are recommended for all cloud-hosted environments.

  • Database scaling: The database server can be a performance bottleneck in high-traffic apps. To handle read-heavy workloads, you can create read replicas. These are secondary database instances that contain a copy of the data from the primary database. You can configure your app to send all read-only queries to these replicas, leaving the primary database free to handle write operations.

    For extremely large datasets, you can consider sharding, where a database is partitioned into smaller pieces (shards) that are distributed across multiple servers.

  • Caching layers: For production environments, you should use a dedicated in-memory caching system, for example Redis or Memcached. These systems store frequently accessed data in RAM, providing much faster retrieval times than a database. When your app needs data, it first checks the cache and only queries the database if the data is not found. This can significantly reduce the load on the database server.

  • Resource allocation and monitoring: To ensure your servers are correctly sized, you should actively monitor their performance. Track key metrics such as CPU usage, memory consumption, and disk I/O to identify bottlenecks before they impact users.

    You should provision your servers with enough CPU, RAM, and fast storage (SSDs) to handle your expected workload. Vertical scaling (upgrading to more powerful hardware) can be a temporary solution before horizontal scaling becomes necessary.