Re: Long transaction problem
}From: scheer@stadt-mh.de (Dirch Scheer)
}Subject: Long transaction problem
}Date: 28 Jul 1994 11:46:16 GMT
}X-Informix-List-Id: <news.7873>
}
}Hello out there,
}
}we are running I-Online 5.0 on a HP9000/867 (HP-UX 9.0). The database
}(with unbuffered logging) contains 2 tables:
}
} eworte 255000 rows and a rowsize of 518
} ewstamm 225000 rows and a rowsize of 312.
}
}Via ISQL or R4GL we have tried the following SQL-Statement
}
} select count(*) from eworte
} where e_asyl > 0
} and e_ord_stamm in
} ( select s_ord from ewstamm
} where s_staat_1 > 0
} and s_hwnw < 3
} )
} into temp tmp_eworte
}
}This statement ends with the error message -458 (Long transaction aborted).
}Why do we get this message???? There are no rows updated or inserted by the
}statement and there is no BEGIN WORK given before. Can anybody explain this
}to me????
}
}Are there any workarounds (we don't want to increase the log buffer)???
Increase the log size (not the log buffer). This is your best solution,
because where one SELECT runs out of space today, smaller SELECT statements
may run out of space next week (or next month), when more than one user is
running them at the same time.
However, there is something else you can do which will improve this one.
That is to use INTO TEMP tmp_eworte WITH NO LOG. This will cut down on the
amount of stuff being written to the log -- it won't eliminate all log
activity, but it will reduce it. As a matter of fact, there is no real
reason for most users to ever use the implicit WITH LOG option to temporary
tables; I'd go so far as to say it was a mistake that when the WITH NO LOG
option was added, it was not made into the default and the alternative WITH
LOG could be allowed explicitly to indicate that for some reason (I can't
think of a good one at the moment), the temporary table should be logged.
Taking another look at your query -- why are you bothering with a temporary
table for a 1 row result? Also, the query could be rewritten as a join, and
it might work faster like this.
SELECT COUNT(*)
FROM eworte O, ewstamm S
WHERE O.e_asyl > 0
AND O.e_ord_stamm = S.s_ord
AND S.s_staat_1 > 0
AND S.s_hwnw < 3
Yours,
Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>