Thank you for the suggestion. I will remove those two local variables. I did receive an answer from another source. According to my crude logging, my times dropped from 2.5 seconds per record to .005 seconds per record.
-Vernon
The FileTable path_locator is the primary key so the needed index exists to retrieve the singe row row efficiently. However, using the ToString method makes the expression non-sargable, requiring a scan instead of index seek.
Below is an example that uses the HierarchyId Parse method on the parameter value rather than invoking the ToString method on the path_locator column. This will allow the primary key index to be used efficiently.
ALTER PROCEDURE [dbo].[GetFileTablePath]
(
@FileTableId nvarchar(MAX)
)
AS
BEGIN
DECLARE @root nvarchar(100);
DECLARE @fullpath nvarchar(1000);
SELECT @root = FileTableRootPath();
SELECT @root + file_stream.GetFileNamespacePath() AS fullpath
FROM DocumentStore
WHERE path_locator = HierarchyId::Parse(@FileTableId);
END;
GO