Fastest Way to Read Small XML File Contents Into a Variable in VBScript


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.


John ParkerService Quality ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David Johnson, CD, MVPOwnerCommented:
Can you not consolidate your xml files.. the file open command is a relatively slow (read expensive) process. Less files with more data per file will dramatically improve performance.
John ParkerService Quality ManagerAuthor Commented:
Unfortunately no...  They are a product of a vendor system (its a parcel scanning system that runs a routine for each parcel scanned... the XML is generated at the end of that routine.)

I'm thinking that it's probably hardware / resource related...  I'm not a server hardware expert, so I'm not sure if there are things I can do to either troubleshoot the timing, or better tune the system to perform this read faster.  Like I said, the times that the files process in 20ms, and the times they process in 100ms are fairly erratic, and I cant tie it to any other activity going on with the server or SAN to be able to point at something that is being a drag on the system...  Hence my reason for reaching out to any experts...  :-)
Jeff DarlingDeveloper AnalystCommented:
Since you cannot combine the files, then maybe you can create a number of batches that run in parallel?  

I don't have the end to end solution for you, but I'm curious to see what experts think about this suggestion.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
John ParkerService Quality ManagerAuthor Commented:
Jeff, multi-threading presents some possibilities.  If disk read is the bottle-neck though, then running multiple threads simultaneously might not have any positive effect.  It's worth a look though...  Multi-threading in vbscript isn't easy, but it can be done... In my case, I am being very careful with the management of the files being processed, as well as the time the script is running (I hard cap the run at 4 mins, 55 secs), but I think I could effectively run multiple threads with a little SQL Server backend to manage what scripts are running, what files are being processed, and when each "thread" finishes so that I don't get cases of hanging scripts hogging resources.  I'll play around with it and respond with what I find.  Might be later in the week though...
Seth SimmonsSr. Systems AdministratorCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.