Inserting into FileTable gets slower as files increase

Question

I am using FileTables in MSSQL 2017 to save & load images, but whenever the images in the FileTable get high in numbers the increase query takes longer to execute. I disabled 8.3 generation names just in case it's the problem.

Here is my Insert query:

string query = "DECLARE @path HIERARCHYID";
query += " DECLARE @new_path VARCHAR(675)";
query += " SELECT @path = path_locator";
query += " FROM Images";
query += " WHERE name = 'Images'";
query += " SELECT @new_path = @path.ToString()";
query += " + CONVERT(VARCHAR(20), CONVERT(BIGINT, SUBSTRING(CONVERT(BINARY(16), NEWID()), 1,6))) + '.'";
query += " + CONVERT(VARCHAR(20), CONVERT(BIGINT, SUBSTRING(CONVERT(BINARY(16), NEWID()), 7,6))) + '.'";
query += " + CONVERT(VARCHAR(20), CONVERT(BIGINT, SUBSTRING(CONVERT(BINARY(16), NEWID()), 13,4))) + '/'";
query += " INSERT into Images (stream_id, file_stream, name, path_locator) ";
query += "  values (NEWID(), @File, '" + filename + "', @new_path)";

The FileTable name is "Images", and when I insert images into a subdirectory called "Images" as you can see above.

If you think there's a way to make my insert query faster please do tell me.

Answer 1

I think this part of code can cause performance problem related to the row count.

query += " SELECT @path = path_locator";
query += " FROM Images";
query += " WHERE name = 'Images'";

you only need one row and you can add TOP 1 for this

query += " SELECT TOP 1 @path = path_locator";
query += " FROM Images";
query += " WHERE name = 'Images'";