Re: GROUP BY ...speed issue

schaffner_at_pewter.chrr.ohio-state.edu wrote:
>
> The following is the SQL statements that use to run in about 20
> minutes. The database hasn't changed size...
>
> INSERT INTO TEMP_FREQ_COUNTER_1996
> (QNAME, Extention, QVALUE, COUNT)
> select c.qname, c.qvalue, count(*)
> from system.recipient R, freq_counter_1996 C
> WHERE r.qname=c.qname and
> r.response in (c.qindex, c.qvalue)
> group by c.qname, c.qvalue;
>
> Now it takes a day and half. I had the indicies rebuilt and still slow.
> The dba says that she didn't change anything. Any educated guess what may
> have changed to slow this down so much?
>
> -------------------==== Posted via Deja News ====-----------------------
> http://www.dejanews.com/ Search, Read, Post to Usenet

FREQ_COUNTER_1996 is clearly a continuously-increasing table. The problem is that you are selecting from and inserting into it in the same statement and the version you are selecting from is being continuously updated by the insert.
As with all such "reflexive" statements, each row inserted requires the creation of a rollback segment entry and the re-reading of all rollback segments created previously during the operation (the price you pay for read consistency).
Very roughly, the time taken will increase as the square of the number of rows in FREQ_COUNTER_1996.
In addition, you are in danger of encountering the dreaded "snapshot too old" message.

Recommendation:
Insert the new values into a temporary table and then use this table to insert into FREQ_COUNTER_1996 after you have closed the cursor.

-- 
Chrysalis

FABRICATI DIEM, PVNC
('To Protect and to Serve')
Terry Pratchett : "Guards Guards"