Discuss approach for improving experience of codes/search endpoint

[corytak]

I'm consistently getting 15s response times for the /codes/search endpoint. For reference, here's the body of the request:

{
	"term": "",
	"pagerSettings": {
		"currentPage": 1,
		"direction": "Asc",
		"itemsPerPage": 10,
		"orderBy": "name"
	},
	"summary": false
}

I can cache the first couple hundred results when the application loads, but as soon as a user searches for a code they'll need to wait for 15 seconds. Any thoughts on how this performance can be improved server side or the experience can improve for the user on the front end.

[rustyswayne]

@corytak I had a look at this today and did not find anything that stood out as an obvious tweak to improve performance on the query. The paged results actually rely on two underlying queries - one to count all possible records and the second that actually returns the current page result set (ordered and filtered). I'm finding these queries directly through SSMS are taking between 4 and 10 seconds ... even though they're simple ... like select count(*) from [Terminology].[Code] where RetiredFLG = 'N'

It's the table size -> 1.73 Million rows

I'm going to try reaching out to @ClintLing next week and see if there are any improvements we can make on that front first before looking for anything further to change in this code.

[rustyswayne]

@ClintLing - @corytak had the great suggestion to alter the Terminology.Code view to use UNION ALL instead of just UNION when combining the [Terminology].[Catalyst].[LocalCodeBASE] table with the [Terminology].[Catalyst].[CodeBASE]. When we tested it in SSMS the count query went from 8 seconds to under 1 ...

[ClintLing]

Great. I will make the change right now. If you have any specific clients that you are installed at I can go out to them also and alter the bindings. Let me know. Thanks! @corytak and @rustyswayne