performance of insert into select from

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

Hi! How about just one index on iUser and on both columns? What value is OPTCOMPIND in your onconfig? Try zero. What platform and versions are you running? Regards, Michael Chris McKay wrote: > 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

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 >

In article <zB2q4.2572$R37.9039@news-server.bigpond.net.au>, Chris McKay <chris_mckay@hotmail.com> writes >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 What? 3 separate indexes. The one of firstname cannot have very unique. This means that to insert a row informix uses the btree structure in the index to get to the first entry with the same firstname. All entries in an index with the same index key are stored in a list and Informix has to rewrite this list when a row in inserted. Keep indexes as unique as possible. Use indexes on >1 column. >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 uses the index on sessionid and filters the results using userid. select count(*) from iuser where sessionid = 'T0bdUiz1ICB65' How many rows? create index fred on iuser(sessionid,userid). Go to www.iiug.org ->Software and get utils2_ak under Database Admin. Run dostats out of that package on the iuser table. Are there triggers or constraints on the users table? > >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 > > -- David Williams

Chris McKay wrote: > > 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? It could not hurt and MAY help some queries including this one. For best performance you need to drop all unneccessary indexes on the target table (Users) before the INSERT INTO ... SELECT ... FROM and rebuild them afterwards. The only indexes you should leave are ones needed to insure integrity of the copied data (so if the data is guaranteed to be OK you can drop them all). Obviously you will have to lock the target table to prevent dirty data from other sources and to minimize locking overhead during the copy. Note that my dbcopy utility is 3X faster than the statement you are running when the -F option is enabled with or without the indexes! Dbcopy is part of the package utils2_ak in the IIUG Software Repository. -- Art S. Kagel & Family kagel@erols.com