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: 280

Filed under:
|
|

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

Related posts about sql-server

Related posts about database