Speeding up this query | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Speeding up this query

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.
Table1.Name as PlaceName
MIN(dbo.MasterTable.Price) as Price
JOIN dbo.MasterTable WITH(NOLOCK) ON dbo.MasterTable.PlaceID = dbo.Table1.PlaceID
JOIN dbo.OutInfo WITH(NOLOCK) ON dbo.OutInfo.OutID = dbo.MasterTable.OutID
      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
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.
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.
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.
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
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 :(
Did you use DTA?
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 :(
Sorry, what is DTA?
Data Engine Tuning Advisor.
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.
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

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |