I'm excited about the new FILESTREAM object, but it has a hidden flaw that must be discussed! If you start loading 100,000+ files into a table, the performance for inserting rows will continue to slow down to the point where it is crawling (several seconds for a single insert!)
This problem occurs because NTFS is continuing to create 8.3 file names for each file that is created in the FILESTREAM directory.
This is not a SQL command, or configuration issue - but an NTFS known problem that was not addressed when SQL creates the directory.
You can stop NTFS from creating 8.3 filesname, and therefore restore performance!
Here is the command to be issued in the diretory: fsutil behavior set disable8dot3
More information here: http://technet.microsoft.com/en-us/library/bb457112.aspx - scroll down to Optimizing NTFS Performance. - credit to channel9!