August 31, 2015 at 9:30 am
I have a FileTable as part of an application that a client uses. In general the response of the SQL Server has been great. However, they have started to make use of a part of the application that handles images. The application has a FileTable set up. However, there is a Stored Procedure that retrieves the path of the requested file. It is taking the Server over two seconds to return a single file path. Unfortuantely they do not have a DBA, but as the current Application Developer, I am trying to help them better optimize the speed of the application. Any ideas on how to speed this up is greatly appreciated.
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
August 31, 2015 at 12:48 pm
The slowness must be in one of the functions. We would need to see the function code to tune those.
But you can get rid of the local variables for a minor performance gain:
USE [BMIPictureDB]
GO
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetFileTablePath]
(
@FileTableId nvarchar(MAX)
)
AS
BEGIN
SELECT FileTableRootPath() + file_stream.GetFileNamespacePath()
FROM DocumentStore
WHERE path_locator.ToString() = @FileTableId
END
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
August 31, 2015 at 7:23 pm
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply