0

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.

maccettura
  • 10,514
  • 3
  • 28
  • 35
Osayed Zaheda
  • 1
  • 5
  • 2
    Do you know what SQL Injection attacks are? Also, you might want to read up on Stored Procedures. No reason your C# code should have all this going on when you could just call a stored proc with the `@filename` parameter – maccettura Oct 04 '19 at 14:48
  • I'm not really familiar with this, but I *think* what you are doing here is "iterate over every file in this folder and insert it into this table". Given this, why are you surprised it takes longer with more files? Are you running this every time a *single* file gets added? If so, then you're wasting a lot of time... you should just add the one new file shouldn't you? – GPW Oct 04 '19 at 15:13
  • Make the id a primary key so a hash is used when lookup. Lookup time will decrease from N/2 to Log(N). – jdweng Oct 04 '19 at 15:45
  • Yeah, I am running this every time. But I don't think that's what it does, It basically gets the "Images" subdirectory then inserts into it. That's all. – Osayed Zaheda Oct 04 '19 at 15:46
  • @maccettura Sure i'll try that out. – Osayed Zaheda Oct 04 '19 at 15:46
  • @jdweng In default, filetables have path_locator as a primary key so that won't work. – Osayed Zaheda Oct 04 '19 at 16:51
  • the primary key should be set to the field you normally query. How often do you query the database using the path_locator? – jdweng Oct 04 '19 at 17:11
  • Never, just that code above. Do you suggest I change it? @jdweng – Osayed Zaheda Oct 04 '19 at 18:41

1 Answers1

0

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'";
Serkan Arslan
  • 13,158
  • 4
  • 29
  • 44