Sql server query using function and view is slower

Question

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 nvarchar(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) = @RRN

Why is the solution with a function and a view going slower?

Answer 1

Check the query execution plan and confirm whether or not the new query is even using the view. If the query doesn't use the view, that's the problem.

---

How does this query fair?

SELECT UserId FROM vwi_Users
WHERE RRN = '59021626919-61861855-S_FA1E11'

---

I see you're freely mixing nvarchar and varchar. Don't do that! It can cause full index conversions (eeeeevil).

Answer 2

the point of the view was to pre-compute the XML value into a regular column. To then use that precomputed value in the index on the view, shouldn't you actually query the view?

SELECT
    UserId
    FROM vwi_Users
    WHERE RRN= '59021626919-61861855-S_FA1E11'

also, make the index this:

CREATE NONCLUSTERED INDEX cx_vwi_Users__RRN ON vwi_Users(RRN) INCLUDE (UserId)

it is called a covering index, since all columns needed in the query are in the index.

Answer 3

Have you tried to add that function result to your table (not a view) as a persisted, computed column??

ALTER TABLE dbo.Users
   ADD dbo.fn_Users_RRN(Data) PERSISTED

Doing so will extract that piece of information from the XML, store it in a computed, always up-to-date column, and the persisted flag makes it physically stored along side the other columns in your table.

If this works (the PERSISTED flag is a bit iffy in terms of all the limitations it has), then you should see nearly the same performance as querying any other string field on your table... and if the computed column is PERSISTED, you can even put an index on it if you feel the need for that.

Answer 4

Scalar functions tend to perform very poorly in SQL Server. I'm not sure why if you make it a persisted computed column and index it, it doesn't have identical performance to a normal indexed-column, but it may be due to the UDF being called even though you think it's no longer needed to be called once the data is computed.

I think you know this from another answer, but your final query is wrongly calling the scalar UDF on every row (defeating the point of persisting the computation):

SELECT UserId FROM Users  
WHERE dbo.fn_Users_RRN(Data) = @RRN 

It should be

SELECT UserId FROM vwi_Users  
WHERE RNN = @RRN