Initially, performance on the Orders table was poor. To improve it, I added CompanyID to the Orders table, since that would be the limiting factor for all users. Now the problem revolves around an OrderStatusHistory table.
Required functionality of the application includes selecting all records from OrderStatusHistory for a particular company (although it is also limited by an Active flag). The current stored procedure for retrieving this data contains SQL similar to "SELECT OrderStatusHistory.SomeFields, Orders.OtherFields FROM OrderStatusHistory WITH (NOLOCK) INNER JOIN Orders WITH (NOLOCK) ON Orders.OrderID = OrderStatusHistory.OrderID WHERE Orders.CompanyID = @CompanyID" AND Active = 1.
How can I improve performance of this query (currently 16 seconds against 2 million records) without adding CompanyID to the OrderStatusHistory table?
Dig deeper on Microsoft SQL Server Performance Monitoring and Tuning
Related Q&A from Jeremy Kadlec, Contributor
Learn how to view a query execution plan in SQL Server with SQL Server Management Studio or Query Analyzer. Review and decrease query plan sub tree ...continue reading
Adding non-clustered indexes to SQL Server columns and tables frequently queried is possible. Learn how to add non-clustered indexes to SQL Server ...continue reading
Want to change the collation to SQL Server 2005 standards after upgrading databases from SQL Server 2000? Learn the best approach involving DTS and ...continue reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.