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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy