Error in running Achilles - query processor ran out of internal resources

jchang · March 16, 2022, 8:28pm

Hello

we received the following error while running Achilles. It happened with it tried to create the #s_tmpach_2004 temp table. Any advise?

Error:
com.microsoft.sqlserver.jdbc.SQLServerException: The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.

Thank you
Jack

Frank · March 17, 2022, 5:51pm

Can you describe your server (cores, cpu, memory)? You can also try processing without analysis 2004 using excludeAnalysisIds=c(2004) as a parameter to the Achilles execution.

jchang · March 17, 2022, 6:38pm

Thanks @Frank . Achilles ran through with analysis 2004 skipped, no errors! I am wondering what analysis 2004 does, and what implications could be to skip this step.

Frank · March 18, 2022, 2:00pm

This query runs an analysis to determine how many people in the CDM have data across multiple domains. It calculates the number and proportion of people with at least one record in the condition_occurrence and drug_exposure tables, for example, and then continues that for all possible permutations of the domain tables. Currently the analysis results are used in a new tool that is part of ARES, and no where else, so you should not see an issue by skipping this analysis.

The query is long but we haven’t seen performance issues in our environments. It would be helpful if you could provide details on your environment to inform whether this could potentially be an issue at multiple sites. Could you share the specifications of your server and which database platform you are using?

solmaz.eradat · August 2, 2023, 2:02pm

Hi @Frank,

We are trying to setup our Microsoft SQL databases in our production servers.

When we run Achilles using production data we are getting the above error “query processor ran out of internal resources”. When adding the excludeAnalysisIds=c(2004) as a parameter to the Achilles execution, we don’t see the error in the production environment .

We tested Achilles on our test servers, using test data generated from unit testing scripts from Rabbit-In-A-Hat. When we ran Achilles on this test data we didn’t see the error.

Not sure if this is due to the volume of data within the production tables or more a server setup. I have attached a file which provides some details on the CPU, memory and cores used:

Results.xlsx (2.9 KB)

Thank you,
Solmaz

Frank · August 2, 2023, 2:36pm

Ah 2004.

github.com

OHDSI/Achilles/blob/main/inst/sql/sql_server/analyses/2004.sql

-- Analysis 2004: Number of distinct patients that overlap between specific domains
-- Bit String Breakdown:   1) Condition Occurrence 2) Drug Exposure 3) Device Exposure 4) Measurement 5) Death 6) Procedure Occurrence 7) Observation

select distinct person_id into #conoc from @cdmDatabaseSchema.condition_occurrence;
select distinct person_id into #drexp from @cdmDatabaseSchema.drug_exposure;
select distinct person_id into #dvexp from @cdmDatabaseSchema.device_exposure;
select distinct person_id into #msmt from @cdmDatabaseSchema.measurement;
select distinct person_id into #death from @cdmDatabaseSchema.death;
select distinct person_id into #prococ from @cdmDatabaseSchema.procedure_occurrence;
select distinct person_id into #obs from @cdmDatabaseSchema.observation;

with rawData as (
select 2004 as analysis_id,
       CAST('0000001' AS VARCHAR(255)) as stratum_1,
       cast((1.0 * personIntersection.count_value / totalPersonsDb.totalPersons) as varchar(255)) as stratum_2,
       CAST(NULL AS VARCHAR(255)) as stratum_3,
       CAST(NULL AS VARCHAR(255)) as stratum_4,
       CAST(NULL AS VARCHAR(255)) as stratum_5,
       personIntersection.count_value
      from

This file has been truncated. show original

This query can in fact generate the type of issues you are seeing. From your spreadsheet I can’t quite make out the server memory capacity, but I would imagine that is the issue. Can you describe the volume of your data by table in GB and the memory of your server in GB?

solmaz.eradat · August 3, 2023, 9:17am

Thank you @Frank .

Running the script below on the production server:

SELECT physical_memory_kb/1000000.0 as physical_memory_GB,
virtual_memory_kb/1000000.0 as virtual_memory_GB, 
cpu_count, *
FROM [sys].[dm_os_sys_info];

we get

. I have attached a file with the row count and table sizes in GB below:

cdm_tables.xlsx (10.6 KB)

Many thanks,
Solmaz

Frank · August 3, 2023, 5:10pm

According to this you have nearly a TB of physical memory on your server and 2 CPU? This seems odd to me, are you sure that’s the configuration? Is this hosted in Azure or on site?

Your overall database appears to be about 9GB from the table listing, so it is surprising you would have an issue if those memory calculations are correct.

solmaz.eradat · August 23, 2023, 2:59pm

Hi @Frank . We are hosting the database in Azure.

fassez1 · February 27, 2024, 11:38pm

Hi @solmaz.eradat - hope you don’t mind me reaching out! I was browsing the forums, since we are now experiencing the exact issue that is discussed here (running Achilles against Azure SQL). Can I ask if there was anything specific that you did to work through it?

Many thanks!

solmaz.eradat · February 28, 2024, 11:27am

Hi @fassez1. Not at all. Have you first tried testing what you see when adding the paramenter excludeAnalysisIds=c(2004) in the Achilles function?

Thanks,
Solmaz

nghiemt · March 3, 2024, 11:53pm

Hello @solmaz.eradat. I am in @fassez1’s team and the person currently running Achilles.
Yes thanks to your earlier very helpful comment, we did use excludeAnalysisIds=c(2004) to skip over 2004 and Achilles did run to completion.

And I can see with The query processor ran out of internal resources and could not produce a query plan · Issue #679 · OHDSI/Achilles · GitHub that one workaround, if we did want the analysis 2004 numbers, is to manually split up analysis 2004 into multiple CTEs and INSERTS.

We were wondering if you were able to eventually get the original version of the Achilles analysis 2004 script working via an Azure config adjustment. Or is the (split up into multiple CTEs) Workaround the current permanent solution for your site as well?

Thank you,
Nghiem