01TypicalDatabasePerformanceProblems

Typical Database Performance Problems

Outline

What can go wrong ?

Typical Database Performance Problems

Outline

What can go wrong ?

Problems related to implementation of user applications

Inefficient SQL


CUSTOMER(cnumber, name, address) PK = (cnumber)
ORDERS(onumber, cnumber, total) PK =(onumber), FK = (cnumber)

Find the total number of orders submitted by a customer whose number is 007


SELECT COUNT(*)
FROM ORDERS JOIN CUSTOMER
            ON ORDERS.cnumber = CUSTOMER.cnumber
WHERE CUSTOMER.cnumber = 007;


SELECT COUNT(*)
FROM ORDERS
WHERE ORDERS.cnumber = 007;

Problems related to implementation of user applications

Domination of procedural code over SQL

Find the names of customers who submitted orders worth more than 1000


for customer in CUSTOMER do
 for order in ORDERS do
  if customer.cnumber = order.cnumber and order.total > 1000 then
   display customer.name


SELECT name
FROM ORDERS JOIN CUSTOMER
            ON ORDERS.cnumber = CUSTOMER.cnumber
WHERE total > 1000;

Problems related to implementation of user applications

Domination of SQL over procedural code

Find the orders with the second highest value of total

SELECT *
FROM ORDERS
WHERE ORDERS.total = ( SELECT MAX(total)
                       FROM ORDERS
                       WHERE total <> ( SELECT MAX(total)
                                        FROM ORDERS ) );

A more efficient solution reads a relational table ORDERS row by row and keep the current largest value of total and the current second largest value of total, update both values when it is necessary

Typical Database Performance Problems

Outline

What can go wrong ?

Problems related to configuration of database server

Incorrect configuration of DBMS, for example too small size of shared memory area, too small size of database cache, incorrect allocation of relational tables to database caches, etc
Incorrect configuration of persistent storage, for example, log files located on the same persistent storage device as database files, frequently used relational tables located on the same persistent and slow storage device, etc
Not enough database processes, e.g. one writer process in a heavy insert/update/delete database environment
Too small data block for large and frequently read relational tables
To small data transmission buffer

Typical Database Performance Problems

Outline

What can go wrong ?

Problems related to transaction processing

Incorrect implementation of database transaction

Transaction T_i


while not eot(ORDERS)
 read(ORDERS.total);
 write(ORDERS.total+1);
end
COMMIT;

Transaction T_k


read(ORDERS.total, onumber = 777)
write(ORDERS.total-20);
COMMIT;

Transaction T_i blocks transaction T_k because COMMIT of T_i is performed after all updates are committed

Problems related to transaction processing

Incorrect isolation level set for a database transaction


SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;


UPDATE ORDERS
SET TOTAL = TOTAL + 0.1 * TOTAL;
COMMIT;


SET TRANSACTION ISOLATION LEVEL READ COMMITTED;


UPDATE ORDERS
SET TOTAL = TOTAL + 0.1 * TOTAL;
COMMIT;

Typical Database Performance Problems

Outline

What can go wrong ?

Problems related to database design

Logical database design not consistent with the database applications


R(driver, truck, capacity)
driver  → truck, truck → capacity


D(driver, truck)
T(truck, capacity)

Find all drivers who use trucks that have a capacity greater than 100


SELECT driver
FROM D JOIN T
       ON D.truck = T.truck
WHERE capacity > 100;

Problems related to database design

Logical database design not consistent with the database applications


R(driver, truck, capacity)
driver  → truck, truck → capacity

D(driver, truck)
C(driver, capacity)

Find all drivers who use trucks that have a capacity greater than 100

Problems related to database design

Database physical design not consistent with database applications
Find all orders whose total value equal to 1000
Missing index on ORDERS(total) forces a full scan of a relational table ORDERS

Problems related to database design

Overnormalization


STREETS(street, zcode)    CITIES(city, zcode)
PK =(street, zcode)       PK = (zcode)
BCNF                      BCNF

Find all streets in Sydney


SELECT street
FROM STREETS JOIN CITIES
             ON STREETS.zcode = CITIES.zcode
WHERE city = 'Sydney';

Problems related to database design

Denormalization


LOCATION(city,street,zcode)
PK =(city,street), CK = (zcode,street)
city,street  → zcode zcode  → city

Find all streets in Sydney


SELECT street
FROM LOCATION
WHERE city = 'Sydney';

Problems related to database design

Using surrogate keys


BUILDING(bID, campus, bnumber, type) PK = (bID), CK = (campus, bnumber)
ROOM(bID, rnumber, area) PK =(bID, rnumber), FK = (bID)

Find all names of campuses and numbers of buildings that have at least one room whose area is greater than 100

Problems related to database design

Using surrogate keys


BUILDING(campus, bnumber, type) PK = (campus, bnumber)
ROOM(campus, bnumber, rnumber, area) PK =(campus, bnumber, rnumber),
                                     FK = (campus, bnumber)