1.1.1

Chapter 49
Best Practices for Tuning Performance
You can help ensure optimum performance by following best practices for SQLFire servers and applications.
Tune Application Logic
Follow these guidelines for tuning applications.
• Use prepared statements for all operations. This greatly reduces latency.
• Avoid expensive join queries by denormalizing and adding constraints.
• Design queries so that results are pruned to the smallest result set first.
• Create indexes before loading data.
• Use sufficient indexes to optimize frequently executed queries. Because indexes add to the footprint of your
application, make sure that the indexes you add provide a meaningful performance benefit.
• Minimize the use of transactions. SQLFire provides atomicity and isolation for single rows even without
transactions.
• Keep the transaction duration and the number of rows involved in the transaction as low as possible. SQLFire
acquires locks eagerly, and long-lasting transactions increase the probability of conflicts and transaction failures.
• Minimize the use of REPEATABLE_READ transactions.
Reduce Distribution Overhead
Latency-sensitive applications must minimize interprocess communication. For SQLFire, this means reducing
traffic between clients and servers, and between servers, because the data itself is distributed.
• Choose a partitioning strategy that colocates most data access and transactions in a single partition.
• To assist with colocation, replicate smaller tables that are rarely modified
• Avoid global indexes.
• Avoid distributed transactions.
• Use data-aware procedures to execute application logic locally to the data.
• Use peer clients when possible. Peer clients have access to metadata about partitioned tables, and they can
directly access the SQLFire server that has the needed data. By default, a thin client may be connected to a
server that does not have the necessary data. If the server does not host the data, it must forward the client
request to a server that does have it. This process adds a round-trip that is not necessary with peer clients. Peer
clients also offload work from the servers, which allows servers to support more clients, including thin clients.
However, peer clients are only appropriate in relatively small numbers. Embedded Peer-to-Peer Deployment
on page 214 provides more details.
• When using thin clients, set single-hop-enabled to true to minimize network round trips where possible. If
necessary, increase the sqlfire.client.single-hop-max-connections system property to a value that gives the best
performance for concurrent single-hop connections. Be sure to start a network server on each data store. See
Enabling Single-Hop Data Access on page 118 for details on when single hop can be utilized.
311