How ALEF Bookstores Scaled 34,000+ Titles with WooCommerce

Background

ALEF Bookstores had built a strong reputation across Egypt by offering a diverse catalogue of more than 34,000 books in both physical branches and online. With business expanding rapidly, manual stock tracking and scattered identifiers began to choke operations. Each book entry shared common identifiers such as ISBNs, but overlapping titles and multiple warehouse locations made items effectively untraceable in practice.

Initial System Setup

To centralize product data, ALEF employed Opentaps, an open-source ERP, as the master system for inventory, pricing, and metadata. They then stood up a WordPress site powered by WooCommerce for their storefront. The goal was clear: build a sync mechanism that pulled updates from Opentaps and reflected them on the website. The first iteration ran an XML export four times a day, parsed each file, and inserted or updated products viawp_insert_postandupdate_post_meta. It seemed straightforward until the process stretched over 18 hours end to end.

Facing Memory and Time Constraints

A full-day synchronization window was unacceptable and the server often exhausted memory. After isolating the issue, the team split the master XML into chunks of 100 products. That mitigated crashes but did little for speed. Next, they leveragedwp_defer_term_countingto suspend taxonomy updates during imports. By deferring term counts until batch completion, they slashed runtime to about six hours. Yet half a day per full sync still left little breathing room.

Database Indexing and Batch Commits

To identify existing products, each book’s ERP ID was stored as a post meta value. However, a rawget_postscall with a meta query without proper indexing can quickly grind the database to a halt. The solution was to add an index on themeta_valuecolumn. Then, all insert and update queries were wrapped in transactions:AUTOCOMMIT OFF, commit after every 10,000 records, thenAUTOCOMMIT ONonce complete. These changes halved full-sync time again, bringing it down to three hours.

Real-Time Stock Updates

Stock quantities change frequently when a sale happens in any branch. Originally, the ERP would export all data for any item that saw a change, leading to full-data imports that took hours. Shifting gears, the team had Opentaps push only quantity data into a dedicated MySQL table. A database view then joined ERP quantities with WooCommerce post meta IDs. In the loop, each product had its stock set via a stripped-down version of WooCommerce’sset_stockfunction and custom queries with disabled checks. That reduced an hour-long update to under two minutes.

End Result

What began as an 18-hour batch process now runs in three hours for full catalog updates and in under two minutes for stock adjustments. ALEF Bookstores can refresh inventory overnight with no service disruptions and ensure customers see accurate in-stock data at all times. Their site handles dozens of daily transactions without a hiccup, leaving room to focus on growth instead of firefighting technical debt.

Key Lessons

• Building sync tools on top of WordPress functions is easy to start but demands database tuning for scale.
• Deferring term counts and batching database commits directly influences large-dataset performance.
• A small custom function can outperform general-purpose CMS logic by skipping unnecessary checks.
• Using an ERP export to feed only changed-field tables unlocks lightweight, high-frequency updates.
• Proper indexing on meta tables is critical when using meta queries to find matching posts.

Conclusion

By iterating on bottlenecks at both the application and database layers, ALEF Bookstores transformed their WooCommerce site from a fragile, slow import setup into a reliable, high-performance platform. Modern shoppers demand real-time accuracy—this case study shows how to deliver it at scale.