{"action":"created","issue":{"url":"https://api.github.com/repos/OHDSI/CommonDataModel/issues/35","repository_url":"https://api.github.com/repos/OHDSI/CommonDataModel","labels_url":"https://api.github.com/repos/OHDSI/CommonDataModel/issues/35/labels{/name}","comments_url":"https://api.github.com/repos/OHDSI/CommonDataModel/issues/35/comments","events_url":"https://api.github.com/repos/OHDSI/CommonDataModel/issues/35/events","html_url":"https://github.com/OHDSI/CommonDataModel/issues/35","id":175755917,"number":35,"title":"Inefficient SQL Code in Conversion Script","user":{"login":"yuxitian","id":7904590,"avatar_url":"https://avatars0.githubusercontent.com/u/7904590?v=3","gravatar_id":"","url":"https://api.github.com/users/yuxitian","html_url":"https://github.com/yuxitian","followers_url":"https://api.github.com/users/yuxitian/followers","following_url":"https://api.github.com/users/yuxitian/following{/other_user}","gists_url":"https://api.github.com/users/yuxitian/gists{/gist_id}","starred_url":"https://api.github.com/users/yuxitian/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/yuxitian/subscriptions","organizations_url":"https://api.github.com/users/yuxitian/orgs","repos_url":"https://api.github.com/users/yuxitian/repos","events_url":"https://api.github.com/users/yuxitian/events{/privacy}","received_events_url":"https://api.github.com/users/yuxitian/received_events","type":"User","site_admin":false},"labels":[{"id":137859827,"url":"https://api.github.com/repos/OHDSI/CommonDataModel/labels/enhancement","name":"enhancement","color":"84b6eb","default":true}],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":0,"created_at":"2016-09-08T13:38:22Z","updated_at":"2017-06-09T12:58:25Z","closed_at":null,"body":"I've noticed that the conversion script includes parts that potentially (and for me did) can take forever to run and not finish. I'm using the PostgreSQL script. The problem is in the sections in which warnings are added to etl_warnings:\n\n```\nINSERT INTO [TARGET_CDMV5].[SCHEMA].ETL_WARNINGS (WARNING_MESSAGE)\nSELECT 'PROCEDURE_OCCURRENCE: ' || CAST(NUM_INVALID_RECORDS AS VARCHAR) || ' records in the source CDMv4 database have invalid PROCOEDURE_CONCEPT_ID'\nFROM (\n    SELECT COUNT(PERSON_ID) AS NUM_INVALID_RECORDS\n    FROM [SOURCE_CDMV4].[SCHEMA].PROCEDURE_OCCURRENCE\n    WHERE PROCEDURE_CONCEPT_ID NOT IN (\n            SELECT CONCEPT_ID\n            FROM [TARGET_CDMV5].[SCHEMA].CONCEPT\n            WHERE CONCEPT_ID = 0\n                OR STANDARD_CONCEPT = 'S'\n            )\n    HAVING COUNT(PERSON_ID) > 0\n    ) warn;\n```\n\nBecause of the \"NOT IN\" part, this code checks every entry in the cdm4 procedure_occurrence to see if its procedure_concept_id is in the list of all concepts in cdm5. Assuming 100m entries in procedure_occurrence and 1m concepts, we're asking the database to do 100 trillion things. Has anyone run the PostgreSQL script and run into this issue?\n\nInstead we should be calling distinct on the procedure_concept_id in procedure_occurrence because there may only be about 10k unique procedure_concept_ids, then calling the NOT IN on the 1m concepts, generating a much smaller list of concepts, then returning to compare each individual entry of procedure_occurrence. See below:\n\n```\nINSERT INTO [TARGET_CDMV5].[SCHEMA].ETL_WARNINGS (WARNING_MESSAGE)\nSELECT 'PROCEDURE_OCCURRENCE: ' || CAST(NUM_INVALID_RECORDS AS VARCHAR) || ' records in the source CDMv4 database have invalid PROCOEDURE_CONCEPT_ID'\nFROM (\n    SELECT COUNT(PERSON_ID) AS NUM_INVALID_RECORDS\n    FROM [SOURCE_CDMV4].[SCHEMA].PROCEDURE_OCCURRENCE\n    WHERE PROCEDURE_CONCEPT_ID IN (\n      SELECT PROCEDURE_CONCEPT_ID\n      FROM (\n        SELECT DISTINCT(PROCEDURE_CONCEPT_ID)\n        FROM [SOURCE_CDMV4].[SCHEMA].PROCEDURE_OCCURRENCE) ids\n        WHERE ids.PROCEDURE_CONCEPT_ID NOT IN (\n          SELECT CONCEPT_ID\n            FROM [TARGET_CDMV5].[SCHEMA].CONCEPT\n              WHERE CONCEPT_ID = 0\n                  OR STANDARD_CONCEPT = 'S'\n            )\n        )\n    HAVING COUNT(PERSON_ID) > 0\n    ) warn;\n```\n\nThis second script I was able to run overnight and finish (compared to stalling for days). There are multiple sections of the script that add to etl_warnings. Maybe they should be changed?\n"},"comment":{"url":"https://api.github.com/repos/OHDSI/CommonDataModel/issues/comments/307381530","html_url":"https://github.com/OHDSI/CommonDataModel/issues/35#issuecomment-307381530","issue_url":"https://api.github.com/repos/OHDSI/CommonDataModel/issues/35","id":307381530,"user":{"login":"clairblacketer","id":12911960,"avatar_url":"https://avatars0.githubusercontent.com/u/12911960?v=3","gravatar_id":"","url":"https://api.github.com/users/clairblacketer","html_url":"https://github.com/clairblacketer","followers_url":"https://api.github.com/users/clairblacketer/followers","following_url":"https://api.github.com/users/clairblacketer/following{/other_user}","gists_url":"https://api.github.com/users/clairblacketer/gists{/gist_id}","starred_url":"https://api.github.com/users/clairblacketer/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/clairblacketer/subscriptions","organizations_url":"https://api.github.com/users/clairblacketer/orgs","repos_url":"https://api.github.com/users/clairblacketer/repos","events_url":"https://api.github.com/users/clairblacketer/events{/privacy}","received_events_url":"https://api.github.com/users/clairblacketer/received_events","type":"User","site_admin":false},"created_at":"2017-06-09T12:58:25Z","updated_at":"2017-06-09T12:58:25Z","body":"@yuxitian would you like to fix this? If you create a pull request I will merge it.\r\n"}}
{"actor":{"display_login":"clairblacketer"}}