1
\$\begingroup\$

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.

\$\endgroup\$
  • \$\begingroup\$ dba.stackexchange.com may be a better fit for this. \$\endgroup\$ – Dan Lyons Oct 31 '13 at 19:02
  • \$\begingroup\$ the SQL Part yes, but the C# stuff probably not @DanLyons \$\endgroup\$ – Malachi Nov 1 '13 at 14:38
  • 1
    \$\begingroup\$ 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. \$\endgroup\$ – Dan Lyons Nov 1 '13 at 17:42
  • \$\begingroup\$ 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. \$\endgroup\$ – Juliano Nov 1 '13 at 19:53
1
\$\begingroup\$

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.

\$\endgroup\$
  • 1
    \$\begingroup\$ 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. \$\endgroup\$ – Mathieu Guindon Nov 17 '13 at 2:55

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.