SQL Server Performance

Performace problem

Discussion in 'SQL Server 2008 Performance Tuning for DBAs' started by greggo, May 5, 2009.

  1. greggo New Member

    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]. LIKE @p8) OR (EXISTS( SELECT N...1 Thanks in advance for any assistance, Greg
  2. Luis Martin Moderator

    Welcome to the forum!.
    Take a look of execution plan in Management Studio. May you need some indexes.
    If you don´t now how to do that, please let me now.
  3. yoavm New Member

    taker into consideration:
    data type of the colunms.
    like operator
    size of tables
    indexes, indexes and indexes
  4. satya Moderator

    Welcome to the forums.
    You you have multiple levels of overhead when it comes to identify the actual performance problem, one is DYNAMIC SQL & another LINQ. As you may be aware that this LINQ to SQL makes querying a SQL Server database as if it was an object and eliminate the need to write very complex SQL statements whenever necessary.
    When I see the query that is displayed below, there are multiple JOINs involved which are prone to performance hit as it needs to identify the correct plan to get the results. Normally there is some business processing so the DLinq code is only a portion, maybe even a small portion of the total processing time. Normally you have to connect to a database over the network and maybe even read the data off disk, again that reduces the portion of time you spend waiting for DLinq.
    It is also an OR/M (object-relational mapping) implementation that allows you to model a relational database using .NET classes. You can then run queries in the database as well as perform insert, update and delete actions to it.
    Another identification that I want to know what kind of information you want to search within the database, you have INFORMATION_SCHEMA views that are installed by default and you could get such information too.
  5. greggo New Member

    Hi,
    Thanks for the suggestions so far. We have made some improvements by forcing linq to use union instead of some of the outer joins. This has cut down the time from 12-15 seconds to under a second.
    This is for a social bookmarking website, and we are trying to find the search terms in certain fields. From there we either grab the 5 most recent bookmarks added, or the top tags from all results. There are indicies on fields like Title, but I am not sure if they are helping since we are doing LIKE queries.
    My biggest query right now is why queries that return few results take longer than queries with large result sets. Is UNION or JOIN slow when working with empty tables?
    I am also wondering if it is possible to use LINQ to call a sproc which returns multiple result tables, and retrieve information out of all of the tables.
    Thanks again,
    Greg
  6. RickNZ New Member

    It might help if you could post your schema as well, but a common reason for such a big jump in execution time is because the query needs to do a table or index scan.
    The clause [t1].[Description] LIKE @p5, which is present in the second query but not the first, will definitely require a scan of the UserExts table, since @p5 = N'%linq%'. You might be able to mitigate the issue by using fulltext search. With a %string% pattern, unfortunately indexes don't usually help (much).

Share This Page