My first post here, so easy on the brickbats, please.
I have a SQL Server database, with FileTables enabled. The number of files is fairly large, roughly 100,000, and some of the summarizing functions I need to perform are unacceptably slow. I have tried all my usual tricks with SQL Server - indexes, materialized views, computed columns, and none of them are permitted on FileTables. What I have come up with as a patch is an asynchronous process to populate a standard table from the FileTables, and that cache table can then be accessed quickly through such optimization techniques. The impulse to update the table I want to get from “Windows Change Notification”, which lets a task sit idle until an event happens, like a change to one of the FileTables, caused by a user adding, deleting or editing a file in a FileTable folder.
Is this event accessible to PowerShell? Would I need to start a PS process that would stall and wait for such a signal? Can I set a PS script to run when such an event happens? I want the PS script to execute a SQL Server stored procedure, which will asynchronously update the cached FileTable data, so that the user is not held up while all this is updated. It does not matter if it takes a while for this update to happen, but it does matter if the user must wait a long time for a response. If the user added or deleted a file and then immediately asked for something that calls for one of these summaries, he might not get the exactly correct count, but that is rather unlikely, and does not matter anyway - the exactly correct count at any particular instant is not crucial.
I know PS and SS can interact pretty nicely, but I have not found any documentation for using “Windows Change Notification” with PS.
I found a brief tutorial from The Company on the subject, but my knowledge of C++ can best be described as non-existent.
https://learn.microsoft.com/en-us/windows/win32/fileio/obtaining-directory-change-notifications