Scan vs Query performance comparison in DynamoDB - Hands-On Comparison

Scan vs Query Performance Comparison in DynamoDB

📖 Scenario: You are managing a DynamoDB table that stores customer orders. You want to understand the difference in performance between using Scan and Query operations to retrieve data.

🎯 Goal: Build a simple DynamoDB setup with a table of orders, then write code to perform a Scan and a Query operation. Compare how each operation works and understand their performance differences.

📋 What You'll Learn

Create a DynamoDB table named Orders with OrderId as the partition key

Insert 5 sample orders with specific OrderId and CustomerName

Define a variable to hold the CustomerName to query

Write a Query operation to get orders for the given CustomerName

Write a Scan operation to get all orders and filter by CustomerName

💡 Why This Matters

🌍 Real World

Understanding the difference between Scan and Query helps optimize database performance and cost in real DynamoDB applications.

💼 Career

Database developers and cloud engineers often need to choose the right DynamoDB operation to efficiently retrieve data.

1

Create the Orders table with sample data

Create a dictionary called orders with these exact entries representing orders: 'OrderId': 101, 'CustomerName': 'Alice', 'OrderId': 102, 'CustomerName': 'Bob', 'OrderId': 103, 'CustomerName': 'Alice', 'OrderId': 104, 'CustomerName': 'Charlie', 'OrderId': 105, 'CustomerName': 'Bob'. Store these as a list of dictionaries in orders.

DynamoDB

# Create the orders list with 5 dictionaries
# Your code here

Hint

Use a list of dictionaries. Each dictionary has keys 'OrderId' and 'CustomerName'.

2

Set the customer name to query

Create a variable called customer_to_find and set it to the string 'Bob'.

DynamoDB

orders = [
    {'OrderId': 101, 'CustomerName': 'Alice'},
    {'OrderId': 102, 'CustomerName': 'Bob'},
    {'OrderId': 103, 'CustomerName': 'Alice'},
    {'OrderId': 104, 'CustomerName': 'Charlie'},
    {'OrderId': 105, 'CustomerName': 'Bob'}
]
# Create the variable customer_to_find with value 'Bob'
# Your code here

Hint

Just assign the string 'Bob' to the variable customer_to_find.

3

Write the Query operation to find orders for Bob

Create a list called query_results that contains only the orders from orders where CustomerName equals customer_to_find. Use a list comprehension with order as the loop variable.

DynamoDB

orders = [
    {'OrderId': 101, 'CustomerName': 'Alice'},
    {'OrderId': 102, 'CustomerName': 'Bob'},
    {'OrderId': 103, 'CustomerName': 'Alice'},
    {'OrderId': 104, 'CustomerName': 'Charlie'},
    {'OrderId': 105, 'CustomerName': 'Bob'}
]

customer_to_find = 'Bob'
# Use a list comprehension to create query_results with orders where CustomerName == customer_to_find
# Your code here

Hint

Use a list comprehension with order in orders and filter by order['CustomerName'] == customer_to_find.

4

Write the Scan operation to find orders for Bob

Create a list called scan_results that contains all orders from orders but filtered by CustomerName equal to customer_to_find. Use a for loop with order as the loop variable and an if statement inside the loop to append matching orders to scan_results.

DynamoDB

orders = [
    {'OrderId': 101, 'CustomerName': 'Alice'},
    {'OrderId': 102, 'CustomerName': 'Bob'},
    {'OrderId': 103, 'CustomerName': 'Alice'},
    {'OrderId': 104, 'CustomerName': 'Charlie'},
    {'OrderId': 105, 'CustomerName': 'Bob'}
]

customer_to_find = 'Bob'

query_results = [order for order in orders if order['CustomerName'] == customer_to_find]

# Use a for loop to create scan_results filtering orders by CustomerName == customer_to_find
# Your code here

Hint

Initialize scan_results as an empty list. Use a for loop over orders. Inside the loop, use an if statement to check order['CustomerName'] == customer_to_find. Append matching orders to scan_results.

Practice

1. Which DynamoDB operation is generally faster when you know the partition key of the item you want to retrieve?

easy

A. Scan

B. UpdateItem

C. Query

D. DeleteItem

Solution

1. Step 1: Understand Query operation

   Query uses the partition key to directly find matching items, making it efficient.

2. Step 2: Compare with Scan operation

   Scan reads the entire table, which is slower and less efficient.

3. Final Answer:

   Query -> Option C

4. Quick Check:

   Query is faster for known keys [OK]

Hint: Use Query when you know the partition key for speed [OK]

Common Mistakes:

• Thinking Scan is faster because it reads all data
• Confusing Query with Scan
• Assuming UpdateItem is for reading data

2. Which of the following is the correct syntax to perform a Query operation in DynamoDB using AWS SDK for JavaScript?

easy

A. dynamoDbClient.query({ TableName: 'MyTable', KeyConditionExpression: '#pk = :pkval', ExpressionAttributeNames: { '#pk': 'PartitionKey' }, ExpressionAttributeValues: { ':pkval': '123' } })

