4

I'd like to convert columns into rows for a given SQL Server table. The table effectively is an audit table; it stores a given old and new value in separate columns for a given user attribute (e.g. first name, middle name, last name, home number).

I've had success doing so with UNPIVOT and CROSS APPLY, respectively. However, I was hoping to improve performance for a large set of records (>500k) and all in one query. The table has a primary clustered index; however, according to the execution plan, for UNPIVOT or CROSS APPLY, both are performing a Nested Loop (Left Outer Join), at a 31% cost. Again, it's not like the query is agonizingly slow, but I'd like to inch out every bit of performance I can achieve.

My thought process was, OK, maybe if I can create an indexed view, the query will run very fast.

The problem is SQL Server indexed views (materialized views in Oracle-speak) have so many restrictions such as UNPIVOT and CROSS APPLY (see the full list here). Since CROSS APPLY is effectively an INNER JOIN and joins are acceptable for indexed views, maybe that's at least one good alternative. Even so, I'm having some difficulty rewriting CROSS APPLY as an INNER JOIN.

Here is the table (I'm showing 6 records to simplify but imagine it's 500K or more records):

UserAuditTbl

RowID     UserID      UserAttribute     OldValue     NewValue
1         ID00184     First Name        John         Jon
2         ID00184     Last Name         NULL         Albert
3         ID00185     Home Phone        555-555-1122 555-555-1212
4         ID00188     Middle Name       Jesse        James      
5         ID00188     Cell Phone        555-555-1234 555-555-1555
6         ID19594     Zip Code          00000        90210

With CROSS APPLY, the query would look like:

SELECT 
   RowID 
  ,UserID 
  ,Column 
  ,Value
FROM
  UserAuditTbl
CROSS APPLY (
  VALUES ('OldValue', OldValue), 'NewValue', NewValue)
) x (Column, Value)
WHERE
  Value IS NOT NULL

I guess the first question is can I convert columns into rows such that the underlying query can be incorporated into an indexed view? And the second question is if it's best just to avoid indexed views, then what are my options here? ETL to a new table via SSIS or trigger?

Thank you for your time.

  • 3
    An indexed view is not a magic, "make-any-query-faster" solution. The most effective use case is when you are eliminating a lot of the I/O at read time, and paying for those writes at write time. Usually the best improvement is aggregation, and obviously you'll do better when you are reading the data at a much higher frequency than writing. You could potentially gain here by materializing the data yourself, but this may be quite tedious and could limit concurrency. Have you looked at the plans to see if maybe indexes on the base table could help, instead of silver bullets? – Aaron Bertrand Jul 2 '18 at 19:09
  • Thank you. Yes, I have actually. Provided user enters search terms, the base table should perform admirably. I should have stated that I'm looking to improve open-ended query search (that is, one without any search terms, querying all columns and rows), not shaving off minutes but perhaps 10s of seconds. My thought process was 31% table scan converted to an index may save some query time, eking out more performance. Writing to the base table is infrequent. There's far, far more reads on the base table. Thank you again for your insights! – user3621633 Jul 2 '18 at 19:20
  • 1
    A non-clustered covering index may help with the I/O, but an indexed view is going to be no better - again, if the view is not eliminating rows, it can't possibly be any more efficient than an equivalent non-clustered index. – Aaron Bertrand Jul 2 '18 at 19:26
  • You're right. That is true, but I was under the impression that nonclustered indexes are not applied to queries which return all columns and rows. That is, if you do not specify any conditions in the WHERE clause, the SQL engine should default to the primary clustered index. Or, am I gravely mistaken here? – user3621633 Jul 2 '18 at 19:37
  • 1
    If the non-clustered index is skinnier than the clustered index, and you only retrieve columns in the index, it will choose the non-clustered index. This is very easy to prove. In fact it will even typically choose the non-clustered index when it includes the same columns as the clustered index (e.g. all the columns in the table). And if you don't believe it or trust it, you can use an index hint or force a plan that does so. – Aaron Bertrand Jul 2 '18 at 19:38
