Originally posted by Mark Herschberg:
On my current project, we're running Oracle 9i on a high end desktop server. We're trying to write approximately 1000 rows to the table per second each and every second. It's very slow. Now I know that many operations achieve must higher performance.
BTW, we're using a prepared statment batching a bunch of DMLs (databse writes). We're using container managed transactions from the EJB server.
Our main operation is as follows. There's a single table with a couple hundred rows. We're updating some subset of those rows (also on the order of a few hundred) per second. This is taking hundreds, and occasionally thousands of miliseconds. That's it. One table. No indexing.
--Mark
If I understand you, are you trying to batch 1000 DML's like the following, assuming you have one sql file:
SAVEPOINT INSERT_STUDENTS
INSERT INTO student VALUES
(100, 'Miller', 'Sarah', 'M', '144 Windridge Blvd.', 'Eau Claire', 'WI', '54703', '7155559876', 'SR',
TO_DATE('07/14/1982', 'MM/DD/YYYY'), 8891, 1);
INSERT INTO student VALUES
(101, 'Umato', 'Brian', 'D', '454 St. John''s Place', 'Eau Claire', 'WI', '54702', '7155552345', 'SR',
TO_DATE('08/19/1982', 'MM/DD/YYYY'), 1230, 1);
INSERT INTO student VALUES
(102, 'Black', 'Daniel', ' ', '8921 circle Drive', 'Bloomer', 'WI', '54715', '7155553907', 'JR',
TO_DATE('10/10/1979', 'MM/DD/YYYY'), 1613, 1);
INSERT INTO student VALUES
(103, 'Mobley', 'Amanda', 'J', '1716 Summit St.', 'Eau Claire', 'WI', '54703', '7155556902', 'SO',
TO_DATE('09/24/1981', 'MM/DD/YYYY'), 1841, 2);
SAVEPOINT UPDATE_CLASS
UPDATE student
SET s_class = 'SR'
WHERE s_class = 'JR';
UPDATE student
SET s_class = 'JR'
WHERE s_class = 'SO';
UPDATE student
SET s_class = 'SO'
WHERE s_class = 'FR';
and so on....1,000 different DML commands?
If you can get 1000 DML's in a few hundred miliseconds, less than one second, I'd say that's pretty good, in Oracle.