5 Simple Tips for Improving Query Performance

Tips

1. 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.
2. 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 will lead to better performance. Always avoid running multiple free-text queries at the same time.
3. 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 of 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.
4. 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. You may also 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.
5. 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.