informix query performance problem – Education Career Blog

by

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.

,

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?

,

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.

Leave a Comment