SQL Server Performance

Speeding up this query

Discussion in 'SQL Server 2008 T-SQL Performance Tuning' started by Martin Dixon, Oct 11, 2011.

  1. Martin Dixon New Member

    Hi, I have a query that is pulling information from tables and populating a search filter. This query is quite slow. I can't actually post the query but I can post a very similar example.

    Code:
    SELECT
    Table1.Name as PlaceName
    Table1.PlaceID
    Table1.VillageID
    MIN(dbo.MasterTable.Price) as Price
    FROM dbo.Table1 WITH(NOLOCK)
    JOIN dbo.MasterTable WITH(NOLOCK) ON dbo.MasterTable.PlaceID = dbo.Table1.PlaceID
    JOIN dbo.OutInfo WITH(NOLOCK) ON dbo.OutInfo.OutID = dbo.MasterTable.OutID
    INNER JOIN
          dbo.Books WITH(NOLOCK) ON dbo.MasterTable.BookID = dbo.Books.BookID INNER JOIN
          dbo.Types WITH(NOLOCK) ON dbo.MasterTable.TypeID = dbo.Types.TypeID
    WHERE Price > 0 and isSearch = 1
    AND (dbo.MasterTable.TypeID = @TypeID OR @TypeID is NULL)
    AND (DATEADD(DAY, 0, DATEDIFF(DAY, 0, dbo.MasterTable.OutMin)) >= @OutMin OR @OutMin IS NULL)
    AND (DATEADD(DAY, 0, DATEDIFF(DAY, 0, dbo.MasterTable.OutMax)) >= @OutMax OR @OutMax IS NULL)
    AND (dbo.MasterTable.PlaceID = @PlaceID OR @PlaceID is NULL)
    AND (dbo.MasterTable.ComType = @ComType OR @ComType is NULL)
    AND (dbo.OutInfo.OutCode = @OutCode OR @OutCode is NULL)
    AND (dbo.MasterTable.GoType = @GoType OR @GoType is NULL)
    AND (dbo.MasterTable.AllowTwo = @AllowTwo OR @AllowTwo is NULL)
    AND (dbo.MasterTable.AllowOne = @AllowOne OR @AllowOwe is NULL)
    AND (dbo.Books.isActive = 1)
    AND (dbo.Types.isActive = 1)
    GROUP BY Table1.Name, Table1.PlaceID, Table1.VillageID
    ORDER BY Table1.Name
    I am simply selecting from a master table, joining three tables based on one column and then filtering them based on if parameters are passed to the stored procedure and grouping them. I can't put it in a view as it uses MIN and WITH(NOLOCK) so can't index it.

    Any other ideas?

    Thanks
  2. FrankKalis Moderator

    Welcome to the forum!
    First of all, the presence of an aggregate like MIN() does not keep you from putting the query into a view. But don£t expect a performance boost just by using a view.
    Next NOLOCK has nothing to do with indexes. It just instructs the engine to ignore any locks on the table(s).

    I would rather check the execution plan and see where most of the time is spend.
  3. Martin Dixon New Member

    Yes, however.
    To index a view you cant use aggregate functions or options in the query.
    I have checked the execution plan and nothing really stands out as a performance hit. Might have to take a closer look.
  4. FrankKalis Moderator

    Hm, if I remember correctly all it needs is to include a COUNT_BIG() in the view definition and then you can index the view even with the aggregate.
  5. Martin Dixon New Member

    Hi,
    I beleive COUNT_BIG() allows you to use ORDER BY or GROUP BY.
    I will wait till I get to the office and check the execution plan
  6. Martin Dixon New Member

    Hi,

    I have looked at the execution Plan and the SQL optimizer says to add an index as it takes 97% of operation time, however when adding this index, it doesnt speed up :(
  7. Luis Martin Moderator

    Did you use DTA?
  8. Martin Dixon New Member

    Sorry, what is DTA?
  9. Luis Martin Moderator

    Data Engine Tuning Advisor.
  10. preethi Member

    Welcome to forums:
    Couple of questions/comments:
    • Do you have indexes on the columns used in JOIN conditions? If not, they are good candidates for indexes
    • Are you passing time component to @OutMIn and @OutMax? If not you can remoe DATEADD, DATEDIFF functions. They improve performance if you have included the columns in any index
    • Most of your conditions are like (Column = @Variable OR @Variable IS NULL). You can change them into IF @variable IS NOT NULL and Column =@Variable. This allows you to improve the execution. Yes, you need to write a whole heap of queries. But it is worth the effort on columns have indexes. (Otherwise you will force table scans on all tables as there is a possibility of reading all the rows when parameter value is null and execution plan need to cater such scenario too)
    • What are the data types used for IsSearch, IsActive and similar columns? If you use bit instead of using "=1" I Prefer "=0x1"
    Hope this helps.
  11. Shehap MVP, MCTS, MCITP SQL Server

    Since no enough details about query execution plan + data entity size , we could come up a wide portfolio of recommendations that might work with you :

    1- Create the following non clustered indexes :

    Create Table1_index1 on Table1 (PlaceID asc, Price asc, isSearch asc)include (Name ,VillageID)

    Create MasterTable_index1 on MasterTable (PlaceID asc, BookID asc, TypeID asc, OutMax asc, OutMin asc, ComType asc, GoType asc, AllowTwo asc, AllowOne asc)include (price)

    create Books_index1( BookID asc, isActive asc)

    create Types_index1(TypeID asc, isActive asc)

    create OutInfo_index1(OutID asc, OutCode asc)

    2- Then , you could apply compression for those indexes and preferred more if to use page compression in most of cases (It might be also row compression which depends on several parameters)

    3- Then Run it again with showing query execution plans and return back for me the highest part for I/O cost + CPU cost.

    Please try the above and let me know the results to continue other possible recommendations

    Note: I assumed Price , isSearch are columns of table1

Share This Page