1

Based on your description, I think it's fairly unlikely that an indexed view is going to be your best solution. The indexed view will add overhead each time you write to UserAuditTbl, and a CROSS APPLY that unpivots each row into two rows should be quite efficient if you are careful to first seek and/or filter to only the necessary rows (e.g., only those rows for a (UserID, UserAttribute) tuple you are interested in).

If you post a full example of your exact table (including indexes), a script to fill your table to the expected number of rows with fake data, and the exact T-SQL you need to optimize, we might be able to provide more specific suggestions.

However, if you decide to try the indexed view approach, one fairly easy way to implement your CROSS APPLY as a join is to create a table that contains one row per value in your CROSS APPLY, then join to that table. For example:

Create the necessary tables

/* Your sample data */
CREATE TABLE dbo.UserAuditTbl (
    RowID INT NOT NULL IDENTITY(1,1)
        CONSTRAINT PK_UserAuditTbl PRIMARY KEY,
    UserID VARCHAR(10) NOT NULL,
    UserAttribute VARCHAR(100) NOT NULL,
    OldValue VARCHAR(100) NOT NULL,
    NewValue VARCHAR(100) NOT NULL
)
INSERT INTO dbo.UserAuditTbl (UserID,UserAttribute,OldValue,NewValue)
VALUES  ('ID00184','FirstName','John','Jon'),
        ('ID00184','LastName','NULL','Albert'),
        ('ID00185','HomePhone','555-555-1122','555-555-1212'),
        ('ID00188','MiddleName','Jesse','James'),
        ('ID00188','CellPhone','555-555-1234','555-555-1555'),
        ('ID19594','ZipCode','00000','90210')
GO

/* Create a two-row table to implement the CROSS APPLY as a join */
CREATE TABLE dbo.OldAndNewValues (
    OldOrNewValue CHAR(8) NOT NULL
        CONSTRAINT PK_OldAndNewValues PRIMARY KEY
)
INSERT INTO dbo.OldAndNewValues (OldOrNewValue)
VALUES  ('OldValue'),
        ('NewValue')
GO

Create the indexed view

/* Create a view that implements the cross apply as a join */
CREATE VIEW dbo.UserAuditIndexedView WITH SCHEMABINDING AS
SELECT u.RowID,
    u.UserID,
    u.UserAttribute,
    v.OldOrNewValue AS Col,
    IIF(v.OldOrNewValue = 'OldValue', u.OldValue, u.NewValue) AS Val
FROM dbo.UserAuditTbl u
CROSS JOIN dbo.OldAndNewValues v
WHERE IIF(v.OldOrNewValue = 'OldValue', u.OldValue, u.NewValue) IS NOT NULL
GO

/* Create the view index.
    You can adjust the column order as necessary for your audit queries.
    Note that the first index on a view must be a unique index,
    so the RowID and Col columns must be used. */
CREATE UNIQUE CLUSTERED INDEX UQ_UserAuditIndexedView
ON dbo.UserAuditIndexedView (UserID, UserAttribute, RowID, Col)
GO

Confirm that the results match

/* Use the view index */
SELECT *
FROM dbo.UserAuditIndexedView WITH (NOEXPAND)
ORDER BY RowID, Col

/* An adapted version of your original query (the original does not compile) */
SELECT RowID, UserID, UserAttribute, Col, Val
FROM dbo.UserAuditTbl
CROSS APPLY (
  VALUES ('OldValue', OldValue), ('NewValue', NewValue)
) x (Col, Val)
WHERE Val IS NOT NULL
ORDER BY RowID, Col
GO
  • This is most excellent! Thank you so much. At the very least, it's an alternative solution if it does not improve performance for this use case, it can be adapted for other problems for myself or anyone who views this question. I very much appreciate your time and help. I'll give it a go and also work on procuring an exact example. Thank you! – user3621633 Jul 2 '18 at 19:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.