score:0
I know the profiler should be able to handle all these cases, but I'm going to ask anyway...
Have you tried profiling any of the following:
- Converting the inner selection to a CTE?
WITH CTE_Versions AS ( SELECT DataID, MAX(VersionNum) as mVersion FROM t1 GROUP BY DataID ) SELECT DISTINCT t1.status AS Status FROM t1 JOIN CTE_Versions AS b ON t1.DataID = b.DataID AND t1.VersionNum = b.mVersion JOIN table2 ON t1.table2ID = table2.ID WHERE table2.field = @parameter
- Adding locking hints?
SELECT DISTINCT t1.status AS Status FROM t1 with (nolock) JOIN ( SELECT DataID, MAX(VersionNum) as mVersion FROM t1 with (nolock) GROUP BY DataID ) AS b ON t1.DataID = b.DataID AND t1.VersionNum = b.mVersion JOIN table2 with (nolock) ON t1.table2ID = table2.ID WHERE table2.field = @parameter
- Moving the parameter into the join? (as per @X-Zero's comment)
SELECT DISTINCT t1.status AS Status FROM t1 JOIN ( SELECT DataID, MAX(VersionNum) as mVersion FROM t1 GROUP BY DataID ) AS b ON t1.DataID = b.DataID AND t1.VersionNum = b.mVersion JOIN table2 ON t1.table2ID = table2.ID AND table2.field = @parameter
I'd be interested to hear how those three (or a combination thereof) would go?
More questions
- Simple WHERE clause kills my query's performance
- Adding simple AND after JOIN kills performance
- Using "CASE" in Where clause to choose various column harm the performance
- Azure SQL query performance significantly degrades when WHERE clause returns a high proportion of rows
- Performance of ON Clause over WHERE Clause
- How to improve performance of non-deterministic function of a column in a where clause or join?
- SELECT DISTINCT Inside WHERE IN clause performance
- Re-factor simple SQL statement with WHERE clause
- SQL Server: Performance of count() with where clause
- Why doesn't nhibernate LINQ support simple outer joins (given a where clause on the outer joined table)?
- WHERE Clause Performance While Querying an Indexed View
- performance implications of filtering by a small set of ID's in a Where clause
- SQL Server Query performance view used in where clause
- Adding Additional Where Clause in SQL Takes Big Performance Hit
- SQL Server Where Clause Performance
- SQL View Performance increases after adding OR condition in where clause
- SSIS Data Flow task providing poor performance when using a Where Clause
- SQL - Query Performance Slow When Joining Two Views with Where Clause
- SQL Performance Question (no where clause vs where column like '%')
- SQL query Performance when using Datetime in where / On clause
- SQL Server different performance in a WHERE clause when constraint is hardcoded
- Remote query, view and where clause performance
- SQL Server: How to improve performance for queries with multiple CTEs and subqueries in the WHERE clause
- TSQL Count(Id) performance with where clause
- query execution performance issues with a NOT IN in where clause
- Scalar value function usage in the where clause causing performance issue
- What is the effect of performance of a SQL query when have aggregate function and where clause
- SQL WHERE clause performance degradation
- Is there any short and simple way to write the lengthy where clause
- SQL Server : where clause performance theory?
More questions with similar tag
- How to pass multiple values in a single parameter for a stored procedures?
- How to enable sa login when doing an unattended install of SQL Server 2014 Express?
- SQL Server Command Builder and AutoIncrementing IDs
- Ms access equivalent to Cast as Bit of sql server
- How to add commas to numbers in SQL Server?
- What indexes optimize this query with four joins?
- OPENJSON - Incorrect syntax near the keyword 'with'
- Within a SQL Server view - how to combine multiple column results into one column
- Cannot login to SQL Server 2008, in Windows 7 x64
- T-SQL UPDATE trigger help
- Powershell - DataSet contains the number of records
- SQL query to replace multiple occurrences with the most frequent ones
- SQL Join table-valued function with table where table field is a function input
- Convert a number in Words with CAMA (number,number)
- sql query takes more time when run in a view
- More efficient way of writing this query?
- Number of Columns Each procedures returns T-Sql
- Undo changes to SQL Server 2005 database
- SQL Pivoting a column
- Query to get items due in the next 7 days
- Concatenate columns of multiple columns and multiple rows into one varchar value, when no of columns is dynamic
- How can I set a custom name for the EF core migration history table and have it used when generating migration scripts?
- Executing SSIS Package as SQLAgent Job
- SQL GROUP BY with null values
- Keep inserted rows in Temp table - after a rollback
- How do I add a “last modified” and "created" column in a SQL Server table?
- Storing images in database results in partial image
- Application not running on 64 bit machine
- Syntax for returning an output parameter
- How can I quickly identify most recently modified stored procedures in SQL Server
Source:
stackoverflow.com