The Challenge

A health organization relied on SharePoint lists to store all patient bill items. Each case contained multiple bill items, leading to a total of over 3 million entries in the SharePoint list. Initially, the system was designed to load bill items dynamically using a button click rather than loading them on page load, to optimize performance. However, due to SharePoint’s list view threshold limitation of 5000 items per query, retrieving bill items was still extremely slow, taking several minutes to complete. This significantly impacted user experience, making it difficult for healthcare professionals to access billing data efficiently.

The Limitations

The primary challenges faced due to the SharePoint architecture included:

• SharePoint List Threshold: SharePoint enforces a 5000-item limit per query to maintain system performance. Exceeding this threshold results in throttling or query failure, making large data retrieval inefficient.
• Paging Through Data: To avoid threshold issues, the system had to fetch data in pages, processing only 5000 records at a time. Given the sheer volume of data, this paging approach led to long wait times.
• User Experience Issues: Healthcare professionals needed quick access to billing records to process payments and manage patient accounts. Long wait times led to frustration, inefficiency, and delays in billing operations.
• Scalability Concerns: As the number of cases grew, the performance issue worsened, making the system unsustainable in the long run.

The Resolution

After conducting a thorough analysis of the problem, an alternative approach was designed and implemented to optimize data retrieval and improve performance. The solution involved:

• Data Storage Optimization: Instead of storing bill items in a SharePoint list, they were converted into CSV files and stored in a document library. Each case had its own file, containing bill items in CSV format.
• File-Based Retrieval Approach: When users needed to access bill items, instead of querying the SharePoint list, the system retrieved the corresponding CSV file.
• Metadata Indexing for Quick Search: Metadata, including the case number and relevant identifiers, was stored alongside the CSV file, allowing efficient search and retrieval without scanning the entire dataset.
• Fast Loading & Downloading: Users could now retrieve bill items almost instantly by fetching the CSV file, and downloading was completed in seconds.
• Reduction in API Calls: Previously, multiple API calls were required to paginate through the SharePoint list. The new approach reduced the number of calls, further improving response times and reducing system load.

The Results

This optimization yielded significant performance and usability improvements:

• Drastic Reduction in Load Time: Data retrieval time decreased from several minutes to just 1-2 seconds.
• Enhanced User Experience: Users could access and download billing records instantly, improving workflow efficiency.
• Scalability: The new approach eliminated SharePoint list limitations, allowing the system to handle increasing data volumes without performance degradation.
• Improved System Reliability: With fewer API calls and reduced SharePoint list usage, the risk of throttling and timeouts was eliminated.
• Better Maintainability: The system became easier to manage, as file-based storage was more efficient and scalable compared to an overloaded SharePoint list.

Conclusion

By rethinking data storage and retrieval methods, the health organization successfully overcame SharePoint’s inherent limitations. Converting bill items into CSV files and leveraging metadata for searchability provided a seamless, high-performance solution.

This case study demonstrates how a strategic architectural change can dramatically enhance system efficiency, scalability, and user satisfaction. Organizations dealing with large datasets in SharePoint can adopt similar approaches to optimize performance and streamline data access.

Cheers…