Reindexing job reported as completed, even though it didn't process a single record.

[rafalbroll-datapharm]

Describe the bug While running the reindex job for newly defined custom search parameter, FHIR Server reports completion, even though no record is indexed (neither can be found using the new search param).

Hosting details: The FHIR Server is hosted inside the Azure Container App, with assigned resources: 1 CPU, 2 GB RAM

The data instance sizes: The entire Resources table is ~60 GB, 978 676 rows The biggest Resource row (measured as datalength(rawresource)) is 5 MB, average is 58 KB

FHIR Version? R4B, [3.4.342]

Data provider? SQL Server

To Reproduce Steps to reproduce the behaviour:

1. Set up the custom search parameter running the command:

   curl --location --request PUT "http://${FHIR_HOST}/SearchParameter/datapharm-product-family" \
   --header 'Content-Type: application/json' \
   --data-raw '{
   "resourceType" : "SearchParameter",
   "id": "datapharm-product-family",
   "url" : "https://medicines.org.uk/productFamily",
   "version" : "0.0.1",
   "name" : "datapharm-product-family",
   "status" : "active",
   "date" : "2023-02-01",
   "publisher" : "Datapharm Ltd",
   "contact" : [
   {
     "telecom" : [
       {
         "system" : "other",
         "value" : "https://www.datapharm.com/"
       }
     ]
   }
   ],
   "description" : "Searching by product family",
   "jurisdiction" : [
   {
     "coding" : [
       {
         "system" : "https://medicines.org.uk/productFamily",
         "code" : "UK",
         "display" : "United Kingdom"
       }
     ]
   }
   ],
   "code" : "datapharm-product-family",
   "base" : [
   "MedicinalProductDefinition"
   ],
   "type" : "token",
   "expression" : "MedicinalProductDefinition.extension.where(url='\''https://medicines.org.uk/productFamily'\'').value"
   }'

2. Run the reindexing Job

   { 
   "resourceType": "Parameters",  
   "parameter": [
          {
           "name": "targetSearchParameterTypes",
           "valueString": "https://medicines.org.uk/productFamily"
         }    
     ]
   }

3. Check the results by calling GET /_operations/reindex/<reindex job id>

Expected behavior The result is

...
{
    {
       "name":"totalResourcesToReindex",
       "valueDecimal": 18514.0
    },
    {
       "name":"resourcesSuccessfullyReindexed",
       "valueDecimal": 18514.0
    },
    {
       "name":"progress",
       "valueDecimal": 100.0
    },
    {
       "name":"status",
       "valueString": "Completed"
    }
}
...

and attempts of searching by that parameters succeed.

Actual behavior The result is

...
{
    {
       "name":"totalResourcesToReindex",
       "valueDecimal": 18514.0
    },
    {
       "name":"resourcesSuccessfullyReindexed",
       "valueDecimal": 0.0
    },
    {
       "name":"progress",
       "valueDecimal": 0.0
    },
    {
       "name":"status",
       "valueString": "Completed"
    }
}
...

and attempts of searching by that parameter fail.

---

Applying maximumNumberOfResourcesPerQuery:

In addition, we were experimenting with adding the parameter maximumNumberOfResourcesPerQuery while running the Reindexing Job. That partially deals with the problem, but sometimes it indexes only 99% of the records. ie. for Reindex Job defined like that:

{ 
    "resourceType": "Parameters",  
    "parameter": [
           {
            "name": "targetSearchParameterTypes",
            "valueString": "https://medicines.org.uk/productFamily"
          } ,
          {
             "name": "maximumNumberOfResourcesPerQuery",
             "valueInteger": "5000"
         }
      ]
}

we got that response

...
{
    {
       "name":"totalResourcesToReindex",
       "valueDecimal": 18514.0
    },
    {
       "name":"resourcesSuccessfullyReindexed",
       "valueDecimal": 18513.0
    },
    {
       "name":"progress",
       "valueDecimal": 99.99
    },
    {
       "name":"status",
       "valueString": "Completed"
    }
}
...

[EXPEkesheth]

@rafalbroll-datapharm - Are you provisioned on Cosmos DB/ SQL persistence layer?

[rafalbroll-datapharm]

@rafalbroll-datapharm - Are you provisioned on Cosmos DB/ SQL persistence layer?

It's the SQL Server.

[fahadnadeem1995]

@rafalbroll-datapharm I am facing the same issue with Azure FHIR Service with SQL server.

[rafalbroll-datapharm]

@rafalbroll-datapharm I am facing the same issue with Azure FHIR Service with SQL server.

Just to clarify, which scenario do you experience? Having a completed task with 0.0% progress, or the latter - having 99% of completion?

[fahadnadeem1995]

@rafalbroll-datapharm It shows completion with 0.0% progress although indicating that a few resources reindexed successfully. And a few resources do return as a result of using that custom search parameter in the query.

Please have a look at the screenshot below:

[EXPEkesheth]

Thanks for reporting , we will investigate further #AB125852