IDS Forum

Help needed in performance tuning

Posted By: ABHISHEK SRIVASTAVA
Date: Thursday, 20 November 2003, at 11:14 a.m.

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.