Where can I find "achilles_result_concept_count" table DDL

OHDSI / WebAPI

OHDSI WebAPI contains all OHDSI services that can be called from OHDSI applications

Apache License 2.0

129 stars 167 forks source link

Where can I find "achilles_result_concept_count" table DDL #1882

Closed ParkChanghHyun closed 3 years ago

ParkChanghHyun commented 3 years ago

My Environment is -WebAPI ver.2.80 -Oracle 12.2

Expected behavior

SEARCH and show me the RC&DRC in Atlas .

Actual behavior

It is TOO~much SLOW! and this is show the ERROR

Steps to reproduce behavior

Where can I find "achilles_result_concept_count" table DDL?.

chrisknoll commented 3 years ago

It gets constructed when you run Achilles.

ParkChanghHyun commented 3 years ago

Thanks, I done the Achilles and made "achilles_result_concept_count" table . But still ATLAS "search" is too ~~ slow. When I search "radiation" word, WebAPI 2.78 done just 30 second. But It is more than 30min in WebAPI 2.80.

I think it is need to improve.

chrisknoll commented 3 years ago

yes, 30 minutes is way too long for a search query.

I'm not sure where the slowdown is for your environment: is it the querying of the vocabulary tables or fetching the record counts from the database?

Some things I would try:

Restart WebAPI after the Achilles was run since I believe the caching of the record counts is performed only when WebAPI starts up
Check your databsae configuration by running some of the vocabular queries. YOu can find the vocabulary queries in the sourceccode, but it may be easier if you turn on logging on your MSSQL server and capture the queries that are running so slowly.
Setup SOLR: this will make the searching faster but the row counts are fetched in a separate query. We haven't added setup instructions into the formal wiki, but the instructions can be found here.

ParkChanghHyun commented 3 years ago

@chrisknoll Thanks for your favor. But it is still too slow. I ran Achilles, so I have "achilles_result_concept_count" table .

In my Redshift Environment, some query run for 6 hour. Look at this.

This query ocuurred, when I search "radiation" word in my Atlas.

=>After that's query end, "radiation" word search is very fast. But, when I search others words, it run that's query for a long time.

anthonysena commented 3 years ago

@ParkChanghHyun - just to add some additional ideas/thoughts to this thread: I'd recommend serving your vocabulary from a "traditional" RDMBS as opposed to RedShift. What we have found in our experience is that RedShift (and PDW) will queue queries based on the order in which they are received. Generally, vocabulary queries are fast but they can create a backlog of activity in the overall RedShift queue. Since I see you are using Oracle, you might want to consider a database/schema on that platform to host the vocabulary tables that you can use as your default vocabulary provider. The advantage here is that more intensive queries (i.e. cohort generation) will take advantage of the RedShift hardware without any additional interference from the vocabulary queries.

ParkChanghHyun commented 3 years ago

Thanks @anthonysena !

Thanks to your TIPS, I changed my WepAPI setting like bellow. So it's good working.

ParkChanghHyun commented 3 years ago

But I have problem in Hierarchy searching! I want to find Hierarch search, but it didnt act properly. So how can I debug it's error. In addition, this WEBAPI well working in only oracle setting. (Hierarchy search well work)

chrisknoll commented 3 years ago

The 403 response means authorization error. Do you have security enabled? if so, did you grant permissions to that datasource?

ParkChanghHyun commented 3 years ago

Thanks @chrisknoll.

Like your mentioned, it is well working on disabled security setting in Atlas. But in enabled Atlas setting almost ATLAS function working well ,but only search concept Hierarchy function is didn't work.

this is my solution. So I have two works for "Atlas concept Hierarchy search" function.

insert "webapi.SEC_PERMISSION" table.
insert "webapi.SEC_ROLE_PERMISSION" table.

And then it's working well.

ParkChanghHyun commented 3 years ago

I think it is becaues in Atlas some insert didn't work in Postgres.( In oracle ,I checked the well inserted "ancestorAndDescendant") It should always insert manually? else, it is kind of error?

-my webAPI version is 2.80.