Awesome, thanks. I have great respect for your reputation Jeff. I've followed your posts for many years, and have tried to live my SQL Server life according to the principles of RBAR! 🙂

I do have all the layouts in Excel already. There are basically seven different file types, they are all "fixed field" files. It looks like the file sizes range from as small as 100 bytes, all the way up to 50mb or so. The big ones, the 50MB ones that I'm concerned about have a 364 byte header and 25 field detail records, each about 390 bytes in size. I have used BCP on other assignments, never with a format file though, most of my experience with BCP has been for one time only imports. The problem in this case is that would essentially amount to a rewrite for this system.

Right now a C# app is reading the entire contents of each incoming file and sticking it in a SQL Server binary column, in a table called "Files", then another C# process comes along and processes all the new records in the Files table one at a time. I know you're going to tell me how this is the worst possible choice for importing data files on so many levels, and of course I whole heartedly agree, but this is what I've inherited for now. To give you a little more background, they want me to go add "with nolock" to all the sprocs in this process to try and improve performance, they also wrote a custom scheduler app because they don't like SSIS, so that's the sort of thing I'm up against here.

I will take all your BCP advice and start working on a replacement "proof of concept" for all this when it fails to meet processing time requirements in the not too distant future. In the mean time, unless someone has a better idea, in order to keep this thing afloat, I'm thinking of something along the lines of a scheduled job to keep track of the file count and rebuild stats and/or indexes when the file count exceeds some count/time threshold. Or maybe a trigger on the Files table that keeps track of the file count over time and triggers a stats/index rebuild. I completely understand that this is a hideous process, that said, there must be a better way to get SQL Server to keep up with all these records being added until I can get it re-architected.