July 2, 2015 at 1:43 pm
I have a filetable that contains a binary file. I need to do a selective read of the file stored in the file table. I can write a C# CLR function that will open the file, read n bytes the from a starting byte. Or I can write a SQL statement that reads the stream in the filetable into a VARBINARY variable using SUBSTRING beginning at the starting byte (offset from 1) for the same n bytes.
Both give me the same result. However, the SQL statement takes considerably longer to read. I know there is overhead in reading through SQL (interpreted language), but the difference in performance is substantial, and I can only attribute this performance degradation if SQL first tries to "load" the entire stream before it identifies the portion of the stream that it needs to read beginning at the starting byte offset.
I wonder if this is the case or if there is another option to read a stream from a filetable directly through SQL queries that is more efficient.
Thanks in advance.
July 2, 2015 at 2:23 pm
N_Muller (7/2/2015)
I have a filetable that contains a binary file. I need to do a selective read of the file stored in the file table. I can write a C# CLR function that will open the file, read n bytes the from a starting byte. Or I can write a SQL statement that reads the stream in the filetable into a VARBINARY variable using SUBSTRING beginning at the starting byte (offset from 1) for the same n bytes.Both give me the same result. However, the SQL statement takes considerably longer to read. I know there is overhead in reading through SQL (interpreted language), but the difference in performance is substantial, and I can only attribute this performance degradation if SQL first tries to "load" the entire stream before it identifies the portion of the stream that it needs to read beginning at the starting byte offset.
I wonder if this is the case or if there is another option to read a stream from a filetable directly through SQL queries that is more efficient.
Thanks in advance.
Have you investigated the FILESTREAM feature? I don't know much about it, but what I heard suggests it might be a good candidate. Let me know and we can both learn something...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 2, 2015 at 2:46 pm
Filetable is built on top of filestream so, in principle, there shouldn't be much of a difference. The main difference is that filetables are accessible by other windows applications as a file on a folder, while a varbinary filestream column in a table is only directly accessible via SQL. At least that's my understanding.
July 3, 2015 at 3:39 am
Hi,
FILESTREAM by design will perform better with C# or any other language that uses OS API to access to the data. As you said, T-SQL is an interpreted language but also it uses the whole SQLOS storage APU to access to FILESTRAM files instead just the OS API.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply