I agree that it looks really complex. Especially if get_path__root_folder_id is specified, which I believe is often the case.
This proc has been introduced in the new "pretty url"-aware code in file storage (on the forthcoming 5.0 HEAD), too, so if this function needs to perform better, let's do it 😊.
BTW, Jun, speaking of possible performance issues in the CR ... have you been seeing any performance issue with content_item__get_id(), if you have even used it in bcms, that is? I've started seeing a considerable amount of lack of speed in the new file storage when it comes to resolving an item from its path, i.e., downloading a file by means of the new index.vuh under /view, using content_item__get_id(). It seems to be a bit quicker on Oracle, though.
I am working with FS "pretty urls" for Heidelberg, and Janine is implementing it for Sloan, too, so I have good faith we will work it out soon.
/Ola