Performance problem

greggo

Hi,

I am having problems when searching my database. I can choose to search in any combination of: Title, Description or Tags. Searching for any combination except Description and Tags gives subsecond response times. However, when searching in Description and Tags (or Title, Description, and Tags) I get 14-15 second query times.

We are using LINQ to generate the queries, and MS SQL Server 2005. Here are the queries generated by LINQ, as seen in SQL Server Profiler. Please let me know what other information I can provide to assist with resolving this issue.



The query in Title + Description:

exec sp_executesql N'SELECT TOP (5) [t0].[Id], [t0].[Type], [t0].[fkOwnerId], [t0].[Name], [t0].[DateAdded], [t0].[DateModified], [t0].[RatingSummary], [t0].[DateRatingCalculated],
[t0].[PopularitySummary], [t0].[DatePopularityCalculated], [t0].[Timestamp], [t0].[RatingCount], [t0].[IsDeleted]
FROM [dbo].[Entities] AS [t0]
WHERE ([t0].[Type] IN (@p0, @p1, @p2, @p3, @p4)) AND (([t0].[Name] LIKE @p5) OR (EXISTS(
    SELECT NULL AS [EMPTY]
    FROM [dbo].[UserEntityTags] AS [t1]
    INNER JOIN [dbo].[Tags] AS [t2] ON [t2].[Id] = [t1].[fkTagId]
    WHERE ([t1].[fkTaggedEntityId] = [t0].[Id]) AND ([t2].[Name] LIKE @p6)
    ))) AND (NOT (EXISTS(
    SELECT NULL AS [EMPTY]
    FROM [dbo].[Entities] AS [t3]
    LEFT OUTER JOIN [dbo].[ContentExts] AS [t4] ON [t4].[fkId] = [t3].[Id]
    LEFT OUTER JOIN [dbo].[UserExts] AS [t5] ON [t5].[fkId] = [t3].[Id]
    WHERE ([t3].[Id] = [t0].[Id]) AND (([t4].[Status] = @p7) OR ([t5].[Status] = @p8) OR ([t5].[Status] = @p9) OR ([t5].[Status] = @p10) OR ([t3].[IsDeleted] = 1))
    )))
ORDER BY [t0].[DateAdded] DESC, [t0].[Name]',N'@p0 int,@p1 int,@p2 int,@p3 int,@p4 int,@p5 nvarchar(6),@p6 nvarchar(6),@p7 int,@p8 int,@p9 int,@p10
int',@p0=3,@p1=4,@p2=5,@p3=6,@p4=1,@p5=N'%linq%',@p6=N'%linq%',@p7=1,@p8=3,@p9=0,@p10=1

and the query in Description + Tags:

exec sp_executesql N'SELECT TOP (5) [t0].[Id], [t0].[Type], [t0].[fkOwnerId], [t0].[Name], [t0].[DateAdded], [t0].[DateModified], [t0].[RatingSummary], [t0].[DateRatingCalculated],
[t0].[PopularitySummary], [t0].[DatePopularityCalculated], [t0].[Timestamp], [t0].[RatingCount], [t0].[IsDeleted]
FROM [dbo].[Entities] AS [t0]
LEFT OUTER JOIN [dbo].[UserExts] AS [t1] ON [t1].[fkId] = [t0].[Id]
LEFT OUTER JOIN [dbo].[GroupExts] AS [t2] ON [t2].[fkId] = [t0].[Id]
LEFT OUTER JOIN [dbo].[ContentExts] AS [t3] ON [t3].[fkId] = [t0].[Id]
WHERE ([t0].[Type] IN (@p0, @p1, @p2, @p3, @p4)) AND (([t1].[Description] LIKE @p5) OR ([t2].[Description] LIKE @p6) OR ([t3].[Description] LIKE @p7) OR ([t3].[URL] LIKE @p8) OR (EXISTS(
    SELECT NULL AS [EMPTY]
    FROM [dbo].[Collections] AS [t4]
    LEFT OUTER JOIN [dbo].[Entities] AS [t5] ON [t5].[Id] = [t4].[fkCategoryId]
    LEFT OUTER JOIN [dbo].[CategoryExts] AS [t6] ON [t6].[fkId] = [t5].[Id]
    WHERE ([t5].[Id] = [t0].[Id]) AND ([t6].[Description] LIKE @p9)
    )) OR (EXISTS(
    SELECT NULL AS [EMPTY]
    FROM [dbo].[UserEntityTags] AS [t7]
    INNER JOIN [dbo].[Tags] AS [t8] ON [t8].[Id] = [t7].[fkTagId]
    WHERE ([t7].[fkTaggedEntityId] = [t0].[Id]) AND ([t8].[Name] LIKE @p10)
    ))) AND (NOT (EXISTS(
    SELECT NULL AS [EMPTY]
    FROM [dbo].[Entities] AS [t9]
    LEFT OUTER JOIN [dbo].[ContentExts] AS [t10] ON [t10].[fkId] = [t9].[Id]
    LEFT OUTER JOIN [dbo].[UserExts] AS [t11] ON [t11].[fkId] = [t9].[Id]
    WHERE ([t9].[Id] = [t0].[Id]) AND (([t10].[Status] = @p11) OR ([t11].[Status] = @p12) OR ([t11].[Status] = @p13) OR ([t11].[Status] = @p14) OR ([t9].[IsDeleted] = 1))
    )))
ORDER BY [t0].[DateAdded] DESC, [t0].[Name]',N'@p0 int,@p1 int,@p2 int,@p3 int,@p4 int,@p5 nvarchar(6),@p6 nvarchar(6),@p7 nvarchar(6),@p8 nvarchar(6),@p9 nvarchar(6),@p10 nvarchar(6),@p11 int,@p12
int,@p13 int,@p14 int',@p0=3,@p1=4,@p2=5,@p3=6,@p4=1,@p5=N'%linq%',@p6=N'%linq%',@p7=N'%linq%',@p8=N'%linq%',@p9=N'%linq%',@p10=N'%linq%',@p11=1,@p12=3,@p13=0,@p14=1

Thanks in advance for any assistance,

Greg

stryk

Hi!

If you run these queries in Management Studio you should check the "Actual Execution Plan". This should show if there are e.g. any Index Scans; if so, you probably need to add some indexes ...

... but: your queries are filtering by using LIKE and Regular Expressions containing wildcards. In this case, SQL Server would mostly start scanning the tables ...

Regards,
Jörg