I have this stored procedure which is accessing a FileTable. This stored procedure is taking between 2 to 2.5 seconds to execute. I am looking for suggestions on how to better optimize this.

Thanks,

-Vernon

USE [BMIPictureDB]
GO
/****** Object:  StoredProcedure [dbo].[GetFileTablePath]    Script Date: 8/31/2015 6:07:37 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetFileTablePath]
(
	@FileTableId nvarchar(MAX)
)
AS
BEGIN
	DECLARE @root nvarchar(100);
	DECLARE @fullpath nvarchar(1000);

	SELECT @root = FileTableRootPath();
	SELECT @fullpath = @root + file_stream.GetFileNamespacePath() FROM DocumentStore WHERE path_locator.ToString() = @FileTableId

	SELECT @fullpath
END

Does @FileTableId need to be nvarchar(max)? 

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

I am not sure. The FileTableId is specified in the SQL Server as a HierarchyID. I did some research in the Sql Server documentation and it states that the HierarchyID can be represented as an nVarChar(4000).  Before I ask for a structure change in the database, will this help speed up the stored procedure versus using a nVarChar(max)?

Thanks,

-Vernon

Thank you so much for your recommendation. It has made a tremendous improvement. I suspect that it may be even faster than I am reporting, but I may be limited in recording the execution speed due to my opening and closing and writing the changes out to a log file. According to my own logging it has gone from 2.5 seconds down to .005 seconds per record retrieved.

I learned a new word today... non-sargable.  Is there a way to find whether an expression is sargable versus non-sargable in the query optimizer? 

Regards,

-Vernon

Normally, if there is a function on the column, that expression is automatically non-sargable and can not be used by an index.