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