Thread

Re: Select-Insert-Query

Oliver Elphick <olly@lfix.co.uk> — 2004-03-02T11:48:06Z

On Tue, 2004-03-02 at 00:49, postgres@countup.de wrote:

> what is the most performant way to select for example the first 99
> rows of a table and insert them into another table... 
> 
> at the moment i do this: 
> 
> for userrecord in select * from table where account_id = a_account_id
> and counter_id = userrecord.counter_id and visitortable_id between
> a_minid and a_maxid limit 99 loop 

Using LIMIT without ORDER BY will give a selection that is dependent on
the physical location of rows in the table; this will change whenever
one of them is UPDATEd.

> insert into lastusers (account_id, counter_id, date, ip, hostname)
> values(a_account_id,userrecord.counter_id,userrecord.date
> ,userrecord.ip,userrecord.hostname); 
> end loop; 
> 
> i think "limit" is a performance killer, is that right? but what to do
> instead 

I'm sure it is the loop that is the killer.  Use a query in the INSERT
statement:

INSERT INTO lastusers (account_id, counter_id, date, ip, hostname)
  SELECT * FROM table
          WHERE account_id = a_account_id AND
                counter_id = userrecord.counter_id AND
                visitortable_id between a_minid and a_maxid
          ORDER BY date DESC
          LIMIT 99;

-- 
Oliver Elphick <olly@lfix.co.uk>
LFIX Ltd