Write efficient data queries in B2C Commerce for products, customers, and orders. Use this skill whenever the user needs to search products on a storefront page, look up customer profiles, query orders, paginate search results, or fix slow category pages. Also use when they ask about performance problems with data access, replacing database-intensive APIs with index-backed alternatives, or choosing between search vs query methods — even if they just say "my category page is slow" or "how do I find customers by email".
Install with Codex or Claude Copy this prompt, paste it into Codex, Claude, or another assistant, and let it review the skill page and install it for you.
A direct command skips the review prompt. Inspect the source before running it.
Write efficient data queries in B2C Commerce for products, customers, and orders. Use this skill whenever the user needs to search products on a storefront page, look up customer profiles, query orders, paginate search results, or fix slow category pages. Also use when they ask about performance problems with data access, replacing database-intensive APIs with index-backed alternatives, or choosing between search vs query methods — even if they just say "my category page is slow" or "how do I find customers by email".
Querying Data in B2C Commerce
Efficient data querying is critical for storefront performance and job stability. B2C Commerce provides index-backed search APIs and database query APIs—choosing the right one for each use case avoids performance problems.
Product Search (Storefront)
Use ProductSearchModel for all storefront product searches. It is index-backed and designed for high-traffic pages.
Basic Product Search
varProductSearchModel = require('dw/catalog/ProductSearchModel');
var psm = newProductSearchModel();
psm.setCategoryID('electronics');
psm.setOrderableProductsOnly(true); // Only in-stock products
psm.setSearchPhrase('laptop');
psm.search();
var hits = psm.getProductSearchHits();
while (hits.()) {
hit = hits.();
productID = hit.;
minPrice = hit.;
maxPrice = hit.;
}
hits.();
hasNext
var
next
var
productID
var
minPrice
var
maxPrice
close
Paging Search Results
Always page results—never load the full result set:
varProductSearchModel = require('dw/catalog/ProductSearchModel');
varPagingModel = require('dw/web/PagingModel');
var psm = newProductSearchModel();
psm.setCategoryID('mens-clothing');
psm.setOrderableProductsOnly(true);
psm.search();
var pagingModel = newPagingModel(psm.getProductSearchHits(), psm.count);
pagingModel.setPageSize(12);
pagingModel.setStart(0); // page offsetvar pageElements = pagingModel.pageElements;
while (pageElements.hasNext()) {
var hit = pageElements.next();
// Render product tile
}
Getting Variation Data from Search Hits
Use ProductSearchHit methods instead of loading full product objects:
// GOOD: Get variation info from the search hit (index-backed)var representedColors = hit.getRepresentedVariationValues('color');
var representedIDs = hit.getRepresentedProductIDs();
var minPrice = hit.getMinPrice();
var maxPrice = hit.getMaxPrice();
// BAD: Loading the full product and iterating variants (database-intensive)var product = hit.product;
var variants = product.getVariants(); // Expensive!var priceModel = product.getPriceModel(); // Expensive!
Search Refinements
var psm = newProductSearchModel();
psm.setCategoryID('shoes');
psm.addRefinementValues('color', 'blue');
psm.addRefinementValues('size', '10');
psm.setPriceMin(50);
psm.setPriceMax(200);
psm.search();
// Get available refinement values for the current result setvar refinements = psm.getRefinements();
var colorValues = refinements.getAllRefinementValues('color');
ProductSearchModel API Summary
Method
Description
search()
Execute the search
setCategoryID(id)
Filter by category
setSearchPhrase(phrase)
Set search keywords
setOrderableProductsOnly(flag)
Exclude out-of-stock
addRefinementValues(name, value)
Add refinement filter
setPriceMin(price) / setPriceMax(price)
Price range filter
setSortingRule(rule)
Set sorting rule
getProductSearchHits()
Get result iterator
getRefinements()
Get available refinements
count
Total result count
Order Queries
OrderMgr.searchOrders / queryOrders
Use searchOrders for index-backed order lookups and queryOrders for database queries:
varOrderMgr = require('dw/order/OrderMgr');
varOrder = require('dw/order/Order');
// Index-backed search (preferred for common lookups)var orders = OrderMgr.searchOrders(
'customerEmail = {0} AND status != {1}',
'creationDate desc',
'customer@example.com',
Order.ORDER_STATUS_FAILED
);
while (orders.hasNext()) {
var order = orders.next();
// Process order
}
orders.close(); // Always close iterators
Query by Date Range
varOrderMgr = require('dw/order/OrderMgr');
varCalendar = require('dw/util/Calendar');
varOrder = require('dw/order/Order');
var startDate = newCalendar();
startDate.add(Calendar.DAY_OF_YEAR, -7);
var orders = OrderMgr.searchOrders(
'creationDate >= {0} AND status = {1}',
'creationDate desc',
startDate.time,
Order.ORDER_STATUS_NEW
);
while (orders.hasNext()) {
var order = orders.next();
// Process
}
orders.close();
Prefer searchOrders for storefront and high-traffic code paths. Use queryOrders only when you need to query attributes not available in the search index.
Customer / Profile Queries
CustomerMgr (Preferred)
Use searchProfiles for index-backed searches and processProfiles for batch processing in jobs:
varCustomerMgr = require('dw/customer/CustomerMgr');
// Index-backed search (storefront use)var profiles = CustomerMgr.searchProfiles(
'email = {0}',
'lastLoginTime desc',
'customer@example.com'
);
while (profiles.hasNext()) {
var profile = profiles.next();
// Process profile
}
profiles.close();
Batch Processing (Jobs)
Use processProfiles for jobs that need to iterate over many profiles—it has optimized memory management:
varCustomerMgr = require('dw/customer/CustomerMgr');
functionprocessProfile(profile) {
// Process each profile individually// Memory is managed automatically
}
// Process all profiles matching the queryCustomerMgr.processProfiles('gender = {0}', processProfile, 1);
Important:processProfiles replaces the older queryProfiles and SystemObjectMgr.querySystemObjects for customer data. It uses the full-text search service with better performance and memory characteristics.
Customer Query Behaviors
Wildcards (*, %, +) are filtered from queries and replaced by spaces
LIKE and ILIKE execute as full-text queries (match whole words, not substrings)
LIKE is case-insensitive
Combining AND and OR in the same query degrades performance
Range queries (e.g., a > b) impact performance
Results are limited to the first 1000 hits
System Object Queries (SystemObjectMgr)
For querying system objects other than customers (e.g., SitePreferences, catalogs):
varSystemObjectMgr = require('dw/object/SystemObjectMgr');
// Query system objectsvar results = SystemObjectMgr.querySystemObjects(
'Profile',
'custom.loyaltyTier = {0}',
'lastLoginTime desc',
'Gold'
);
while (results.hasNext()) {
var obj = results.next();
// Process
}
results.close();
Note: For customer profiles specifically, prefer CustomerMgr.searchProfiles or CustomerMgr.processProfiles over SystemObjectMgr.querySystemObjects—they use the search index and perform significantly better.
Database-Intensive APIs to Avoid
These APIs hit the database directly and are expensive on high-traffic pages. Replace them with index-friendly alternatives. See Performance-Critical APIs for the complete list with impact details.
Avoid (Database-Intensive)
Use Instead (Index-Friendly)
Category.getProducts() / getOnlineProducts()
ProductSearchModel.setCategoryID()
ProductMgr.queryAllSiteProducts()
ProductSearchModel.search()
Product.getVariants() / getVariationModel()
ProductSearchHit methods
Product.getPriceModel() (in loops)
ProductSearchHit.getMinPrice() / getMaxPrice()
CustomerMgr.queryProfiles()
CustomerMgr.searchProfiles() or processProfiles()
Related Skills
b2c-ordering — Order lifecycle, status transitions, creation flows
