Transcript Optimization I: Optimizing InterBase Applications

2150
Optimization I: Optimizing
InterBase Applications
Craig Stuntz
TeamB / Senior Developer
Vertex Systems Corporation
About this Course
– How to design a robust, high-performance
client or middle tier application
– How to diagnose and correct problems in
slow applications.
– Will not cover optimizing SQL and
metadata (that’s course #3156, next)
– Will not cover optimizing server
configurations (that’s course #6104 – CD
only)
The Golden Rule of
Optimization
Optimize the slow stuff first.
In other words, it is important to optimize
use of your own time as well as your
application.
Corollary to the Golden
Rule
Always develop database
applications using a database
populated with real data.
– Use real customer data, if available.
– Scramble to protect customer privacy.
– Use a test data generator if real data is
not available.
– Avoid using a local server.
Application Design
–
–
–
–
–
Client / server or n-tier?
Choosing database access components
Make reasonable requests to the server
Use transactions properly
Understand InterBase internals
Client / Server vs. n-Tier
– Consider also “psuedo n-tier” design
– n-Tier designs should be stateless
– dbExpress and ADO.NET practically
require n-Tier design; IBX works better
this way
Choose DB Access
Components
– For Win32 Delphi or Kylix, choose between IBspecific components or DB-independent
components.
– For .NET languages, use BDP (or VCL for
.NET components such as IBX/dbExpress if
using VCL for .NET)
– For Java, JDBC – much better in IB 7
– Avoid direct use of the IB API when possible
Make Reasonable Requests
to the InterBase Server
–
–
–
–
Think in terms of result sets, not tables.
Use only needed columns/rows
Avoid iterating over result sets on client tier
Know and use SQL functions like EXISTS,
MIN/MAX, ROWS…
– This subject is covered in more detail in
Course #3156 (Optimization II)
–
–
–
–
–
–
Understanding
Transactions
Use correct isolation mode
Consider read-only
CommitRetaining – Improved in IB 7.1
End transactions ASAP
Understand rollbacks
Use multiple concurrent transactions when
necessary
InterBase Internals
– Understand InterBase’s multi-generational
architecture
– Understand sweeping and garbage collection
– Long-running transaction overhead
– Data distribution and rebalancing indices
Diagnosing and Correcting
Performance Problems
– Common bottlenecks and how to fix them
– Tools
– A workflow for correcting performance
problems
Common Bottlenecks
–
–
–
–
–
Excessive fetching
Long-running transactions
Record counting
Unnecessary iteration
Unnecessary rollbacks
Excessive Fetching
– Shows up immediately in SQL monitor
– Hard to avoid with BDE sometimes
– Prefer JOINs over Delphi lookup fields when
lookup tables are large
Long-running Transactions
– Diagnose with IB 7 Performance
Monitoring
– Understand sweeping and garbage
collection
– IB 7 & 7.1 significantly change the rules
– Use DataSnap or your favorite inmemory dataset for interactive browsing
Record Counting
– Exact record counts are expensive on
InterBase due to MGA.
– However, it’s easy to tell when a result set is
empty!
– If you must count (as with a progress bar), pick
a smaller number to count.
Unnecessary Iteration
– Can often be replaced with SQL
– When simple SQL won’t work, a stored
procedure will be much faster
– But consider blocking when using a proc
Understand Rollbacks
– Only necessary when you need to undo a
successful statement.
– Affects when the sweep runs.
– InterBase will convert to commit when possible
– improved in 7.1
Tools
–
–
–
–
–
Source code profilers
Gstat and Performance Monitor
SQL monitor
Query tuning tools
TCP/IP monitor
Optimizing Existing
Applications
–
–
–
–
–
–
When does the problem happen?
Does the problem get worse with time?
Profile the application
Use SQL monitoring
Isolate and fix bad SQL
Use performance monitoring.
Thank You
2150
Optimization I: Optimizing InterBase SQL and
Metadata
Please fill out the speaker evaluation
You can contact me further by posting
questions to the Borland newsgroups