Sql server query using function and view is slower
        Posted  
        
            by Lieven Cardoen
        on Stack Overflow
        
        See other posts from Stack Overflow
        
            or by Lieven Cardoen
        
        
        
        Published on 2010-04-14T19:10:37Z
        Indexed on 
            2010/04/14
            19:13 UTC
        
        
        Read the original article
        Hit count: 334
        
I have a table with a xml column named Data:
CREATE TABLE [dbo].[Users](
    [UserId] [int] IDENTITY(1,1) NOT NULL,
    [FirstName] [nvarchar](max) NOT NULL,
    [LastName] [nvarchar](max) NOT NULL,
    [Email] [nvarchar](250) NOT NULL,
    [Password] [nvarchar](max) NULL,
    [UserName] [nvarchar](250) NOT NULL,
    [LanguageId] [int] NOT NULL,
    [Data] [xml] NULL,
    [IsDeleted] [bit] NOT NULL,...
In the Data column there's this xml
<data>
  <RRN>...</RRN>
  <DateOfBirth>...</DateOfBirth>
  <Gender>...</Gender>
</data>
Now, executing this query:
SELECT UserId FROM Users 
WHERE data.value('(/data/RRN)[1]', 'nvarchar(max)') = @RRN
after clearing the cache takes (if I execute it a couple of times after each other) 910, 739, 630, 635, ... ms.
Now, a db specialist told me that adding a function, a view and changing the query would make it much more faster to search a user with a given RRN. But, instead, these are the results when I execute with the changes from the db specialist: 2584, 2342, 2322, 2383, ...
This is the added function:
CREATE FUNCTION dbo.fn_Users_RRN(@data xml)
RETURNS varchar(100)
WITH SCHEMABINDING
AS
BEGIN
      RETURN @data.value('(/data/RRN)[1]', 'varchar(max)');
END;
The added view:
CREATE VIEW vwi_Users
WITH SCHEMABINDING
AS
SELECT UserId, dbo.fn_Users_RRN(Data) AS RRN from dbo.Users
Indexes:
CREATE UNIQUE CLUSTERED INDEX cx_vwi_Users ON vwi_Users(UserId)
CREATE NONCLUSTERED INDEX cx_vwi_Users__RRN ON vwi_Users(RRN)
And then the changed query:
SELECT UserId FROM Users 
WHERE dbo.fn_Users_RRN(Data) = '59021626919-61861855-S_FA1E11'
Why is the solution with a function and a view going slower?
© Stack Overflow or respective owner