SQL Query need to be optimized (GridDB) specific Solutions needed

We have a GridDB database that contains sales data for an e-commerce platform. There are three relevant tables: Orders, Products and OrderItems.

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    TotalAmount DECIMAL(10, 2)
);


CREATE TABLE OrderItems (
    OrderItemID INT PRIMARY KEY,
    OrderID INT,
    ProductID INT,
    Quantity INT,
    Price DECIMAL(10, 2)
);


CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(255),
    Category VARCHAR(255)
);

I am trying to write an SQL query to find the total revenue generated for each product category in the year 2023. However, the database is massive, and both tables (Orders and Ordersitems) have 1 million+ rows. Writing a straightforward query would be inefficient and slow.

I have written an SQL Query but its not functioning well. Need to optimize it so it can return results quickly.

I have written this:

SELECT DISTINCT
    P.Category,
    (SELECT SUM(O.TotalAmount)
     FROM Orders O
     INNER JOIN OrderItems I ON O.OrderID = I.OrderID
     INNER JOIN Products P2 ON I.ProductID = P2.ProductID
     WHERE P2.Category = P.Category
       AND EXTRACT(YEAR FROM O.OrderDate) = 2023
    ) AS CategoryTotalRevenue
FROM Products P;

You can try using groupby.

SELECT
    P.Category,
    SUM(O.TotalAmount) AS CategoryTotalRevenue
FROM
    Products P
    INNER JOIN OrderItems I ON P.ProductID = I.ProductID
    INNER JOIN Orders O ON I.OrderID = O.OrderID
WHERE
    EXTRACT(YEAR FROM O.OrderDate) = 2023
GROUP BY
    P.Category;