IDS Forum

Re: Help needed in performance tuning

Posted By: Ravi Krishna
Date: Thursday, 20 November 2003, at 11:55 a.m.

In Response To: Help needed in performance tuning (ABHISHEK SRIVASTAVA)

Abhishek,

First make sure that the query the stored proceudre is using uses
appropriate indexes. Have you tried SET EXPLAIN ON. What sort
of execution plan informix is generating.

Chances are, with the addition of indexes, your performance problem may disappear
without fragmenting.

----- Original Message -----
From: "ABHISHEK SR...." <abhishes@hotmail.com>
To: <ids@iiug.org>
Sent: Thursday, November 20, 2003 11:14
Subject: Help needed in performance tuning [2204]


> Hello All,
>
> I am having serious performance problems in my application. I have stored proc which are taking
half an hour to execute. Please help me. I will be very gratefull.
>
> I have a very large table in my application. It has 120 million rows in it.
> This table is continiously inserted with rows with around 50 rows per second.
> I keep 45 days of data in it. after that the data is purged. The inserts are done by means of a C
process running as a daemon thread.
>
> I have the task of generating reports from this table. The report is generated based on criteria
like country, date, time etc.
>
> For each query 20K records are returned.
>
> Today my queries are taking 30 min to run.
>
> The reason is that I don't just select records. After selecting the records I do a "foreach" in a
stored proc and do some calculation. each row in the first query has an ID field. In my table the
same ID can be inserted many times. So for each unique ID that I find in the loop, I should find all
the records in the same table with that ID and look at a field called Code. When i find all the
values of a code for a corresponding ID, I can make determine the status of that ID.
>
> But this is very slow. Currently I don't have any fragmentation in place yet.
>
> I tried fragmenting based on date, But it did not help much in improving performance.
>
> Please tell me what can I do to improove performance.
>
> I have found that the first query takes only 25% of total execution time. But the foreach loop
takes the rest 65% of the time.
>
> To improove the performance time, I want to put all the IDs together on the disk so that the
second query where I find all the records of the same ID in the table and get their code becomes
faster.
>
> But can I fragment on ID? because my table is very big and there are max 40 records for an ID in a
table.
>
> What can I do to make it run faster?
>
> If I cannot fragment the data based on ID, is there any other way I can organize my data in a
better way on the disk so that queries run faster?
>
> I have already done a lot of re-search on the indexes, and I think we are using the most optimized
form.
>
> I would really appiciate any help from you in this regard. Thank you so much for your help in
advance. Please do help me.
>
> regards,
> Abhishek.
>
>

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.