FHIR Server for Azure - How to retrieve FHIR Resource using logical ID leveraging the backend POINT READ Operation type in Cosmos DB

[LKShankarKarthik]

** Current Setup

• Microsoft FHIR Server for Azure deployed on Azure App Service
• Configured to use Cosmos DB as backend - Currently size is approx 3 TB and has over 400M documents

** Observation

While trying to retrieve MedicationRequest using logical ID with the below query, below are the observations

https://[fhirserverurl]/MedicationRequest/_id=[logicalid]

• On average this request takes 15 seconds as per Application Insights
• As per Cosmos DB Diganostic Logs it uses Query Operation Type (& cant see PartitionKey being used in the WHERE clause of the Query)

** Clarification
How to retrieve MedicationRequest using logical ID (without performing Search) ? Will the below query help? Main objective to improve performance and to Optimise Cosmos DB cost.

https://[fhirserverurl]/MedicationRequest/[logicalid]

When tried using below Query, couldnt find a trace of this query in Cosmos DB diagnostic logs. How to confirm whether the FHIR Query uses POINT READ operation type on Cosmos DB?

[brendankowitz]

Hi @LKShankarKarthik,

There's a lot there, but for the example https://[fhirserverurl]/MedicationRequest/[logicalid], yes, FHIR Server will do a direct Read, not a search. In the case of a versioned read, it is using a query but is scoped to the exact logical partition where we know the item exists.

fhir-server/src/Microsoft.Health.Fhir.CosmosDb/Features/Storage/CosmosFhirDataStore.cs

Lines 470 to 500 in 1f585f7

	public async Task<ResourceWrapper> GetAsync(ResourceKey key, CancellationToken cancellationToken)
	{
	EnsureArg.IsNotNull(key, nameof(key));
	bool isVersionedRead = !string.IsNullOrEmpty(key.VersionId);
	if (isVersionedRead)
	{
	QueryDefinition sqlQuerySpec = new QueryDefinition($"select {SearchValueConstants.SelectedFields} from root r where r.resourceId = @resourceId and r.version = @version")
	.WithParameter("@resourceId", key.Id)
	.WithParameter("@version", key.VersionId);
	(IReadOnlyList<FhirCosmosResourceWrapper> results, _) = await _retryExceptionPolicyFactory.RetryPolicy.ExecuteAsync(() =>
	ExecuteDocumentQueryAsync<FhirCosmosResourceWrapper>(
	sqlQuerySpec,
	new QueryRequestOptions { PartitionKey = new PartitionKey(key.ToPartitionKey()) },
	cancellationToken: cancellationToken));
	return results.Count == 0 ? null : results[0];
	}
	try
	{
	return await _containerScope.Value
	.ReadItemAsync<FhirCosmosResourceWrapper>(key.Id, new PartitionKey(key.ToPartitionKey()), cancellationToken: cancellationToken);
	}
	catch (CosmosException exception) when (exception.StatusCode == HttpStatusCode.NotFound)
	{
	return null;
	}
	}

The example https://[fhirserverurl]/MedicationRequest/_id=[logicalid] is scoped as an array in FHIR (_id=id1,id2,id3,id4...) so it may access one or more logical partitions. While it seems feasible to make such an optimization it's not on the team's radar right now.