Enhancing CMS Application Efficiency with Prisma Optimize

Introduction

In addition to quick query execution, high-performance applications need efficient and customized data retrieval. Prisma Optimize offers insights and suggestions to assist developers in finding bottlenecks, optimizing queries, and simplifying database interactions.

Building on our Prisma Accelerate CMS project, we’ll add Prisma Optimize to measure and improve our query performance. By the end, you’ll see how Prisma Optimize’s recommendations can take a well-performing app to the next level.

Table of Contents

1. Introduction
2. What is Prisma Optimize?
3. Integrating Prisma Optimize into the CMS Project
4. Using Prisma Optimize to Analyze Queries
5. Applying Prisma Optimize Recommendations
6. Insights on AI-Powered Recommendations
7. Benchmarking Improvements
8. Conclusion

---

What is Prisma Optimize?

Prisma Optimize improves Prisma ORM by pointing out ineffective database operations and making recommendations for improvements, like:

• Analysis of queries and insights into performance.
• Suggestions for indexing, cutting down on complete table scans, and over-fetching.
• AI-supported justifications for every suggestion to assist you in successfully implementing changes.

Integrating Prisma Optimize into the CMS Project

Let’s add Prisma Optimize to our Prisma Accelerate CMS project by following these steps:

• Install Prisma Optimize packages

npm install @prisma/client@latest @prisma/extension-optimize

• Update schema.prisma with Tracing In schema.prisma, add the tracing preview feature to the generator block:

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["tracing"]
}

• Then, regenerate Prisma Client:

npx prisma generate

• Log in to Data Proxy Platform, navigate your existing workspace, and click on optimize at the top left corner bar.

• Click on launch optimize which brings you to a new page

• Click on the settings icon to generate API keys

• Add Prisma Optimize API Key to your environment variables:

OPTIMIZE_API_KEY="YOUR_OPTIMIZE_API_KEY"

• Extend Prisma Client with Prisma Optimize In your Prisma Client configuration file (src/lib/prisma.ts):

import { PrismaClient } from '@prisma/client';
import { withOptimize } from '@prisma/extension-optimize';
import { withAccelerate } from '@prisma/extension-accelerate';

const prisma = new PrismaClient().$extends(
  withOptimize({
    apiKey: process.env.OPTIMIZE_API_KEY, 
  })
).$extends(
  withAccelerate()
);

export default prisma;

---

Using Prisma Optimize to Analyze Queries

To modify our existing code to incorporate Prisma Optimize, you can replace the include statement from the Post route with select to specify the fields you need. Here’s how you can do it:

Previous code:

export async function GET(req) {
  const posts = await prisma.post.findMany({
    include: { author: true, category: true },
    cacheStrategy: { ttl: 3600 }
  });
  return new Response(JSON.stringify(posts), {
    headers: { 'Content-Type': 'application/json' },
  });
}

to use this const posts = await prisma.post.findMany({
  select: { id: true, title: true, content: true },
});

Updated Version:

export async function GET(req) {
  const posts = await prisma.post.findMany({
    select: {
      id: true,
      title: true,
      content: true,
      author: { select: { name: true } }, 
      category: { select: { name: true } }
    },
    cacheStrategy: { ttl: 3600 },
  });

  return new Response(JSON.stringify(posts), {
    headers: { 'Content-Type': 'application/json' },
  });
}

We use select for post, author, and category to only fetch necessary fields, reducing payload size and optimizing performance.
To modify your existing code to incorporate both Prisma Accelerate and the selective field retrieval from Prisma Optimize, we replaced the include statement with select to specify the fields we need.

Analyzing Queries With Prisma Optimize

With Prisma Optimize setup, let’s record a session and analyze some queries from our CMS project.

• Start a Recording Session In the Prisma Optimize dashboard on Prisma Data Platform, click Start Recording to begin collecting query performance data.

• Run Queries in the Application such as loading posts, creating new users, or fetching categories. These interactions will be logged in the recording session for analysis.

• Stop the Recording Session once you’ve captured enough queries, in the Prisma Optimize dashboard.

• Analyze the Results and explore query metrics in Prisma Optimize because the dashboard displays query types, execution times, and recommended improvements.

• The recommendation by Prisma Optimize

From the recorded session, the current query to fetch posts has Excessive number of rows returned and Prisma optimize recommends:

We recommend using the take option to limit the number of entries returned by your queries:

Applying Prisma Optimize Recommendations

Let’s apply a few common recommendations Prisma Optimize suggests for our CMS project.

Excessive number of rows returned
Solution: take option to limit the number of entries returned.

prisma.post.findMany({
  take: 10, // Limits the number of results to 10
  // other query parameters
})

Excessive Data Retrieval
Solution: Use select to fetch only required fields:

const posts = await prisma.post.findMany({
  select: { id: true, title: true, content: true },
});

---

Insights on AI-Powered Recommendations

Prisma Optimize uses AI to make suggestions and give you details that help you understand why a change is recommended. For example, if Prisma AI says that adding an index to a WHERE clause would make it work better, it will explain how the index could cut down on scan times. This advice is especially helpful for queries or models that are complicated, as it can be hard to figure out how an optimization will affect them.

I asked questions I needed clarification on based on recommendations and the above was the response from AI.

---

Benchmarking Improvements

After applying Prisma Optimize’s recommendations, we measure the impact on query performance using benchmarking techniques.

export async function GET(req) {
  const posts = await prisma.post.findMany({ take: 10, select: { id: true, title: true } , cacheStrategy: { ttl: 3600 },})
  return new Response(JSON.stringify(posts), {
    headers: { 'Content-Type': 'application/json' },
  });
}

Initial Query Performance (Before Optimization)

Optimized Query Performance (After Applying Recommendations)

## Performance Comparison Before and After Optimization

| Request           | Response Time Before Optimization | Response Time After Optimization |
|-------------------|-----------------------------------|----------------------------------|
| GET /api/posts    | 3975 ms                           | 265 ms                           |
| GET /api/posts    | 3975 ms                           | 154 ms                           |
| GET /api/posts    | 3975 ms                           | 106 ms                           |
| GET /api/posts    | 3975 ms                           | 158 ms                           |
| GET /api/posts    | 3975 ms                           | 118 ms                           |
| GET /api/posts    | 3975 ms                           | 128 ms                           |

This table reflects the improvement in response times after optimization.
All the codes can be found in this repository.

---

Conclusion

Including Prisma Optimize in our Prisma Accelerate CMS project has shown how well-focused query optimizations improve application speed. Providing insightful analysis, also helps developers lower database load and increase response times.

Prisma Optimize is best for applications that:

• Handle large datasets and require efficient data retrieval.
• Operate in serverless environments where database connections need to be managed effectively.
• Experience high traffic and need scalable solutions to maintain performance under load.