Skip to content

Latest commit

 

History

History

optimize-excessive-rows

README.md

Prisma Optimize Usage Example: Applying the "Excessive number of rows returned" Recommendation

This repository demonstrates how to use Prisma Optimize to improve query performance using the "Excessive number of rows returned" recommendation.

Prerequisites

To successfully run the project, you will need the following:

  1. A database connection string supported by Prisma Optimize.
  2. An Optimize API key, which you can obtain from your Prisma Data Platform account.

Getting started

1. Clone the repository

Clone the repository, navigate into it, and install the dependencies:

git clone [email protected]:prisma/prisma-examples.git --depth=1
cd prisma-examples/optimize/optimize-excessive-rows
npm install

2. Configure environment variables

Create a .env file in the root of the project directory:

cp .env.example .env

Next, open the .env file and update the DATABASE_URL with your database connection string and the OPTIMIZE_API_KEY with your Optimize API key:

# .env
DATABASE_URL="__YOUR_DATABASE_CONNECTION_STRING__"
# Replace __YOUR_DATABASE_CONNECTION_STRING__ with your actual connection string.
OPTIMIZE_API_KEY="your_secure_optimize_api_key"
  • DATABASE_URL: The connection string to your database.
  • OPTIMIZE_API_KEY: Reference the Environment API Keys section in our documentation to learn how to obtain an API key for your project using Optimize.

3. Set up the project

Perform a database migration to prepare the project:

npx prisma migrate dev --name init

If the database isn't seeded, run the following command to seed it:

npx prisma db seed

4. Open the Optimize dashboard

You can create recordings and view detailed insights into your queries, along with optimization recommendations, in the Optimize dashboard. To access the dashboard:

  1. Log in to your Prisma Data Platform account. If you haven't already, complete the onboarding process for Optimize by clicking the Get Started button.
  2. If Optimize hasn't been launched yet, click the Launch Optimize button.
  3. If you want to use a different workspace, navigate to your desired Workspace, click the Optimize tab on the left sidebar to open the Optimize dashboard. Then, if Optimize is not yet launched, click the Launch Optimize button.

5. Run the script

Let's run the script with unoptimized Prisma queries:

  1. In the Optimize dashboard, click the Start new recording button.

  2. In the project terminal, run the project with:

    npm run dev

  3. After the script completes, you'll see a log saying "Done." Then, in the Optimize dashboard, click the Stop recording button.

  4. Observe the queries with high latencies highlighted in red, and review the recommendations in the Recommendations tab. You should see the recommendation:

    • Excessive number of rows returned

      For more insights on this recommendation, click the Ask AI button and interact with the AI Explainer chatbot.

  5. To create a reference for comparison with other recordings, rename the recording to Unoptimized queries by clicking the green recording label chip in the top left corner and typing "Unoptimized queries".

    Rename recording

Optimize example: applying the "Excessive number of rows returned" recommendation

Next, let’s follow the recommendation provided by Optimize to improve the performance of the queries:

  1. To improve the performance of Query 1 by addressing the Excessive number of rows returned recommendation, add a take option to the query:

    await prisma.user.findMany({
  take: 10,
})

  2. Click the Start new recording button to begin a new recording and check for any performance improvements.

  3. In the project terminal, run the project with:

    npm run dev

  4. After the script completes, click the Stop recording button.

  5. Rename the recording to Optimized queries by clicking the recording chip in the top left corner and typing "Optimized queries."

You can now compare performance improvements by navigating to the "Optimized queries" and "Unoptimized queries" recording tabs and observing the query latency differences.

Next steps