• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

What makes database access slow? (general question)

 
Mark Herschberg
Sheriff
Posts: 6037
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Databases are my weakest area. I know how to make use of them, but not really how to use them optimally. 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. What could be limiting us?
Could it IO bound? That we are not able to connect to the server enough times?
Is it database CPU bound? I would find this hard to believe.
Is it throughput bound? Is there's simply a certain amount of performance overhead a roundtrip operation must incur and we should not count on the operation completing any faster then that.
We can buy a faster server is necessary. If so, what specs are important for a database server? or rather, how do CPU, cache, memory, and network card effect typically database 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.
--Mark
 
Victor Pendleton
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What type of statements are you processing? If you are doing bulk loading you may consider dropping the indexes and rebuilding after the load has completed.
 
Les Hayden
Greenhorn
Posts: 28
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Mark,
I'm new to the JSP side of things, but as Vic was pointing out, indexes significantly slow database inserts and updates. If you have a lot of indexes declared on your tables, that would cause a significant slowdown. Droping any indexes will result in an increase in your write performance.
Of course if you are also doing queries against those tables, droping the indexes could slow down the queries.
Les
 
Jamie Robertson
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
indexes ( as stated above ) and views have to be updated with every insert. This could cause writing overhead, but you lose performance in select statements if don't have them!
but for more technical stuff, see Oracle9i Database Performance Tuning Guide and Reference. Covers every aspect of tuning and performance planning.
Some parts of Database Performance Planning ( Oracle 9i ) were useful:
From page 29 --> "Use this simple estimation guide for the cost of index maintenance: Each index
maintained by an INSERT, DELETE, or UPDATE of the indexed keys requires about
three times as much resource as the actual DML operation on the table. What this
means is that if you INSERT into a table with three indexes, then it will be
approximately 10 times slower than an INSERT into a table with no indexes. For
DML, and particularly for INSERT-heavy applications, the index design should be
seriously reviewed, which might require a compromise between the query and
INSERT performance."
Jamie
 
Mark Herschberg
Sheriff
Posts: 6037
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
We're not indexing.
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
 
HS Thomas
Ranch Hand
Posts: 3404
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

We're updating some subset of those rows (also on the order of a few hundred) per second.
What rules and how do you determine the subset?
Sort and scan ?
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.
It doesn't seem too big a table to require indexes.
Depends on what your prepared statements are actually doing.
BTW, is this a temporary table ? Tables normally have a habit of growing, never mind trying to relate it to anything else.
So normally you'd require at least one index.
regards
[ July 10, 2003: Message edited by: HS Thomas ]
 
leo donahue
Ranch Hand
Posts: 327
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Mark Herschberg
Sheriff
Posts: 6037
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Um, here's how it works...
We keep track of user's portfolios (which sharesand how much they hold). Every second we received a bunch of orders, and cross them. Basically, we look at what the orders are (e.g. user A buys stock X), and we grab the row of the database for user A's holdings for stock X. We change the quantity, and write it back to the datase.
If we do all the processing except for the DB read and write, we can increase performanince by 1-2 orders of magnitude. Even if we just turn off writing, we do well enough.
The database rows are accessed using a data access object making a single JDBC call (once for all read information, once for the write information, as opposed to one call per row). The data access object is called from a session facade using bean managed transactions.
--Mark
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic