Help needed in performance tuning

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.

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. > >

Some thoughts since you have not specified any system information which are viable for a tuning project : 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 ? Hope some of these questions would help u to find where the bottleneck is !!! Thanks. Ravi. "ABHISHEK SR...." <abhishes@hotmail.com> wrote: 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. --------------------------------- Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard

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

Yes Clifton, you got it right. I first select a set of records and then seach the same table for each record select in the first set of records. However aliasing is not easy for me because our application is very complex. Let me explain. Each record has a code. Then we have reports which are based on business state. Many codes contribute to a state. For example state A can be achieved if for a particular ID has records each with code 1, 2, 1.1 , 2.2 occur and 3 does not occur. The IDs which are included for a state are kept in config table. Most of our reports are like show all records for a country/region which have state A and not state B. So my SP today looks like foreach select id, b, code, d into ID, B, CODE, D from all_record_table where country='ab' and date <= 'x' and date >= 'y' order by id -- check if state A is achieved foreach select 'x' into temp from all_record_table, config where code id = ID and CODE IN ( select CODE from config where type='stateA_main') andCODE IN (select CODE from config where type='stateA_sub') and CODE NOT IN (select CODE from config where type='stateA_reject') Let isA = 1; end foreach -- state A not achieved continue with other records if (isA = 0) then continue foreach; end if --- check if state B is achieved foreach select 'x' into temp from all_record_table, config where code id = ID and CODE IN ( select CODE from config where type='stateB_Man') andCODE IN (select CODE from config where type='stateB_Sub') and CODE NOT IN (select CODE from config where type='stateB_reject') Let isB = 1; end foreach -- state b achieved continue with other records if (isB = 1) continue foreach; end if -- state A achieved and B not achieved. record considered for report. return id, b, code, D with resume next; end procedure I have tried but if I try to do every thing in one SQL it becomes very very complex for me. Is there any way to simplify this? I have thought about it for a long time.. but haven't came up with any idea. Actually when I do a set explain on, it doesn't show me much bottlenecks on the SQL because I don't think the set explain on sees the load generated because of foreach repeated times. It is only at runtime the problem occurs. regards, Abhishek. > If I remember the string of emails related to this problem, you have a query > that, once is searches a table for records, searches the same table once > again. > Have you given any thoughts to using an alias for the table in your first > select and selecting from the table twice in the same select ... such as: > select a.value, b.value > from table1 a, table1 b > where a.fieldname1 = b.fieldname2 > Perhaps you can combine two or more of your selects into one. > Hope this helps. > Clifton