Correlation
1. Queries that are affected only by database server speed:
◦ CPU heavy query
◦ Insert CLOB 500 times from stored procedure
◦ Read CLOBs from stored procedure
◦ Insert CLOB 500 times from stored procedure into table with primary key
◦ Write 'varchar(255)' 500 times
◦ Read 'varchar(255)' 500 times
◦ Write 'integer' 500 times
◦ Write 'datetime' 500 times
◦ Read 'datetime' 500 times
◦ Write 'integer' 500 times with primary key
◦ Write 'integer with index' 1000 times
2. Queries that are affected only by application server to database network speed.
◦ Get Connection creation in Java
◦ New Connection creation in Java
◦ Insert one row, count rows table then clear the table
3. Queries that are affected by database server speed and by application server to database network speed :
◦ Insert CLOB 500 times
◦ Insert CLOB 500 times into table with primary key
◦ Read CLOB
◦ Insert CLOB 500 times from stored procedure {Measured execution inside the procedure}
◦ Latency of Insert CLOB 500 times from stored procedure {Measured execution inside the procedure}
◦ Insert BLOB 500 times
◦ Read BLOB.
|
Queries that are affected only by database server speed
|
Queries that are affected only by application server to database network speed
|
Queries that are affected by database server speedand by application server to database network speed
|
What is the problem?
|
|
|---|---|---|---|---|
|
Slow?
|
X
|
X
|
Database server speed
|
|
|
Slow?
|
X
|
X
|
Application server to database network speed
|
|
|
Slow?
|
X
|
X
|
X
|
Database server speed and Application server to database network speed
|
 |
• IF
◦ Insert CLOB 500 times
◦ Insert CLOB 500 times into table with primary key
◦ Read CLOB
◦ Insert CLOB 500 times from stored procedure {Measured execution inside the procedure}
◦ Latency of Insert CLOB 500 times from stored procedure {Measured execution inside the procedure}
◦ Insert BLOB 500 times
◦ Read BLOB queries are slow, but
◦ CPU heavy query
◦ Insert CLOB 500 times from stored procedure
◦ Read CLOBs from stored procedure
◦ Insert CLOB 500 times from stored procedure into table with primary key
◦ Write 'varchar(255)' 500 times
◦ Read 'varchar(255)' 500 times
◦ Write 'integer' 500 times
◦ Write 'datetime' 500 times
◦ Read 'datetime' 500 times
◦ Write 'integer' 500 times with primary key
◦ Write 'integer with index' 1000 times queries are not slow, the problem will be with the application server to database network speed.
|
|
|
• IF
◦ Insert CLOB 500 times
◦ Insert CLOB 500 times into table with primary key
◦ Read CLOB
◦ Insert CLOB 500 times from stored procedure {Measured execution inside the procedure}
◦ Latency of Insert CLOB 500 times from stored procedure {Measured execution inside the procedure}
◦ Insert BLOB 500 times
◦ Read BLOB queries are slow, but
◦ CPU heavy query
◦ Insert CLOB 500 times from stored procedure
◦ Read CLOBs from stored procedure
◦ Insert CLOB 500 times from stored procedure into table with primary key
◦ Write 'varchar(255)' 500 times
◦ Read 'varchar(255)' 500 times
◦ Write 'integer' 500 times
◦ Write 'datetime' 500 times
◦ Read 'datetime' 500 times
◦ Write 'integer' 500 times with primary key
◦ Write 'integer with index' 1000 times queries are not slow, the problem will be with the database server speed.
|
• If every query is slow, the problem will be with the application server to database network speed, and with the database server speed.