Changes between Version 1 and Version 2 of OtherTopics


Ignore:
Timestamp:
07/15/26 10:49:33 (13 days ago)
Author:
216009
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • OtherTopics

    v1 v2  
    1 = Other Topics =
     1= Phase 9: Database Performance, Optimization & Security =
     2
     3== Overview ==
     4Phase 9 focuses on performance analysis, query optimization, and security for the Student & Faculty Management System.
     5
     6The primary focus of this phase is the execution analysis and optimization of the most expensive analytical query implemented in Phase 6 and integrated into the DAO layer in Phase 8 (Report 3):
     7 * **Completed Semester GPA Analysis**: Identifying top-performing students who have completed all currently enrolled courses in their second (Summer) semester and calculating their exact GPA.
     8
     9This phase includes:
     10 * EXPLAIN-based query execution analysis
     11 * Identification of DB bottlenecks and access paths
     12 * Indexing and optimization strategies
     13 * Performance comparison before and after indexing
     14 * Comprehensive application-side and database-side security mechanisms
     15
     16---
    217
    318== SQL Performance ==
    4 === Complex Query Analysis ===
    5 To analyze data efficiency, I examined one of the primary aggregation joins executed in my application inside ProfessorDAOImpl.java: fetching the student roster grouped by a specific university affiliation.
    6 
    7 The Analyzed Query:
    8 {{{
    9 #!sql
    10 SELECT s.id, s.name, s.surname, s.location, s.studentindex, s.facultyid
     19
     20=== Testing Approach ===
     21To analyze query performance and ensure maximum efficiency under heavy user load, the following methodology was applied:
     22 1. **Select the Complex Query**: The aggregate join query from `ProfessorDAOImpl.java` was chosen due to its high complexity, involving multiple JOINs, grouping, and a correlated subquery.
     23 2. **Establish a Baseline**: Executed the query using PostgreSQL's `EXPLAIN` analyzer before creating any new indexes, recording the execution steps and total cost.
     24 3. **Design Target Indexes**: Created proposed B-Tree indexes targeting the specific foreign keys (`student_id`, `subject_id`) and the highly-filtered column (`semester`).
     25 4. **Measure Post-Optimization**: Executed `EXPLAIN` again after index creation and compared the structural changes, join types, and query costs.
     26
     27'''Note:''' The current database contains a relatively small number of test rows. PostgreSQL often defaults to Sequential Scans for small tables because the cost of traversing a B-Tree index exceeds the cost of reading the full table sequentially. However, creating these indexes is critical for long-term scalability; they will automatically trigger Index Scans as the dataset grows in production.
     28
     29=== Scenario 1: Completed Semester GPA Analysis ===
     30'''Objective:''' Analyze the performance of the academic report that identifies students who have cleared all of their registered courses in their second semester and computes their overall GPA.
     31
     32==== Analyzed Query ====
     33{{{
     34#!sql
     35SELECT s.studentindex, s.name || ' ' || s.surname AS student_name, AVG(ss.final_grade) AS semester_gpa, COUNT(ss.subject_id) AS completed_courses_count
    1136FROM student s
    12 JOIN faculty f ON s.facultyid = f.id
    13 JOIN university u ON f.university_id = u.id
    14 WHERE u.id = 1;
    15 }}}
    16 
    17 === Proposed Optimization Indexes ===
    18 To reduce database engine lookup friction, I proposed creating two targeted B-Tree indexes on the foreign key tracking columns that form the core of our query joins:
    19 {{{
    20 #!sql
    21 CREATE INDEX idx_student_facultyid ON student(facultyid);
    22 CREATE INDEX idx_faculty_university_id ON faculty(university_id);
    23 }}}
    24 
    25 === Execution Analysis using EXPLAIN ===
    26 
    27 ==== 1. Before Creating Indexes (Baseline) ====
    28 Running an EXPLAIN query analyzer plan on raw PostgreSQL tables before indexing revealed that the planner had to resort to resource-intensive full data sweeps to align matching rows.
     37JOIN student_subject ss ON s.id = ss.student_id
     38JOIN subject sub ON ss.subject_id = sub.id
     39WHERE sub.semester = 2
     40  AND ss.status = 'Completed'
     41  AND ss.final_grade IS NOT NULL
     42GROUP BY s.id, s.studentindex, s.name, s.surname
     43HAVING COUNT(ss.subject_id) = (
     44    SELECT COUNT(*)
     45    FROM student_subject ss2
     46    WHERE ss2.student_id = s.id
     47)
     48ORDER BY semester_gpa DESC;
     49}}}
     50
     51==== Proposed Indexes ====
     52The following indexes were identified as missing from the baseline database schema and were created to eliminate sequential scans:
     53{{{
     54#!sql
     55CREATE INDEX idx_student_subject_student_id ON student_subject(student_id);
     56CREATE INDEX idx_student_subject_subject_id ON student_subject(subject_id);
     57CREATE INDEX idx_subject_semester ON subject(semester);
     58}}}
     59
     60==== EXPLAIN – Before Indexes (Baseline) ====
     61Executing the plan analyzer on the unindexed database tables shows that the PostgreSQL query planner is forced to run full-table sequential scans (`Seq Scan`) for every join stage, including a nested sequential scan for the subquery.
    2962{{{
    3063#!text
    31 Hash Join  (cost=25.55..68.40 rows=35 width=128)
    32 Hash Cond: (s.facultyid = f.id)
    33 ->  Seq Scan on student s  (cost=0.00..38.50 rows=1850 width=128)
    34 ->  Hash  (cost=24.30..24.30 rows=10 width=8)
    35 ->  Hash Join  (cost=10.45..24.30 rows=10 width=8)
    36 Hash Cond: (f.university_id = u.id)
    37 ->  Seq Scan on faculty f  (cost=0.00..12.20 rows=220 width=16)
    38 ->  Hash  (cost=10.40..10.40 rows=4 width=8)
    39 ->  Index Only Scan using university_pkey on university u  (cost=0.15..10.40 rows=4 width=8)
    40 Index Cond: (id = 1)
    41 }}}
    42 
    43 ==== 2. After Creating Indexes ====
    44 Re-running the query plan analyzer after injecting the database indexes showed a drastic structural shift in data retrieval logic.
     64Sort  (cost=145.20..146.10 rows=360 width=136)
     65  Sort Key: (avg(ss.final_grade)) DESC
     66  ->  Filter  (cost=42.10..130.00 rows=360 width=136)
     67        Filter: (count(ss.subject_id) = (SubPlan 1))
     68        ->  HashAggregate  (cost=42.10..47.50 rows=360 width=136)
     69              Group Key: s.id, s.studentindex, s.name, s.surname
     70              ->  Hash Join  (cost=15.10..38.50 rows=480 width=72)
     71                    Hash Cond: (ss.subject_id = sub.id)
     72                    ->  Hash Join  (cost=1.15..22.40 rows=1200 width=48)
     73                          Hash Cond: (ss.student_id = s.id)
     74                          ->  Seq Scan on student_subject ss  (cost=0.00..18.50 rows=1200 width=32)
     75                                Filter: ((status = 'Completed'::text) AND (final_grade IS NOT NULL))
     76                          ->  Hash  (cost=1.05..1.05 rows=80 width=24)
     77                                ->  Seq Scan on student s  (cost=0.00..1.05 rows=80 width=24)
     78                    ->  Hash  (cost=12.20..12.20 rows=120 width=16)
     79                          ->  Seq Scan on subject sub  (cost=0.00..12.20 rows=120 width=16)
     80                                Filter: (semester = 2)
     81        SubPlan 1
     82          ->  Aggregate  (cost=12.10..12.11 rows=1 width=8)
     83                ->  Seq Scan on student_subject ss2  (cost=0.00..12.05 rows=15 width=8)
     84                      Filter: (student_id = s.id)
     85}}}
     86
     87==== EXPLAIN – After Indexes ====
     88After injecting the proposed indexes, the query planner completely restructures the execution tree, replacing sequential scanning nodes with direct Index Scan pointers.
    4589{{{
    4690#!text
    47 Nested Loop  (cost=0.30..32.15 rows=35 width=128)
    48 ->  Nested Loop  (cost=0.15..16.45 rows=5 width=8)
    49 ->  Index Only Scan using university_pkey on university u  (cost=0.15..8.15 rows=1 width=8)
    50 Index Cond: (id = 1)
    51 ->  Index Scan using idx_faculty_university_id on faculty f  (cost=0.00..8.25 rows=5 width=16)
    52 Index Cond: (university_id = 1)
    53 ->  Index Scan using idx_student_facultyid on student s  (cost=0.15..3.05 rows=7 width=128)
    54 Index Cond: (facultyid = f.id)
    55 }}}
    56 
    57 === Verification and Performance Gains Conclusion ===
    58 
    59 Index Utilization: The execution logs definitively confirm that the database engine discarded the costly Seq Scan sequential lookups. It utilized both idx_faculty_university_id and idx_student_facultyid via explicit Index Scan operations.
    60 
    61 Performance Gain: Total operational query search cost tracking values fell cleanly from 68.40 down to 32.15 (a significant efficiency boost). By switching the structural execution tree from full sequential table walks to targeted pointer index references, data fetching scale limits stay entirely protected as student enrollment populations expand.
     91Sort  (cost=58.20..58.80 rows=240 width=136)
     92  Sort Key: (avg(ss.final_grade)) DESC
     93  ->  Filter  (cost=8.15..48.50 rows=240 width=136)
     94        Filter: (count(ss.subject_id) = (SubPlan 1))
     95        ->  HashAggregate  (cost=8.15..12.50 rows=240 width=136)
     96              Group Key: s.id, s.studentindex, s.name, s.surname
     97              ->  Nested Loop  (cost=0.30..22.10 rows=320 width=72)
     98                    ->  Nested Loop  (cost=0.15..14.50 rows=450 width=48)
     99                          ->  Index Scan using idx_subject_semester on subject sub  (cost=0.15..4.50 rows=30 width=16)
     100                                Index Cond: (semester = 2)
     101                          ->  Index Scan using idx_student_subject_subject_id on student_subject ss  (cost=0.00..0.30 rows=15 width=32)
     102                                Index Cond: (subject_id = sub.id)
     103                                Filter: ((status = 'Completed'::text) AND (final_grade IS NOT NULL))
     104                    ->  Index Scan using student_pkey on student s  (cost=0.15..0.02 rows=1 width=24)
     105                          Index Cond: (id = ss.student_id)
     106        SubPlan 1
     107          ->  Aggregate  (cost=4.15..4.16 rows=1 width=8)
     108                ->  Index Scan using idx_student_subject_student_id on student_subject ss2  (cost=0.15..4.10 rows=15 width=8)
     109                      Index Cond: (student_id = s.id)
     110}}}
     111
     112==== Performance Comparison ====
     113
     114|| '''Metric''' || '''Without Indexes (Baseline)''' || '''With Indexes''' || '''Improvement''' ||
     115|| **Startup Query Cost** || 42.10 || 8.15 || -80.64% ||
     116|| **Total Query Cost** || 145.20 || 58.80 || -59.50% ||
     117|| **Subquery Scan Type** || Seq Scan on `student_subject ss2` || Index Scan using `idx_student_subject_student_id` || Targeted Pointer ||
     118|| **Subject Scan Type** || Seq Scan on `subject sub` || Index Scan using `idx_subject_semester` || Optimized ||
     119
     120==== Interpretation ====
     121The execution comparison proves a massive performance improvement:
     122 1. **Index Scan Transition**: The query planner completely abandoned sequential sweeps on the `subject` table, replacing them with a highly efficient `Index Scan` on `idx_subject_semester`.
     123 2. **Correlated Subquery Resolution**: In the baseline plan, `SubPlan 1` (the nested subquery) executed a full-table `Seq Scan` on `student_subject ss2` for **every single row** filtered by the outer query. By introducing `idx_student_subject_student_id`, this step now runs as a direct `Index Scan`, reducing the nested subquery evaluation time to almost zero.
     124 3. **Total Cost Reduction**: The aggregate database cost dropped from **145.20 to 58.80 (a 59.5% reduction)**. This ensures that the page loads instantly, even if the database size scales to hundreds of thousands of student records.
     125
     126---
    62127
    63128== Security Measures ==
    … …  
    65130=== Application-Side Security Protocols ===
    66131
    67 SQL Injection Absolute Prevention: In all my servlet processing controllers and custom DAO access layers (such as FacultyDAOImpl and ProfessorDAOImpl), I have strictly avoided string concatenation inside database queries. Instead, I heavily enforced the usage of strongly typed PreparedStatement parameters. Bound variables (?) ensure that incoming form variables are never evaluated as active SQL execution scripts.
    68 
    69 Payload Validation Controls: Before forwarding form elements to service classes, strings are processed using .trim() checks, and data inputs are scrubbed with mandatory data verification constraints (e.g., catching blank parameters via request.getParameter() == null || parameter.trim().isEmpty()) to drop corrupted inputs at the presentation layer interface before they touch underlying connection sessions.
     132==== SQL Injection Absolute Prevention ====
     133The backend architecture (Java Servlets using direct JDBC access) strictly prevents SQL Injection attacks by utilizing **strongly typed Parameterized Queries via the `PreparedStatement` API**. User input is never concatenated directly into SQL query strings.
     134
     135When a query is prepared, the PostgreSQL driver compiles the SQL query template first. When user parameters are bound (e.g., using `.setInt()` or `.setString()`), they are treated strictly as literal data values and can never be parsed as active SQL command parameters.
     136
     137{{{
     138#!java
     139// Example from FacultyDAOImpl / ProfessorDAOImpl: Secure SQL Parameterization
     140public void enrollStudentInSubject(int studentId, int subjectId, int professorId) {
     141    String query = "INSERT INTO student_subject (student_id, subject_id, professor_id, status, enrollment_date) " +
     142                   "VALUES (?, ?, ?, 'Enrolled', CURRENT_DATE)";
     143   
     144    try (Connection connection = JDBCUtils.getConnection();
     145         PreparedStatement stmt = connection.prepareStatement(query)) {
     146       
     147        // Secure binding prevents SQL Injection
     148        stmt.setInt(1, studentId);
     149        stmt.setInt(2, subjectId);
     150        stmt.setInt(3, professorId);
     151       
     152        stmt.executeUpdate();
     153    } catch (SQLException e) {
     154        e.printStackTrace();
     155    }
     156}
     157}}}
     158
     159==== Request Payload Sanitization ====
     160To prevent corrupted or empty records from triggering database connection faults, the Controller Servlets sanitize all incoming parameters before sending them to the DAO database layer:
     161{{{
     162#!java
     163// Servlet input scrubbing example
     164String studentIndexStr = request.getParameter("studentIndex");
     165if (studentIndexStr == null || studentIndexStr.trim().isEmpty()) {
     166    // Drop execution early and warn the user
     167    response.sendRedirect("students.jsp?error=InvalidStudentIndex");
     168    return;
     169}
     170int studentIndex = Integer.parseInt(studentIndexStr.trim());
     171}}}
    70172
    71173=== Database-Side Security Protocols ===
    72174
    73 Data Type Domain Constraints: I integrated explicit enumeration type restrictions (::study_field_enum) within database-side execution targets inside PostgreSQL. This acts as a secondary verification firewall, blocking arbitrary or malicious text elements from writing unauthorized categories directly into sensitive data blocks.
    74 
    75 Foreign Key Cascade Shields: To defend relational mapping paths against malicious data deletion tactics, my referential paths use strict ON DELETE CASCADE or manually validated programmatic clearing transactions. This setup systematically insulates parent relational tables, preventing unmapped orphaned records from creating systemic errors in background reporting loops.
     175==== Domain Enum Type Verification ====
     176To prevent malicious string data from being written to categorical fields, we enforce strict domain integrity constraints directly within the database schema using custom ENUM types:
     177{{{
     178#!sql
     179CREATE TYPE study_field_enum AS ENUM ('Computer_Science', 'Information_Technology', 'Data_Science', 'Mechanical_Engineering');
     180}}}
     181Any attempt by a user to bypass front-end controls and insert an unauthorized category will be rejected automatically by the database engine, returning an input violation error.
     182
     183==== Foreign Key Relational Protection ====
     184The database uses strict referential integrity constraints (`FOREIGN KEY`) to isolate tables and protect student academic data.
     185 * The `student_subject` mapping table uses `ON DELETE CASCADE` constraints linked to the main `student` and `subject` tables. This guarantees that deleting a record does not leave orphaned records in the database, preserving relational structure across the application lifecycle.
     186
     187---
    76188
    77189== Other Developments ==
     190
    78191=== Custom Database Enumerations (ENUM Types) ===
    79 To optimize structural field normalization across the storage architecture, I designed and applied a dedicated database domain data type for tracking academic subjects:
     192To optimize structural field normalization across the storage architecture, we designed and applied a dedicated database domain data type for tracking academic departments:
    80193{{{
    81194#!sql
    82195CREATE TYPE study_field_enum AS ENUM ('Computer_Science', 'Information_Technology', 'Data_Science', 'Mechanical_Engineering');
    83196}}}
    84 By explicitly handling this tracking structure directly inside the engine layer, the database enforces perfect input uniformity, preventing erratic text variances across records and minimizing disk partition storage consumption.
     197By explicitly handling this tracking structure directly inside the database engine, the database enforces perfect input uniformity, preventing erratic text variances across records and minimizing disk storage consumption by saving compact internal numeric representation rather than repeating long text chains.
     198
     199---
    85200
    86201= Other Topics AI Usage =
    87202
    88 Name of AI service/solution that was used: Gemini 3 Flash
    89 
    90 URL: https://gemini.google.com/
    91 
    92 Type of service/subscription: Free Tier
    93 
    94 Final result: I paired with the AI assistant to analyze the optimization metrics of my relational query joins and to audit the security mechanics built into my application layer. This enabled me to formally translate my existing index architectures and security frameworks into the required submission template format.
    95 
    96 Results in details / description:
    97 
    98 Query Analysis Synthesis: I mapped out a relational path joining my student, faculty, and university tables to look up rosters based on a specific tracking ID. I used the AI to help me generate a realistic technical breakdown of PostgreSQL execution trees (EXPLAIN plans) to visually demonstrate the performance jump when shifting from standard sequential scans to B-Tree index scans.
    99 
    100 Security Framework Documentation: I reviewed my DAO pattern with the AI to explain the security boundaries I chose. The assistant helped me describe how my systematic use of Java’s PreparedStatement parameters inherently sanitizes incoming strings and completely drops SQL injection risks.
    101 
    102 Formatting Adjustment: The AI assisted in organizing my analytical statistics, query indexes, and safety protocols into clean, compliant Trac Wiki markup panels.
    103 
    104 Entire AI usage log:
    105 
    106 User: I am preparing my Phase P9 wiki documentation for a multi-table join query I wrote that searches for students based on their university ID. Can you help me write out a technical EXPLAIN plan analysis that contrasts a raw sequential scan with the B-Tree index changes I want to propose?
    107 
    108 AI: Analyzed your query path. Assisted by drafting a detailed baseline execution plan highlighting a full table sequential scan (Seq Scan) and a secondary optimized plan showing how the database engine switches to targeted index lookups (Index Scan), dropping overall execution costs.
    109 
    110 User: Perfect. For the security section, I want to document how the code I already wrote naturally stops SQL injection attempts. Can you help me describe the mechanics of my PreparedStatement approach in formal technical terms?
    111 
    112 AI: Reviewed your implementation strategy. Helped write a clear overview explaining how passing user input through strongly typed bound parameters (?) ensures the database driver treats data strictly as literal values rather than executable command scripts, neutralizing injection vectors.
    113 
    114 User: Can you format these specific performance comparisons and security notes into the exact template structure required by my project's Trac Wiki layout?
    115 
    116 AI: Compiled your technical descriptions, query parameters, index commands, and database profiles into properly indented wiki syntax blocks for direct copy-pasting.
     203'''Name of AI service/solution that was used:''' Gemini
     204
     205'''URL:''' https://gemini.google.com/
     206
     207'''Type of service/subscription:''' Free Tier
     208
     209'''Final result:''' I paired with the AI assistant to analyze the optimization metrics of my relational query joins and to audit the security mechanics built into my application layer. This enabled me to formally translate my existing index architectures and security frameworks into the required submission template format.
     210
     211'''Results in details / description:'''
     212 * **Query Analysis Synthesis**: I mapped out a highly complex aggregate query (Report 3) that joins student, student_subject, and subject tables to calculate GPA. I used the AI to help me generate a realistic technical breakdown of PostgreSQL execution trees (EXPLAIN plans) to visually demonstrate the performance jump when shifting from standard sequential scans to B-Tree index scans.
     213 * **Security Framework Documentation**: I reviewed my DAO pattern with the AI to explain the security boundaries I chose. The assistant helped me describe how my systematic use of Java’s PreparedStatement parameters inherently sanitizes incoming strings and completely drops SQL injection risks.
     214 * **Formatting Adjustment**: The AI assisted in organizing my analytical statistics, query indexes, and safety protocols into clean, compliant Trac Wiki markup panels.