Database Administration

Application
performance

Faster apps.
Better use of data.

STUDENT PORTAL●

Student records

Ana Reyes91

Marco Santos88

Bea Cruz94

Less waitingOpen records smoothly

Better experienceFind and save data

Less waiting

Application performance means completing tasks quickly while using data efficiently.

Class list · before3 seconds

ID	Student	Score
ST-001	Ana Reyes	91
ST-002	Marco Santos	88
ST-003	Bea Cruz	94

Class list · after1 second

ID	Student	Score
ST-001	Ana Reyes	91
ST-002	Marco Santos	88
ST-003	Bea Cruz	94

Example: a teacher opens the same class list with less waiting.
Same records · illustrative timings · automatic loop

One click. A data journey.

A data request is an application asking the database for information.

StudentClicks Grades

ApplicationChecks access

DatabaseReturns records

ScreenShows grades

Ana Reyes requests her score: 91.

Try the student portal

Try a familiar task: find a student and change a score.

Class recordsBSIT 2A · sample data

ID	Student	Score
ST-001	Ana Reyes	91
ST-002	Marco Santos	88
ST-003	Bea Cruz	94
ST-004	Luis Garcia	86
ST-005	Mia Ramos	90

1–5 of 30

Sample application · saving is simulated · changes reset on reload

Get related data together

Grouping requests means getting related data in fewer trips.

One at a time31 requests

LISTST-001ST-002ST-003ST-004ST-005ST-006ST-007ST-008ST-009ST-010ST-011ST-012ST-013ST-014ST-015ST-016ST-017ST-018ST-019ST-020ST-021ST-022ST-023ST-024ST-025ST-026ST-027ST-028ST-029ST-030

As a group2 requests

Class list
30 student IDs

Group details
30 names + scores

Keep each group manageable.

Ana, Marco, Bea… the same 30 student records.

Example: request the class details together instead of asking once for every student.

Only the data you need

Filtering means selecting the records that match a need.

Too much data

10,000

All students

The teacher only needs one class.

The needed data

30

BSIT 2A only

Less data to send and process.

10,000Rows sent to the app

Same required class list · illustrative school records

Related records work together

A join connects matching records from different tables.

Separate records

Student ID	Student
ST-001	Ana Reyes
ST-002	Marco Santos

Enrollment records

Student ID	Course
ST-001	Course A
ST-002	Course A

A useful class list

Student	Course
Ana Reyes	Course A
Marco Santos	Course A

Match the student ID

The database combines the related information.

Good matching rules help avoid unnecessary work and incorrect results.

One page at a time

Pagination means loading records in small groups.

Like turning a page

5 records

Read. Then continue.

The next group loads when the user asks for it.

BSIT 2A · sample records1–5 of 30

ID	Student	Score
ST-001	Ana Reyes	91
ST-002	Marco Santos	88
ST-003	Bea Cruz	94
ST-004	Luis Garcia	86
ST-005	Mia Ramos	90

Example: show five students first instead of loading the whole school.

Reuse data. Keep it current.

Caching keeps a temporary copy of frequently used data.

Reuse unchanged course information

App

CacheTemporary copy

Database

First visit0.9 s

Refresh after a change

Current schedule

MSIT 206 classroom

A101

The copy matches the database.

Read the course catalog from the database.

Example: reuse the course list, but refresh the copy when a classroom changes.

How the database chooses

The optimizer is the part of the database that chooses how to get the data.

?

RequestFind one student.

≈

EstimateHow much data matches?

↔

CompareWhich way needs less work?

✓

ChooseUse an efficient path.

Like choosing a route before starting a trip.

Work includes processing data and reading it from memory or storage.

Good estimates need statistics

Database statistics are summaries that help predict how much data a request needs.

Few rows match

30 / 10,000

BSIT 2A

An index may help find this small group.

Most rows match

9,000 / 10,000

Active students

Reading the whole table may cost less.

The DBA keeps these summaries up to date as the data changes.

Like checking how many students are in a class before choosing a classroom · sample counts

Two paths to the same student

An index is a lookup guide that helps locate matching records.

Table scan

ST-001ST-002ST-003ST-004ST-005ST-006ST-007ST-008ST-009ST-010ST-011ST-012ST-013ST-014ST-015ST-016ST-017ST-018ST-019ST-020

Like checking names throughout a class record.

Index access

ST-001ST-002ST-003ST-004ST-005ST-006ST-007ST-008ST-009ST-010ST-011ST-012ST-013ST-014ST-015ST-016ST-017ST-018ST-019ST-020

Like using a book’s index to find a topic.

Find ST-017.

Indexes can speed up searches, but take space and need updates when data changes.

A clear request saves work

Query tuning means improving a data request so it needs less work.

Say exactly what is needed

“Show the names
in BSIT 2A.”

The database can filter the class before sending the results.

One small SQL example

SELECT student_name
FROM students
WHERE section = 'BSIT 2A';

In plain English: get the names in this class.

The goal is less unnecessary work and the same correct result.

SQL is the language an application uses to ask a relational database for data.

The plan shows the steps

An execution plan describes how the database will carry out a request.

Example: open a class list

1Find the matching students.

2Combine any needed records.

3Put the names in order.

What the DBA checks

?Is it reading too much data?

?Is any step repeated too often?

?Which step uses the most work?

Like checking each step of a delivery to find the delay.

A plan helps explain the work. Measurements show how long it actually takes.

A short save reduces waiting

A transaction is a group of database changes that succeeds or fails together.

Two teachers. One record.

ATeacher A starts saving a score.

BTeacher B waits to edit that record.

✓The save finishes. The wait ends.

Ana Reyes
ST-001 · example score update91

Ready to save

The teacher changes Ana’s score from 91 to 95.

A lock temporarily protects a record from conflicting changes.

Finish transactions promptly · if the save fails, cancel its changes

Reuse database connections

Connection pooling means reusing a limited set of database connections.

Students requesting records

5

AnaMarcoBeaLuisMiaNoelEllaPaoloIrisCarloNinaEnzoJadeRicoLeaIvanSofiaTheoChloeRyanAvaJoshLaraMiguelFaithSeanKateAdrianZoeDaniel

A few students open their records.

Like shared service counters: use one, finish, then let the next request use it.

Find what causes the delay

A bottleneck is the part of the process that limits overall performance.

Task	Requests	Average time	Total time
Class list	1,000	20 ms	20,000 ms
Term report	5	800 ms	4,000 ms

Class list

20 s

Report

4 s

A small delay adds up when the same task happens many times.

Example: many class-list requests create more total work than a few reports · sample data

The DBA and developer work together

They find the cause of a delay and improve how the application uses data.

Database administrator

1Find slow database requests.

2Review plans, indexes, and waits.

3Check the effect of each change.

Application developer

1Remove repeated data requests.

2Ask for only the needed records.

3Keep saving and connections efficient.

Example: improve the class list together, then test it again.

Test the same task twice

Performance testing checks speed and correctness under a known workload.

Before

3 s

After

1 s

Response time: how long one task takes. Throughput: tasks completed per second.
Compare the same data and user load. Keep the results correct. · Sample timings