|
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. |
|
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!). 1
If you're running on Windows, it's quite easy to use file notification handles with APIs like FindFirstChangeNotification. Here's a simple sample:
(02 Jul '13, 09:50)
Volker Barth
1
Thanks Mark and Volker, I will investigate the Directory Change Notifications and see how I can make that work as it seems a more elegant solution. For now I have declared multiple directory servers with a three directory depth. Each with its own proxy table. I then list the proxy table name in a column on the interface table so as the application rolls through the active interfaces it can build a dynamic sql call. The event will load any file listings in the specified table/directory into a processing table. The main reason for declaring the Directory Access server at a much higher level was to allow for only one table name to query from. By creating multiple servers and tables, and then storing the table name on the interface that uses it I can get around the single versus multiple table name issues as well as gaining back the speed from a much shallower call to the windows information. If you see any issues with this please let me know. Thanks again this forum is always a wealth of information for me.
(08 Jul '13, 09:24)
trexco
|
