Query Performance questions

[treeverti]

When testing leaf docs, I am finding that the queries can take some time with about 200,000 patients. The total rows we can have is considerably larger (about 12,000,000). I was wondering if there are some tips that can be provided to speed up query performance of leaf docs.

[ndobb]

That's very difficult to answer without more info. And at the end of the day, Leaf is executing queries that a human using a front-end SQL IDE could execute as well, so the same rules for optimization would apply (eg indexing). It's not unlike Tableau's use of SQL, for example (ie, if a query is slow, as the front-end application that is usually not Tableau's "fault").

If you can provide more detail such as

• Database flavor/vendor
• Hardware
• Data model
• Is this on the 'Find Patients' screen, 'Visualization', 'Patient List'? (the types of queries generated will all differ)
• What is the query you are trying to run (actual SQL)?
• Have you tried to run it yourself and see if your database recommends any indexes?
• In Leaf's appsettings, are you using CTE or Parallel QueryStrategy?

...I can perhaps provide some recommendations.