I have created a directory access server on my production server. I would like a database event to poll the directory access server to see if any files have been downloaded for processing. I can make all of this work, but the response time is pretty poor.
I did not expect it to be extremely quick since the directory the DAS is monitoring is on a different box, and we are searching the file name with 'like' expressions and wild cards. However it is pretty poor.
I could modify the DAS to not go as deep into the file layout I guess (currrently it is set for 8 levels) and just create more of them if I need to get more granular. I was hoping that there might be a way to index the table, but since it is a proxy table I am thinking not.
select substr(file_name,35+1,100),1 from DBA.ebill_retro_files where file_name like 'ALPHARetroSpectrackInbound'|| '%.' || 'DAT' and file_name not like 'ALPHARetroSpectrackInbound%%' and permissions not like 'd%';
I guess another option might be to implement some type of java call to check the directories, but I am unsure of how to do that. If anyone had some examples of how to do that that would be great.
Thanks for any ideas.
asked 01 Jul '13, 19:08
You are correct, proxy tables cannot be indexed since the contents of the 'table' are outside of the database and therefore can change without the database server knowing.
Remember that when you do a SELECT from the proxy table all of the rows from the 'table' must be retrieved from the table and filtered according to your WHERE clause. In your case the table is on another computer and hence the eight levels of directories must be accessed remotely. To do this each directory is first scanned, then each entry is 'stat'ed to get the permissions, size, etc. and then if the entry is a directory then it must be scanned, etc. As you can imagine this can result in thousands or millions of remote calls to the remote system - i.e. at least one remote call per entry and at least two per directory.
If you are looking for performance then I would not be monitoring the directory tree from a remote computer. Either cut down on the number of files/directories that need to be polled, or better yet, I would recommend setting up a process on the remote computer that monitors (polls) the directory tree and then notifies the database server of the changes (or both!).
answered 02 Jul '13, 09:35