B. dynamoDbClient.scan({ TableName: 'MyTable', KeyConditionExpression: 'PartitionKey = 123' })

C. dynamoDbClient.query({ TableName: 'MyTable', FilterExpression: 'PartitionKey = 123' })

D. dynamoDbClient.getItem({ TableName: 'MyTable', Key: { PartitionKey: '123' } })

Solution

1. Step 1: Identify correct Query syntax

   Query requires KeyConditionExpression with placeholders and attribute names/values.

2. Step 2: Check options for correct usage

   dynamoDbClient.query({ TableName: 'MyTable', KeyConditionExpression: '#pk = :pkval', ExpressionAttributeNames: { '#pk': 'PartitionKey' }, ExpressionAttributeValues: { ':pkval': '123' } }) uses KeyConditionExpression and ExpressionAttributeNames/Values correctly.

3. Final Answer:

   dynamoDbClient.query({ TableName: 'MyTable', KeyConditionExpression: '#pk = :pkval', ExpressionAttributeNames: { '#pk': 'PartitionKey' }, ExpressionAttributeValues: { ':pkval': '123' } }) -> Option A

4. Quick Check:

   Query needs KeyConditionExpression [OK]

Hint: Query needs KeyConditionExpression, not FilterExpression [OK]

Common Mistakes:

• Using FilterExpression instead of KeyConditionExpression for Query
• Using scan method with KeyConditionExpression
• Confusing getItem with query syntax

3. Given a DynamoDB table with 1000 items, what will be the main difference in performance between these two operations?
dynamoDbClient.scan({ TableName: 'MyTable' })
and
dynamoDbClient.query({ TableName: 'MyTable', KeyConditionExpression: '#pk = :pk', ExpressionAttributeNames: { '#pk': 'PartitionKey' }, ExpressionAttributeValues: { ':pk': '123' } })

medium

A. Scan reads all 1000 items; Query reads only matching items, so Query is faster.

B. Scan is faster because it reads all items at once; Query is slower due to filtering.

C. Both operations have the same speed because they access the same table.

D. Query reads all items; Scan reads only matching items.

Solution

1. Step 1: Understand Scan operation

   Scan reads every item in the table, so it processes all 1000 items.

2. Step 2: Understand Query operation

   Query uses the partition key to read only matching items, which is faster.

3. Final Answer:

   Scan reads all items; Query reads only matching items, so Query is faster. -> Option A

4. Quick Check:

   Scan reads all; Query reads matching [OK]

Hint: Scan reads whole table; Query reads only matching keys [OK]

Common Mistakes:

• Thinking Scan is faster because it reads all data at once
• Confusing Query reading all items
• Assuming both have same speed

4. You wrote this DynamoDB Query code but it returns no results:

const params = { TableName: 'MyTable', KeyConditionExpression: 'PartitionKey = :pk', ExpressionAttributeValues: { ':pk': '123' } };
const data = await dynamoDbClient.query(params);

What is the most likely error?

medium

A. Missing ExpressionAttributeNames for reserved word PartitionKey

B. Using Scan instead of Query

C. TableName is misspelled

D. Incorrect KeyConditionExpression syntax; should use #pk = :pk

Solution

1. Step 1: Check KeyConditionExpression syntax

   KeyConditionExpression requires placeholders for attribute names like #pk defined in ExpressionAttributeNames.

2. Step 2: Identify missing ExpressionAttributeNames

   The code uses 'PartitionKey' directly without ExpressionAttributeNames, causing no matches.

3. Final Answer:

   Incorrect KeyConditionExpression syntax; should use #pk = :pk -> Option D

4. Quick Check:

   Reserved words need placeholders in KeyConditionExpression [OK]

Hint: Use placeholders for reserved words in KeyConditionExpression [OK]

Common Mistakes:

• Not using ExpressionAttributeNames for reserved words
• Confusing Scan and Query methods
• Misspelling TableName

5. You want to retrieve all items where the attribute 'Status' equals 'Active' from a large DynamoDB table. The table's partition key is 'UserId'. Which approach is best for performance and cost?

hard

A. Use Query with KeyConditionExpression on 'UserId' and FilterExpression on 'Status' = 'Active'.

B. Create a Global Secondary Index (GSI) on 'Status' and Query the GSI for 'Active' items.

C. Use Scan with a FilterExpression on 'Status' = 'Active' to get all matching items.

D. Use Scan without any filters to get all items and then filter in application code.

Solution

1. Step 1: Understand limitations of Scan and Query

   Scan reads entire table and is costly; Query requires partition key, but 'Status' is not the partition key.

2. Step 2: Use GSI for efficient querying

   Creating a GSI on 'Status' allows Query on 'Status' attribute efficiently without scanning.

3. Final Answer:

   Create a Global Secondary Index (GSI) on 'Status' and Query the GSI for 'Active' items. -> Option B

4. Quick Check:

   GSI enables efficient queries on non-key attributes [OK]

Hint: Use GSI to query non-key attributes efficiently [OK]

Common Mistakes:

• Using Scan with filters on large tables
• Trying to Query without partition key
• Filtering in application instead of database