informix query performance problem

Question

The following SQL takes 62 seconds to return:

select getCreditBalance(Customerid)
  from business_apply
 where serialno = '20101013000005'

How to tune it?

Please tell me in detail.

I just want to know the steps I should do to tune it . we use IDS 9.04 .

As in JDBC I cant see output with SET Explain ON

shall I execute query in dbaccess (with SET Explain on)?

My problem is I cant get execution plan ...If I can get it ,I will post it here.

Answer 1

Make sure there is an index on serialno and tune the code in the getCreditBalance function. Without knowing what that does, it's hard to give you any additional help.

Answer 2

You've not given us very much to work on.

Basic questions

• What is the type of the column 'SerialNo'?
  • If it is a numeric column, don't quote the value you are searching for.
• Is there an index on 'SerialNo'?

The index is important; the type is not so important.

Crucial question

• What does the getCreditBalance() procedure do?

Auxilliary questions

• Which version of Informix are you using? Is it IDS or SE or something else?
• When did you last run UPDATE STATISTICS?
• Is there a problem connecting to the database, or is it definitely just this query that is slow?
• What language are you using to submit the query?
• Are there any networks with huge latencies involved?
• Which isolation level are you running at?
• How big is the Business_Apply table?
  • What is the size of each row?
  • How many rows?
• Which other tables are accessed by the getCreditBalance() procedure?
  • How big are they?
  • Do they have appropriate indexes?
• What sort of machine is the Informix server running on?
• What does the query plan tell you when you run with SET EXPLAIN on?
• Is there any chance you've got a failing disk and the o/s is taking forever to read it?