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

var ProductSearchModel = require('dw/catalog/ProductSearchModel');

var psm = new ProductSearchModel();
psm.setCategoryID('electronics');
psm.setOrderableProductsOnly(true); // Only in-stock products
psm.setSearchPhrase('laptop');
psm.search();

var hits = psm.getProductSearchHits();
while (hits.hasNext()) {
    var hit = hits.next();
    var productID = hit.productID;
    var minPrice = hit.minPrice;
    var maxPrice = hit.maxPrice;
}
hits.close();

Paging Search Results

Always page results—never load the full result set:

var ProductSearchModel = require('dw/catalog/ProductSearchModel');
var PagingModel = require('dw/web/PagingModel');

var psm = new ProductSearchModel();
psm.setCategoryID('mens-clothing');
psm.setOrderableProductsOnly(true);
psm.search();

var pagingModel = new PagingModel(psm.getProductSearchHits(), psm.count);
pagingModel.setPageSize(12);
pagingModel.setStart(0); // page offset

var 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 = new ProductSearchModel();
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 set
var refinements = psm.getRefinements();
var colorValues = refinements.getNextLevelRefinementValues(
    refinements.getRefinementDefinitionByName('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:

var OrderMgr = require('dw/order/OrderMgr');
var Order = 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

var OrderMgr = require('dw/order/OrderMgr');
var Calendar = require('dw/util/Calendar');
var Order = require('dw/order/Order');

var startDate = new Calendar();
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();

searchOrders vs queryOrders

Aspect	`searchOrders`	`queryOrders`
Backing	Search index	Database
Performance	Fast for indexed fields	Slower, full table scan possible
Use when	Querying indexed attributes (status, email, dates)	Querying non-indexed or custom attributes
Result limit	Up to 1000 hits	No hard limit (but use paging)

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:

var CustomerMgr = 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:

var CustomerMgr = require('dw/customer/CustomerMgr');

function processProfile(profile) {
    // Process each profile individually
    // Memory is managed automatically
}

// Process all profiles matching the query
CustomerMgr.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):

var SystemObjectMgr = require('dw/object/SystemObjectMgr');

// Query system objects
var 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
b2c-custom-objects — Custom object CRUD, OCAPI search queries

Best Practices

Do

Always close iterators — unclosed iterators leak resources (results.close())
Page results — use PagingModel or limit result counts; never load unbounded result sets
Put all filtering in the query — don't post-process or filter results in custom code
Use index-backed APIs — ProductSearchModel, searchOrders, searchProfiles for storefront pages
Use processProfiles for batch customer operations in jobs (optimized memory)
Limit page size — maximum ~120 products per page for search result pages
Use setOrderableProductsOnly(true) — to filter unavailable products at the search level

Don't

Don't iterate over product variants on search result pages — use ProductSearchHit methods instead
Don't post-process search results — all criteria must go into the query for efficient execution
Don't use queryAllSiteProducts() on storefront pages — it bypasses the search index
Don't combine AND + OR in customer queries — it degrades performance
Don't rely on getting more than 1000 results — search APIs cap at 1000 hits
Don't call database-intensive APIs on high-traffic pages — category pages, search results, PDPs, and homepage

Job-Specific Guidelines

Use processProfiles over queryProfiles for large customer data sets
Design loop logic so memory consumption doesn't grow with result set size
Keep only the currently processed object in memory; don't retain references
Stream data to files regularly; don't build large structures in memory
Limit transaction size to under 1000 modified business objects

Detailed References

Performance-Critical APIs — full list of index-friendly vs database-intensive APIs

b2c-querying-data