Re: performance of insert into select from

Drop index on users table, run the insert st. and recreate index(es).Pl. inform me the result. Alkesh Vipani ----- Original Message ----- From: "mips" <mips@zg.tel.hr> To: <informix-list@iiug.org> Sent: Tuesday, February 15, 2000 12:24 AM Subject: Re: performance of insert into select from > Is the query without INSERT slow? I have this problem: > SELECT ... FROM tab1,tab2> > is fast, but > INSERT INTO ... SELECT > > is very slow. > > >Hi, > > > >I have a query similar to : > >INSERT INTO Users (userId, ePassword, firstName, lastName) > > SELECT userId, ePassword, firstName, lastName > > FROM iUser > > WHERE iUser.userId= "cmc030" AND > > iUser.sessionId = "T0bdUiz1ICB65"> > > >The Users table is over a million rows and the iUser table is about 80,000. > >I have indexes on userId, firstName and lastName in the Users table and > >indexes on iUser.userId and iUser.sessionId. > > > >This insert runs very slowly. I have run UPDATE STATISTICS HIGH on > >iUser.userId and iUser.sessionId, as well as on the Users table, although > >not recently (there have only been a few inserts/updates since then). > > > >This is the output of sqexplain.out: > > > >... > > > >Estimated Cost: 118761 > >Estimated # of Rows Returned: 1000032 > > > > > > > >1) informix.iuser: INDEX PATH > > > > Filters: informix.iuser.userid= 'cmc030' > > > > (1) Index Keys: sessionid > > Lower Index Filter: informix.iuser.sessionid = 'T0bdUiz1ICB65' > > > >This sort of implies that before the insert completes there's a lot of work > >going on in the Users table. It was suggested to me that the indexes on > >first and last name may be quite deep with the possible number of > >duplications in each but even so I can't see why it takes so long given the > >performance of queries on the Users table. > > > >A question: Do I have to do UPDATE STATISTICS on serial columns with primary > >key indexes? > > > >Any ideas. > > > >Chris > > >

I have this problem: SELECT ... FROM tab1,tab2 WHERE .... is fast, but INSERT INTO xxx SELECT ... FROM tab1,tab2 WHERE .... is very slow. Table xxx doesn't have indexes. Any help or explanation?