5 Simple Tips for Improving Query Performance
Running complex or multiyear queries without adversely affecting other ESSENCE users can be challenging, even for power users. Here are some general tips for queries that will help ESSENCE run quickly and efficiently for all users.
- Let the query run completely before you start making changes. Often in the process of running or testing a query, small changes need to be made, usually resulting in an attempt to cancel the query and rerun an updated one. Unfortunately, even after you close the browser window, hit the cancel or refresh button in your browser, or cancel the Application Programming Interface (API) request, the original query will continue to run to completion in the database. Browser actions do not cancel queries already sent to the database. This means that multiple queries that are similar can be running at the same time on ESSENCE. A good rule of thumb is to wait until you see data before refreshing and resubmitting—otherwise, you might encounter the browser’s nonstop spinning wheel. In other words, avoid submitting the same query multiple times.
- Run ONE free-text query at a time. When you’re working with large datasets that span multiple years, although it might seem quick and efficient to run multiple queries in parallel, it’s not. The more requests the server receives, the more time all query requests take to run. Instead, try running requests sequentially. That’ll lead to better performance. Always avoid running multiple free-text queries at the same time.
- Use short time parameters to test free-text queries. When fine-tuning a free-text query, a large time range is rarely necessary to get a sense of the query results. We recommend using 3 to 6 months’ data from the Chief Complaint Query Validation (CCQV) data source. If you are analyzing a rare health event or seasonal effects, try running the same query multiple times over different 3- to 6-month periods.
- Run multiyear trends of free-text or complex queries when there’s less traffic. If you want to identify the multiyear trend of a free-text query or a complex query with many filters, try running the query either early in the morning or late in the afternoon when fewer analysts are using ESSENCE. Or, consider turning the free-text queries that you perform routinely into Chief Complaint Discharge Diagnosis (CCDD) Categories in NSSP–ESSENCE, which should speed up performance dramatically. Please contact us if you have a query you would like us to convert to a CCDD Category.
- Use the ESSENCE API. If you need to analyze a free-text query over multiple years using methods outside ESSENCE, break down your request into smaller chunks of time—for example, into 3- to 6-month intervals—and then combine these to form one large dataset. In short, avoid running one multiyear free-text query using the APIs in favor of multiple queries broken up into time increments.
The NSSP team routinely monitors system performance and assesses system architecture to keep up with demand. If you have questions or want to modify your workflow to keep ESSENCE running efficiently, please contact us. We will tailor additional guidance based on your analysis needs.