Metadata query performance optimization

Question

SQL Server 2008 R2 + .NET 4.5:

I have the following metadata table:

CREATE TABLE [dbo].[Metadata](
    [Id] [uniqueidentifier] NOT NULL,
    [ContentId] [uniqueidentifier] NOT NULL,
    [ContentType] [varchar](50) NOT NULL,
    [ContentDate] [datetime] NOT NULL,
    [MetaName] [varchar](50) NOT NULL,
    [MetaValue] [varchar](max) NOT NULL,
 CONSTRAINT [PK_Metadata] PRIMARY KEY NONCLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Upon which I make the following query (linqpad friendly):

var Repository = Metadatas;
        var queryModel = new {
            Filters = new[] {new {Field = "departamento", Value = "maquiagem"}},
            Ordering = new[] {new {Descending = true, Field = "date"}},
            Skip = (int?)null,
            Limit = (int?)null
        };

// first we group metadata by content
var grouped = Repository.GroupBy(x => x.ContentId, y => y);

// then filter by user defined filters
foreach (var filter in queryModel.Filters)
{
    var localFilter = filter;
    grouped = grouped.Where(x => x.Any(m => m.MetaName.ToLower() == localFilter.Field.ToLower() && m.MetaValue == localFilter.Value));
}

foreach (var orderBy in queryModel.Ordering)
{
    var field = orderBy.Field;
    if (orderBy.Descending)
    {
        grouped = grouped.OrderByDescending(x => x.FirstOrDefault(m => m.MetaName == field).MetaValue);
    }
    else
    {
        grouped = grouped.OrderBy(x => x.FirstOrDefault(m => m.MetaName == field).MetaValue);
    }
}

// skip
if (queryModel.Skip.HasValue)
{
    grouped = grouped.Skip(queryModel.Skip.Value);
}

// limit
if (queryModel.Limit.HasValue)
{
    grouped = grouped.Take(queryModel.Limit.Value);
}

// get matching content
var contentIds = grouped.Select(x=>x.Key);

// Retrieve all metadata from matching content
var metadata = Repository.Where(x=>contentIds.Contains(x.ContentId)).ToList();

// group metadata them by content id and return
var result = metadata.GroupBy(x => x.ContentId).Select(x => new Tuple<string, List<Metadata>>(x.Key.ToString(), x.ToList())).ToList();

result.Dump();

An example resulting query upon calling this method:

exec sp_executesql N'
SELECT 
[meta1].[Id] AS [Id], 
[meta1].[ContentId] AS [ContentId], 
[meta1].[ContentType] AS [ContentType], 
[meta1].[ContentDate] AS [ContentDate], 
[meta1].[MetaName] AS [MetaName], 
[meta1].[MetaValue] AS [MetaValue]
FROM [dbo].[Metadata] AS [meta1]
WHERE  EXISTS (SELECT 
    1 AS [C1]
    FROM   (SELECT [Distinct1].[ContentId] AS [ContentId]
        FROM ( SELECT DISTINCT 
            [Extent2].[ContentId] AS [ContentId]
            FROM [dbo].[Metadata] AS [Extent2]
        )  AS [Distinct1]
        WHERE  EXISTS (SELECT 
            1 AS [C1]
            FROM [dbo].[Metadata] AS [Extent3]
            WHERE ([Distinct1].[ContentId] = [Extent3].[ContentId]) AND ((LOWER([Extent3].[MetaName])) = (LOWER(@p__linq__0))) AND ([Extent3].[MetaValue] = @p__linq__1)
        ) ) AS [Filter2]
    OUTER APPLY  (SELECT TOP (1) [Extent4].[Id] AS [Id]
        FROM [dbo].[Metadata] AS [Extent4]
        WHERE [Filter2].[ContentId] = [Extent4].[ContentId] ) AS [Limit1]
    WHERE [Filter2].[ContentId] = [meta1].[ContentId]
)',N'@p__linq__0 nvarchar(4000),@p__linq__1 varchar(8000)',@p__linq__0=N'author',@p__linq__1='Janeane Garofalo'

The table is currently populated with 18522 rows, and searching applying one filter and one orderby on the QueryModel takes 140ms to return. It's only 132 rows/ms. I need to improve this time.

The execution plan showed me a full table scan was being made on MetaValue.

I tried adding a fulltext index on the MetaValue field and using FREETXT(), the reads were reduced by 1/3, but duration kept the same. I don't want freetext anyway, I need the full value of the MetaValue to match the filter.

This made me think that reducing the MetaValue from varchar(max) to varchar(something_that_fits_in_an_index) will not help reduce the duration. SQL Server says maximum index size is 900 bytes, and MetaValue right now must hold keys that are 1700 characters long. Could grow in the future.

I have given some thought about downloading the whole table to memory, it's around 10-20mb right now, but I know that will not scale.

I imagine using Solr/Lucene is probably faster, but the application deploy is pretty simple right now and it's better to keep it that way.

I'm open to suggestions.

Answer 1

I came up with a solution, if someone has any better suggestions or any way to optimize my solution, I'm open to giving the answer to them.

Since I needed the full value of MetaValue to match the filter, and MetaValue was not indexable because it was so big, my solution was to hash MetaValue and put it in another column.

alter table Metadata add MetaValueHash uniqueidentifier

Then I indexed MetaValueHash and filtered by it, instead of MetaValue.

The query that had 140ms duration came down to 7ms.

Problem solved, I only wish Sql Server would do this automatically.