Index Question.

I'm having a problem with a query taking an hour or more to run. The three fields in the WHERE clause uses an index, and returns about 1.5 million records out of approx 10 million for the date range in question (there are about 45 million records total). The GROUP BY clause groups those records into roughly 200 records. My questions are: Would it help to create another index on the fields that I'm grouping by even though they are unrelated to my where clause? How much performance gain could I expect if I did the division in a seperate program? The worst part with this whole situation is that I also have to update the same records that are returned by this query after a report is generated. The update takes about 6 to 8 hours (1.5 million rows, updating one field), and puts a tremendous load on the system (which is being used for other queries at the same time). Another question: Any thoughts/strategies on how to improve performance on a table with more than 40 million rows that is starting to grow at 10 million a week? More information. The table in question has about 50 fields in it, 10 or so are foreign keys to other tables, and there are also around 8 indexes used for various reports. I know this may be kind of vague, but any help with this situation, and advice on growth strategies would be greatly appreciated. Here's the query i've basically been talking about: SELECT field01, field02, field03, field04, COUNT(*) AS field05, SUM(field06/60) AS field06sum, SUM(field07/100) AS field07sum, SUM(field08) AS field08sum, SUM(field09/60) AS field09sum, SUM(field10/100) AS field10sum, SUM((field11+field12)/100) AS field1112sum FROM table01 WHERE search01='VALUE' AND search02 BETWEEN '02/09/1999' AND '02/15/1999' AND search03=VALUE GROUP BY field01, field02, field03, field04 ORDER BY field01, field02, field03, field04

Russell Bierschbach (rbierschbach@simpletel.com) wrote: > good description of problem snipped Just a question. Why are you batching this? I don't know the details of your application but on a couple of occasions I've had people ask about batching operations where doing the same maintenance in a more on-line mode actually improved the system's performance, and also made the application more responsive. Kind of strategic answer. Not really tactical.

In article <7akfuu$ip0$1@remarQ.com>, Russell Bierschbach <rbierschbach@simpletel.com> writes >I'm having a problem with a query taking an hour or more to run. The three >fields in the WHERE clause uses an index, and returns about 1.5 million >records out of approx 10 million for the date range in question (there are >about 45 million records total). The GROUP BY clause groups those records >into roughly 200 records. My questions are: Would it help to create another >index on the fields that I'm grouping by even though they are unrelated to >my where clause? How much performance gain could I expect if I did the >division in a seperate program? > >The worst part with this whole situation is that I also have to update the >same records that are returned by this query after a report is generated. >The update takes about 6 to 8 hours (1.5 million rows, updating one field), >and puts a tremendous load on the system (which is being used for other >queries at the same time). Another question: Any thoughts/strategies on >how to improve performance on a table with more than 40 million rows that is >starting to grow at 10 million a week? > >More information. The table in question has about 50 fields in it, 10 or so >are foreign keys to other tables, and there are also around 8 indexes used >for various reports. > >I know this may be kind of vague, but any help with this situation, and >advice on growth strategies would be greatly appreciated. > >Here's the query i've basically been talking about: > >SELECT > field01, field02, field03, field04, > COUNT(*) AS field05, > SUM(field06/60) AS field06sum, > SUM(field07/100) AS field07sum, > SUM(field08) AS field08sum, > SUM(field09/60) AS field09sum, > SUM(field10/100) AS field10sum, > SUM((field11+field12)/100) AS field1112sum >FROM table01 >WHERE search01='VALUE' > AND search02 BETWEEN '02/09/1999' AND '02/15/1999' > AND search03=VALUE >GROUP BY field01, field02, field03, field04 >ORDER BY field01, field02, field03, field04 > > > Do you have an index on (search01,search03,search02,field01,field02,field02,field04, field06,field07,field08,field09,field10,fieldl11,field12) ?? This would allow a key only search If this is not possible then at least an index on (search01,search03,search02) Also how many CPUs do you have on this machine? I would look into having this table fragmented across several disks and using PDQ to at least allow the scan/group/order by phases to be done in parallel. How are the updates done? -- David Williams

Have you tried creating stored procedures for the count and sum fields, and then call the procedures from the query ? RJR David Williams wrote in message ... >In article <7akfuu$ip0$1@remarQ.com>, Russell Bierschbach ><rbierschbach@simpletel.com> writes >>I'm having a problem with a query taking an hour or more to run. The three >>fields in the WHERE clause uses an index, and returns about 1.5 million >>records out of approx 10 million for the date range in question (there are >>about 45 million records total). The GROUP BY clause groups those records >>into roughly 200 records. My questions are: Would it help to create another >>index on the fields that I'm grouping by even though they are unrelated to >>my where clause? How much performance gain could I expect if I did the >>division in a seperate program? >> >>The worst part with this whole situation is that I also have to update the >>same records that are returned by this query after a report is generated. >>The update takes about 6 to 8 hours (1.5 million rows, updating one field), >>and puts a tremendous load on the system (which is being used for other >>queries at the same time). Another question: Any thoughts/strategies on >>how to improve performance on a table with more than 40 million rows that is >>starting to grow at 10 million a week? >> >>More information. The table in question has about 50 fields in it, 10 or so >>are foreign keys to other tables, and there are also around 8 indexes used >>for various reports. >> >>I know this may be kind of vague, but any help with this situation, and >>advice on growth strategies would be greatly appreciated. >> >>Here's the query i've basically been talking about: >> >>SELECT >> field01, field02, field03, field04, >> COUNT(*) AS field05, >> SUM(field06/60) AS field06sum, >> SUM(field07/100) AS field07sum, >> SUM(field08) AS field08sum, >> SUM(field09/60) AS field09sum, >> SUM(field10/100) AS field10sum, >> SUM((field11+field12)/100) AS field1112sum >>FROM table01 >>WHERE search01='VALUE' >> AND search02 BETWEEN '02/09/1999' AND '02/15/1999' >> AND search03=VALUE >>GROUP BY field01, field02, field03, field04 >>ORDER BY field01, field02, field03, field04 >> >> >> > > Do you have an index on > (search01,search03,search02,field01,field02,field02,field04, > field06,field07,field08,field09,field10,fieldl11,field12) > ?? > This would allow a key only search > > If this is not possible then at least an index on > (search01,search03,search02) > > Also how many CPUs do you have on this machine? > > I would look into having this table fragmented across several disks > and using PDQ to at least allow the scan/group/order by phases to > be done in parallel. > > How are the updates done? > > > >-- >David Williams