Re: temporary space - doesn't scale?

In the year of Our Lord Wed, 13 Dec 2000 16:59:02 -0500, Matthew Cornell <cornell@cs.umass.edu> broke a vow of silence to utter: >Obnoxio The Clown wrote: >> That's right, blame the server. >> >> You haven't really given us enough info to make any kind of assessment here, but >> you're definitely doing a cartesian product between P1 and P2, possibly even a >> 3-way cartesian. > >Right. That's the first way we worked out the SQL. Maybe that's an >unreasonable approach. Just out of interest, how may rows in the two tables, and how wide are they (in bytes)? >> -229 also implies an OS file error, rather than a dbspace error. Have you, for >> instance, got DBTEMP or PSORT_DBTEMP set to a directory that doesn't exist or is >> full? Are the permissions OK on your tempdbs chunk? Is /tmp full? > >DBSPACETEMP is set to tempdbs, where tempdbs was created using: No, I mean DBTEMP or PSORT_DBTEMP environment variables. > onspaces -c -d tempdbs -t -p ~informix/links/chunk2 -o 0 -s>2000000 > >The chunk is a cooked file, and is working fine for other queries. After >I bumped it up to 2GB it got past the error, so it wasn't an OS file >error. I used onstat to look at the space/chunk as the program ran, and >I could see the free space dropping each second. With 2GB it stops with >300K free left - close! I'm concerned And well you should be. However, it's unlikely that there is a sound reason for this to be so. Hence my request for the number of rows and size of each row. I have a sneaking suspicion that if you take P1 * P2 * long-T-something (possibly * long-t-something again), you'll get to the size of ~2GB. In other words, rewriting the SQL to avoid a cartesian (always a good idea) could make this problem diappear. I realise you don't want generalities if you are exhausted, but if you are joining N tables together, you must, in general, restrict each table in the join to only rows that are related to each other. Something like: SELECT ... FROM P1, P2, T WHERE (what you said) AND P1.key = P2.key AND T.joinfield1 = P1.joinfield1 AND T.joinfield2 = P2.joinfield2 Ish. Perhaps you could post the schema for the tables in question if this doesn't make sense? >> Anyway, you seem to be like that Marques chappie -- quick to point fingers and >> slow to read manuals (in this case, the output from finderr) -- maybe you should >> use that super special Postgres and leave us to fiddle with this inferior junk. > >I can't believe Informix is junk - there are so many dedicated users. >:-) You are quite right to aim your sarcasm at me - I'm writing while >frustrated, tired, and pressed for results, which makes for regrettable, >incomplete, and generally stupid posts like mine these last few days. I >apologize. Ah. All is forgiven.

In article <3a37f786.14219496@News.CIS.DFN.DE>, obnoxio@hotmail.com (Obnoxio The Clown) wrote: > In the year of Our Lord Wed, 13 Dec 2000 16:59:02 -0500, Matthew Cornell > <cornell@cs.umass.edu> broke a vow of silence to utter: > > >Obnoxio The Clown wrote: > >> That's right, blame the server. > >> > >> You haven't really given us enough info to make any kind of assessment here, but > >> you're definitely doing a cartesian product between P1 and P2, possibly even a > >> 3-way cartesian. > > > >Right. That's the first way we worked out the SQL. Maybe that's an > >unreasonable approach. > > Just out of interest, how may rows in the two tables, and how wide are they (in > bytes)? > > >> -229 also implies an OS file error, rather than a dbspace error. Have you, for > >> instance, got DBTEMP or PSORT_DBTEMP set to a directory that doesn't exist or is > >> full? Are the permissions OK on your tempdbs chunk? Is /tmp full? > > > >DBSPACETEMP is set to tempdbs, where tempdbs was created using: > > No, I mean DBTEMP or PSORT_DBTEMP environment variables. > > > onspaces -c -d tempdbs -t -p ~informix/links/chunk2 -o 0 -s> >2000000 > > > >The chunk is a cooked file, and is working fine for other queries. After > >I bumped it up to 2GB it got past the error, so it wasn't an OS file > >error. I used onstat to look at the space/chunk as the program ran, and > >I could see the free space dropping each second. With 2GB it stops with > >300K free left - close! I'm concerned > > And well you should be. However, it's unlikely that there is a sound reason for > this to be so. Hence my request for the number of rows and size of each row. I > have a sneaking suspicion that if you take P1 * P2 * long-T-something (possibly > * long-t-something again), you'll get to the size of ~2GB. > > In other words, rewriting the SQL to avoid a cartesian (always a good idea) > could make this problem diappear. I realise you don't want generalities if you > are exhausted, but if you are joining N tables together, you must, in general, > restrict each table in the join to only rows that are related to each other. > > Something like: > > SELECT ... > FROM P1, P2, T > WHERE (what you said) > AND P1.key = P2.key > AND T.joinfield1 = P1.joinfield1 > AND T.joinfield2 = P2.joinfield2> > Ish. > > Perhaps you could post the schema for the tables in question if this doesn't > make sense? > > >> Anyway, you seem to be like that Marques chappie -- quick to point fingers and > >> slow to read manuals (in this case, the output from finderr) -- maybe you should > >> use that super special Postgres and leave us to fiddle with this inferior junk. > > > >I can't believe Informix is junk - there are so many dedicated users. > >:-) You are quite right to aim your sarcasm at me - I'm writing while > >frustrated, tired, and pressed for results, which makes for regrettable, > >incomplete, and generally stupid posts like mine these last few days. I > >apologize. > > Ah. All is forgiven. > SELECT P1.item_id, P2.item_id, date, time, quantity, trans_label FROMav_2 P1, av_2 P2, training_data_fully_labeled WHERE P1.value=training_data_fully_labeled.origin AND P2.value=training_data_fully_labeled.destination Maybe I am missing something, I agree this seems to be getting a large result set, but it doesn't seem to be a cartesian product to me. It will do something like get the results set of P1 and training_data_fully_labeled joined, and then join that result set to P2. Unless informix is doing the cartesian product first, which would be pretty wierd... Will Sent via Deja.com http://www.deja.com/