CODEWITHSUNDEEP
performanceinformix
idiotgenius
idiotgenius

Reputation: 499

How to tune Informix database performance

I want to how to do following general step:

  1. where to find slow SQL
  2. how to debug SQL (including functions)
  3. how to create index properly
  4. when using "update statistics", when should I use HIGH or LOW, and why?

I am going to write a paper about this topic; any help is welcomed.

Upvotes: 1

Views: 2791

Answers (1)

Jonathan Leffler
Jonathan Leffler

Reputation: 753960

One place to start is, funnily enough, the Informix Performance Tuning Guide, one section of the Informix 11.70 Information Centre. In particular, it explains most of what you need to know about UPDATE STATISTICS, and also about automatic update statistics.

For question 3, at one level, there isn't much to it - you follow the syntax from the manuals and it works. I'm guessing though that you're more concerned with whether you should create an index on a table; this would in part follow on from questions 1 and 2.

There are a variety of ways to find slow SQL. If you have OAT (OpenAdmin Tool), then it has ways to report the slowest queries. Alternatively, you can look to SET EXPLAIN.

If you have Informix 11.70, then there is a built-in SPL (stored procedure language) debugging facility. For earlier versions, Server Studio and Sentinel has some support. You can also look at the built-in TRACE facility and the related SET DEBUG FILE statement, but they tend to be tricky to interpret, and don't really give you performance information (more a question of correct vs incorrect functioning).

Upvotes: 3

Related Questions