I have a system that generates small XML files containing details about specific activities that occur in our facility. These files are about 32.8 KB, so they are fairly small. They are posted to a single staging folder on my server via FTP. I have a VBScript that runs every 5 minutes and imports the contents of the file into a SQL Server database. There are between 350k and 400k files created over a period of about 6 hours, and my goal is to try to process them as fast as possible, with the goal of keeping as close to real time as I can. This means that I need to be able to process a file in 50ms or less. My current average is about 100ms. However, at times, I do achieve speeds as quick as 20ms average over five minutes.
The current method I use is using FSO to read the text into a variable, pull the file create date/time into another variable, then execute a Stored Procedure passing those two variables in as parameters. I've broken the script down to every single detail, and when I analyze the details about 90ms of the normal 100ms processing time is the command "objFSO.OpenTextFile(fsoFile.Path, 1).ReadAll" that reads the file contents into the variable. I've not found any correlation in the server resource monitor that would reflect why the speed of reading the file takes 90ms most of the time, but can take less than 5ms other times. (The Stored Procedure itself takes less than 5ms to execute.)
Any suggestions on either (1) other methods to use in VBScript to import the file, remembering that I also need to pass the File Create Time to the Stored Procedure, or (2) suggestions on how to improve the physical performance of reading these files into memory.
The system these are being processed on is a HP Gen8 server with a 12 TB SAN attached via 10 GB Fiber. The FTP Staging folder resides on the SAN. The server is not running anything other than the FTP server, a web server (very light traffic at this time... still in development), and this VBScript. The FTP also receives about 600k image files with average size of 650kb during the same 6 hour window.