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,
(
[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

// group metadata them by content id and return

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]
WHERE  EXISTS (SELECT
1 AS [C1]
FROM   (SELECT [Distinct1].[ContentId] AS [ContentId]
FROM ( SELECT DISTINCT
[Extent2].[ContentId] AS [ContentId]
)  AS [Distinct1]
WHERE  EXISTS (SELECT
1 AS [C1]
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]
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.

• dba.stackexchange.com may be a better fit for this. Oct 31, 2013 at 19:02
• the SQL Part yes, but the C# stuff probably not @DanLyons
– Malachi
Nov 1, 2013 at 14:38
• Since this looks like an EAV table, I suspect the root cause for the performance issue is sub-optimal database design (hence my suggestion for dba stackexchange). It would be helpful to add some information about what the data looks like in the table. It might seed ideas for modifying the EF code, adding indexes, or (in a worst case) showing that the schema needs restructuring. Nov 1, 2013 at 17:42
• Does DBA accept have this kind of review-ish question? I came here because it's the place for less general questions, like this one. Nov 1, 2013 at 19:53

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.

• Maybe it was a dba.stackexchange question after all :) C#-wise I think some of your foreach blocks can be shortened to linq expressions, if you don't find the fluent linq syntax hard to read. Nov 17, 2013 at 2:55