Sales Order Customer Link Field Slow with Large Dataset (~2 Million Records)

1

Hi everyone,

I’m facing a performance issue with the Customer Link field in Sales Order.

In our system, we have around 2 million (20 lakh) Customer records. Due to this large dataset, the Link field search and dropdown suggestions are slow — it takes noticeable time to fetch results when typing in the customer field.

Current Behavior:

  • Typing in the Customer field is laggy

  • Dropdown suggestions take time to load

  • Overall user experience is slow

Expected Behavior:

  • Fast search results (like instant suggestions)

  • Optimized query performance even with large datasets

What I’ve Checked:

  • Basic indexing exists on name field

  • Server performance is normal

  • Issue seems specific to Link field query

Questions:

  1. How can we optimize the Link field search performance in Frappe for large datasets?

  2. Is there a way to:

    • Limit search scope (e.g., based on territory/company)?

    • Customize the query for faster lookup?

  3. Should we override the Link field query using set_query or server-side custom query?

  4. Any best practices for handling millions of records in Link fields?

Additional Info:

  • Frappe / ERPNext Version: (15)

  • Database: MariaDB

Would appreciate any suggestions or best practices