Part 2: Optimizing the Database with Stored Procedures for a Film/Series Review Application

Introduction to Part 2

In Part 1, we laid the groundwork by creating the core tables and establishing relationships in MSSQL. Now, in Part 2, we’ll dive into optimizing common operations using Stored Procedures. Stored Procedures allow us to encapsulate frequently used queries, ensuring efficient data access, improved performance, and enhanced security.

In this section, we’ll cover:

• Creating Stored Procedures for key database operations
• How these procedures improve performance and maintainability
• Preparing the database for integration with our C# .NET Core backend

Why Use Stored Procedures?

Stored Procedures bring several advantages to the table:

• Performance: Procedures are precompiled and cached, leading to faster execution times, especially for complex queries.
• Security: By abstracting the SQL code, we reduce exposure to SQL injection attacks and protect the integrity of the database.
• Reusability: We avoid redundancy in our backend code by centralizing SQL logic, making the application easier to maintain and extend.

In this application, we’ll use Stored Procedures for common tasks like retrieving reviews for a movie, adding new reviews, fetching top-rated content, and searching by tags.

Stored Procedures for Common Operations

1. Get Reviews for a Movie

This procedure retrieves all reviews for a specific movie, ordered by the latest review date, enabling users to view the most recent feedback first.

CREATE PROCEDURE GetMovieReviews
    @movie_series_id INT
AS
BEGIN
    SELECT r.review_id, r.user_id, u.username, r.review_text, r.review_date
    FROM Reviews r
    JOIN Users u ON r.user_id = u.user_id
    WHERE r.movie_series_id = @movie_series_id
    ORDER BY r.review_date DESC;
END;

Here:

• The @movie_series_id parameter allows us to target reviews for a specific movie.
• JOIN with the Users table helps retrieve user details, creating a richer review display.

2. Add a New Review

Inserting a new review is a common action, and this stored procedure ensures consistency and reduces backend code complexity.

CREATE PROCEDURE AddReview
    @user_id INT,
    @movie_series_id INT,
    @review_text TEXT
AS
BEGIN
    INSERT INTO Reviews (user_id, movie_series_id, review_text, review_date)
    VALUES (@user_id, @movie_series_id, @review_text, GETDATE());
END;

This procedure inserts a new review with:

• @user_id and @movie_series_id linking the review to a specific user and movie/series.
• GETDATE() automatically sets the review date.

3. Fetch Top-Rated Movies/Series

This procedure retrieves the top-rated movies or series, sorted by their average rating, giving users a quick way to find popular content.

CREATE PROCEDURE GetTopRatedMovies
    @top_count INT
AS
BEGIN
    SELECT ms.movie_series_id, ms.title, AVG(r.rating) AS avg_rating
    FROM MoviesSeries ms
    JOIN Ratings r ON ms.movie_series_id = r.movie_series_id
    GROUP BY ms.movie_series_id, ms.title
    ORDER BY avg_rating DESC
    OFFSET 0 ROWS FETCH NEXT @top_count ROWS ONLY;
END;

This procedure uses:

• AVG(r.rating) to calculate average ratings.
• The @top_count parameter limits the results for efficient querying.

4. Get Movies by Tag

Filtering by tags enhances discoverability, allowing users to view movies associated with specific themes or genres.

CREATE PROCEDURE GetMoviesByTag
    @tag_name VARCHAR(50)
AS
BEGIN
    SELECT ms.movie_series_id, ms.title, ms.genre, ms.release_date
    FROM MoviesSeries ms
    JOIN MovieSeriesTags mst ON ms.movie_series_id = mst.movie_series_id
    JOIN Tags t ON mst.tag_id = t.tag_id
    WHERE t.tag_name = @tag_name;
END;

This procedure utilizes:

• A join on the MovieSeriesTags and Tags tables to retrieve all movies/series associated with a specific tag.

Testing the Stored Procedures

Once the stored procedures are created, we can execute them with sample data to ensure they perform as expected. For example:

-- Test fetching reviews for a specific movie
EXEC GetMovieReviews @movie_series_id = 1;
-- Test adding a review
EXEC AddReview @user_id = 1, @movie_series_id = 1, @review_text = 'Amazing movie!';
-- Test retrieving top-rated movies
EXEC GetTopRatedMovies @top_count = 5;
-- Test filtering movies by tag
EXEC GetMoviesByTag @tag_name = 'Action';

By testing each stored procedure, we validate both functionality and performance, ensuring our SQL commands work efficiently and without error.

Final Remarks

With these stored procedures in place, we’ve optimized data retrieval and manipulation, making our application more secure and maintainable. In Part 3, we’ll move to backend development with C# .NET Core, connecting our API to the stored procedures and enabling the application to retrieve and display data dynamically.

Next Part

Part 3: Building a Multi-Layered Architecture in C# .NET Core for a Film/Series Review Application