Re: GROUP BY ...speed issue

I am inserting into the TEMP_FREQ_COUNTER_1996 table from the FREQ_COUNTER_1996 and RECIPIENT tables. The "from" tables are not the same as the "into" table. Is this what you were implying?

In article <343B49E2.38F1_at_iol.ie>,
  cellis_at_iol.ie wrote:
>
> 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"

-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet