Performance Problem
greggo
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
Uri Dimant
How big are your tables? Have you tracked an execution plan of the query?
Does the optimizer use indxes?
What if you run SP with all aparameters and compare performance....?
"greggo" <u51702@uwe> wrote in message news:95a2243976b38@uwe...
Erland Sommarskog
> 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.
I can tell why the second query is so much slower. But I can't say
how you should fix it, because I know nada about your application.
> The query in Title + Description:
>
> 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)
In this query, there are presumably good indexes on t0.Name and t2.Name
that can be used.
> and the query in Description + Tags:
>
> 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(
Here you are searching in four different tables, whereof three are
outer-joined. This looks like a difficult case for the optimizer.
It will probably have to table scan it all.
Overall, if performance and scalability is an issue for you, I would
consider to scrap LINQ, at least for the difficult queries. It does not
make it any easier to write good queries, if you have a tool to generate
them.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
greggo via SQLMonster.com
with unions, and this has significantly improved our performance (from 12-15
seconds down to less than a second).
Can you recommend any tools for generating queries?
Thanks,
Greg
Erland Sommarskog wrote:
>> I am having problems when searching my database. I can choose to search
>> in
>[quoted text clipped - 7 lines]
>> Please let me know what other information I can provide to assist with
>> resolving this issue.
>
>I can tell why the second query is so much slower. But I can't say
>how you should fix it, because I know nada about your application.
>
>> The query in Title + Description:
>>
>[quoted text clipped - 5 lines]
>> INNER JOIN [dbo].[Tags] AS [t2] ON [t2].[Id] = [t1].[fkTagId]
>> WHERE ([t1].[fkTaggedEntityId] = [t0].[Id]) AND ([t2].[Name] LIKE @p6)
>
>In this query, there are presumably good indexes on t0.Name and t2.Name
>that can be used.
>
>> and the query in Description + Tags:
>>
>[quoted text clipped - 7 lines]
>> OR ([t3].[Description] LIKE @p7)
>> OR ([t3].[URL] LIKE @p8) OR (EXISTS(
>
>Here you are searching in four different tables, whereof three are
>outer-joined. This looks like a difficult case for the optimizer.
>It will probably have to table scan it all.
>
>Overall, if performance and scalability is an issue for you, I would
>consider to scrap LINQ, at least for the difficult queries. It does not
>make it any easier to write good queries, if you have a tool to generate
>them.
>
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-performance/200905/1
Erland Sommarskog
> Can you recommend any tools for generating queries?
good for cutting the development time for a small project, but as the
system and data grows, it becomes more and more imperative to write the
SQL yourself to get better control.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
WOLO Laurent
Do you have any indexes for description and tags ?
"greggo" <u51702@uwe> a écrit dans le message de news:95a2243976b38@uwe...