Hello all,
I am running eMERGE type 2 diabetes algorithm on our local Atlas but failed (Cohort #1662611 PheKB-Type 2 Diabetes (T2DM)-GH from the public Atlas). I also exported the sql server query to run directly on our local database, it got error “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…”.
Can anyone help run this cohort in your local Atlas instance and help us to figure out what’s the problem?
The algorithm definition is available from https://phekb.org/phenotype/type-2-diabetes-mellitus
@ericaVoss hi Erica, Karthik mentioned that you are very knowledgeable of Atlas cohort implementation. Can you help with running this cohort and advise if the cohort is constructed correctly?
Thank you all very much.
Best regards,
Sunny
Sunny - I imported the definition to our local Northwestern instance and got the same error. I’ll do a little digging and see if I can figure out how to make it run.
@sunnyshang or @lrasmussen - could one of you point me on how to find your ATLAS cohort implementation from this site?
Tagging @Chris_Knoll because he’ll probably be the one who ends up having the answer here - but I will try to help too!
I point you to this post. Thank you for your help. --Sunny
Thank you, Luke.
@ericaVoss from Sunny’s description I grabbed the JSON definition from http://www.ohdsi.org/web/atlas/#/cohortdefinition/1662611 (@sunnyshang - let me know if this is not the right one) and imported it to my instance.
This is interesting. If I go to Export -> SQL -> MSSQL Server, copy and paste the SQL code and replace the variables, the query runs without any problem.
Grabbed the cohort and I’m trying to run on our system. I’ll let you know what happens when I get back online later.
I just ran it on a version pointing to a Postgres instance with 5M patients and it completed. I’m wondering if it’s an issue with sql server…
It ran in our internal ATLAS fine.
The above reads to me more like a DB issue. It TEMP full? Is the box busy trying to run other queries?
@Chris_Knoll, @Frank, or @anthonysena - seen this before?
@ericaVoss - what DB did you try it against?
@cukarthik - It definitely is specific to SQL Server, but still not sure the underlying cause
I can say that ours is a very fresh install of the OHDSI stack on SQL Server, and it is isolated so that no other queries were running at the time. I’m still curious why the generated SQL ran for me, but not @sunnyshang, yet we are both seeing the error when running from OHDSI. When run from the app do we have to factor in the SQL Server Java driver as a possible source of the error? I’m not convinced yet if that’s a valid lead.
@lrasmussen - Through ATLAS to a MS APS.
I have a copy of SYNPUF on MSSQL, running it there now, but it is taking awhile. I’ll let you know when it finishes.
Yea, I’m thinking that is the case. I haven’t tested the ohdsi stack against Postgres since we have not set that up. We were getting the same error against SQL Server both directly hitting the dB and via the ohdsi stack, which makes sense. We tried the query on two other sql server instances and experience the same thing, and one of the instances is new so their is no usage on it right now.
I got 518,981 rows in my #FINAL_COHORT table
Hi, Everyone. Sorry for the late reply. Usually when I get an error, I do copy the SQL and run it as you guys did. If it runs OK locally but not through Atlas, it’s possible that there is a configuration error in your WebAPI’s SOURCE and SOURCE_DAIMON table such that you possibly have the wrong table qualifiers set up.
We also have a field in the WebAPI’s cohort_generation_info table called ‘fail_message’ which can tell you what the error was in generation:
select fail_message from {webapiSchema}.cohort_generation_info where id={cohortDefinitionId}
Run that on your WebAPI database and if it failed, then you should see an error message.
-Chris
To add to @Chris_Knoll’s post: it would be helpful to know which version(s) of SQL Server are in use amongst the folks on this thread @ericaVoss, @cukarthik, @lrasmussen, @sunnyshang
We are running SQL Server 2012 enterprise. The strange thing is that our Postgres instance isn’t as well resourced as our SQL Server machines and it completed.
SQL Server 2016 Standard for me