Re: Tech Tip du Jour: Over the threshold

Hi, I am using Informix 11 on Red hat linux. One query with two tables joined runs fast and gives result within 1-2 minutes. But when I use same query to insert result into temp table "select... from .. insert into temp t1 with no log" it takes around 8-9 hours. I doubt about temp dbspace but temp dbspaces are defined in config and environment. I do not see much change in tempdbspace read/write with onstat -D. Also no files created in /tmp directory. What can be issue? and where to check? is it know issue with informix11? Please help. Regards, Nandkishor --000e0cd329b4c1a79d046f26bc9f

Nandkishor Singare wrote: > Hi, > > I am using Informix 11 on Red hat linux. > One query with two tables joined runs fast and gives result within 1-2 > minutes. > But when I use same query to insert result into temp table "select... from > ... insert into temp t1 with no log" it takes around 8-9 hours. > > I doubt about temp dbspace but temp dbspaces are defined in config and > environment. > I do not see much change in tempdbspace read/write with onstat -D. > Also no files created in /tmp directory. > > What can be issue? and where to check? is it know issue with informix11? > Please help. Kinell, I was really struggling to figure out where this fitted into the subject matter. :o) Can you post onstat -d output? Also, what version of IDS, what version of RatHed, and can you post the explain plan of the query? How many rows are in the tables? -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.

Do you have the temp dbspaces defined in DBSPACETEMP environment variable in the user's environment and exported (or at least in the server's ONCONFIG file? If not the temp table is being created in the ROOTDB dbspace which is a logged dbspace, even though you have specified WITH NO LOG. Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Jul 20, 2009 at 1:55 PM, Nandkishor Singare <ns.singare@gmail.com>wrote: > Hi, > > I am using Informix 11 on Red hat linux. > One query with two tables joined runs fast and gives result within 1-2 > minutes. > But when I use same query to insert result into temp table "select... from > ... insert into temp t1 with no log" it takes around 8-9 hours. > > I doubt about temp dbspace but temp dbspaces are defined in config and > environment. > I do not see much change in tempdbspace read/write with onstat -D. > Also no files created in /tmp directory. > > What can be issue? and where to check? is it know issue with informix11? > Please help. > > Regards, > Nandkishor > > --000e0cd329b4c1a79d046f26bc9f > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001636c5b1f42b6992046f27d330