IDS Forum

Re: Re: Help needed in performance tuning

Posted By: ABHISHEK SRIVASTAVA
Date: Thursday, 20 November 2003, at 10:43 p.m.

In Response To: Re: Help needed in performance tuning (Ravi T )

Ok let me give some system information and also what we have done so far.

We have one 4 CPU 4 GB RAM HP-UX NClass Server.
This is running Informix Dynamic Server 9.3.

Because of performance problems, every weekend an update statistics command is run. We have also studied our index usage again and again ... (In our last iteration we got around 20% improovement when we removed all the redundant indexes and tried to use fewer indexes). But again this is not good enough for our customer... a query with a large date critera can still take 20 min to execute whereas our customer is ready to give us 2-3 minutes.

We have already asked Informix consultants from ibm to come and have a look at our system params and they said our system is configured optimally.

The system performs very well for less data. If I query the stored proc for one days of data I can get a sub second response time. The problem comes only when multiple days of data is a part of the query.

As I explained that in my stored proc I have one big query and I loop over the extracted records. for each records I need to validate its status and that is why I have to execute 2-3 stored procs as well.
The problem is that if someone choose a large date critera then 1st SQL can return upto 30K of records.
For each of the records in 30K records I execute 2-3 stored procs (having atleast 1 sql query).

This means that the complete stored proc executes 30K * 3 = 90K SQL queries.
I don't expect it to perform. However the business requirements are such that I can't see how can I eliminate the record by record processing.

If I try everything to do in one SQL rather than looping the SQL query will be very very complex and in some business cases unimplementable.

Has anyone dealt with this kind of situation before? I am really in need of pointers here.

One idea which I got from my friend was OK you can get rid of loop so why not make the loop faster? the sql queries in the loop query the table based on ID. So if I could group the IDs together on the disk, my loop queries should become very fast. But as I explained the ID can occur max 40 times in a table of 120 million. So creating a fragment on IDs looks ruled out for me. (correct me if I am wrong).

So how do I group all the IDs together on the disk (something which fragment/partitions do) without really creating an explicit fragment.
This may sound wierd.... but what else can be done?

regards,
Abhishek.



1. When you run onstat -u does it show the IDS is waiting for buffers ?

2. When you run onstat -g seg does it show dynamically allocated segments and are they happening when your process run ?

3. Do you run updpate statistics for the procedures ? and for the table ?

4. Are your indexes sitting on a separate dbspace ? If so try putting both the table and indexes on the same dbspace, this is in contrary to what informix recommends but I have seen better performanc doing this way. Test and trial will help u on this on a test box.

5. Have you checked to see if your indexes are corrupted ?

6. what is the raw size on your 120 million raw table ?

7. How many extents have been allocated to the table ?

8. Have you checked the iostat for the disk where your table and indexes are residing ?

9. How many cpus in the system ? Have you run onstat -g glo and see if you could take advantage of an additional one or two dynamically allocated cpus ?

10. If you have a lot of memory in the system have you tried forced residency ? (this way the IDS does not have to read from the disk if it had to)

11. What is the idle of the system when your query is slow ?

12. Is this a query that used to perform well before and all of a sudden it got jammed

Messages In This Thread

• Help needed in performance tuning
  ABHISHEK SRIVASTAVA -- Thursday, 20 November 2003, at 11:14 a.m.
  • Re: Help needed in performance tuning
    Ravi Krishna -- Thursday, 20 November 2003, at 11:55 a.m.
  • Re: Help needed in performance tuning
    Ravi T -- Thursday, 20 November 2003, at 1:04 p.m.
    • Re: Re: Help needed in performance tuning
      ABHISHEK SRIVASTAVA -- Thursday, 20 November 2003, at 10:43 p.m.
  • Re: Help needed in performance tuning
    ABHISHEK SRIVASTAVA -- Friday, 21 November 2003, at 3:07 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.