Transcript Database performance cookbook - CSCI 6442

DATABASE
PERFORMANCE
COOKBOOK
SCENARIO




You’ve completed CSCI 6442 (congratulations!)
You have an MS from GWU (congratulations again!)
You’re working on a project using a DBMS (congrats)
And they think of you as a database guru (congrats?)
 And now—a programmer says “I’m having performance
problems.”
 The boss, of course, tells her to come see you!
 How to proceed?
WHERE TO LOOK










How loaded is the server?
Which DBMS is being used?
How does the application use SQL?
Identify the queries that are slow
Review the use of SQL—can SQL do more?
Look at the plan for each one
First—check for existence of indexes and their use in the plan
Then—general review of the query and the plan
Recommend changes to indexes and query as appropriate
Get performance test results right away
SERVER LOAD
 The problem may be at the server





Find that out first
Server may be overloaded
Server may not be administered effectively
Server may not have enough capacity
Sometimes test server does not have production capacity
DBMS CHOICE
 If either the IBM, Sybase or Oracle family is being used, you
can work with it
 If MySQL is used, check that NODB is being used. Even then,
you have limited options for optimization
APPLICATION USE OF SQL
 Applications may use SQL for put and get, do all data
manipulation in the application
 They may issue many SQL statements to get a result that you
can obtain with a single statement
 Often programmers will just copy another programmer’s SQL
to get access to the database, won’t understand a thing
 Huge performance gains are available from fixing these
problems
IDENTIFY PROBLEM QUERIES
 Usually some SQL statements stand out as performance
problems
 If they’re all slow, the problem may be in the database server
or the application server
WHERE TO LOOK
 Study the use of SQL in each problem query
 Can more function be added to the database and removed
from the application? This can improve performance
 Look at indexes and their use in the plan
 Test alternative formulations of the statement for
performance
 Recommend changes in SQL statement, indexes
WHERE TO LOOK
 Carry out and document results of performance tests on your
improvements right away
 You want a quantitative record of what you accomplished
TAKE THE CREDIT
 Share the results of the performance improvement, with due
modesty
 You are a hero, take the credit