CODEWITHSUNDEEP
performanceinformix
idiotgenius
idiotgenius

Reputation: 499

informix query performance problem

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.

Upvotes: 1

Views: 563

Answers (2)

Jonathan Leffler
Jonathan Leffler

Reputation: 755114

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?

Upvotes: 3

Michael Goldshteyn
Michael Goldshteyn

Reputation: 74470

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.

Upvotes: 1

Related Questions