Optimizing nested sql query I have a code, which dynamically creates an SQL query based on the selected tables and columns. One of the sample query, which is generated is as following:
SELECT
    agr.Name,
    agr.Link,
    comp1.Name as 'Party 1',
    cnt1.Name as 'Party 1 Country',
    comp2.Name as 'Party 2',
    cnt2.Name as 'Party 2 Country'
FROM
    [dbo].[Agreements] agr
    LEFT JOIN [dbo].[Companies] comp1 on agr.Party1CompanyId = comp1.BusinessId
    AND agr.TenantId = comp1.TenantId
    LEFT JOIN [dbo].[Countries] cnt1 on comp1.CountryId = cnt1.BusinessId
    AND comp1.TenantId = cnt1.TenantId
    LEFT JOIN [dbo].[Companies] comp2 on agr.Party2CompanyId = comp2.BusinessId
    AND agr.TenantId = comp2.TenantId
    LEFT JOIN [dbo].[Countries] cnt2 on comp2.CountryId = cnt2.BusinessId
    AND comp2.TenantId = cnt2.TenantId
WHERE
    agr.ValidFrom &lt;= DATETIMEFROMPARTS(2019, 1, 1, 00, 00, 00, 1)
    AND agr.ValidTo &gt;= DATETIMEFROMPARTS (2019, 12, 31, 23, 59, 59, 0)
    AND agr.TenantId = 1
    AND comp1.ValidFrom &lt;= DATETIMEFROMPARTS(2019, 1, 1, 00, 00, 00, 1)
    AND comp1.ValidTo &gt;= DATETIMEFROMPARTS (2019, 12, 31, 23, 59, 59, 0)
    AND comp2.ValidFrom &lt;= DATETIMEFROMPARTS(2019, 1, 1, 00, 00, 00, 1)
    AND comp2.ValidTo &gt;= DATETIMEFROMPARTS (2019, 12, 31, 23, 59, 59, 0)
    AND cnt1.ValidFrom &lt;= DATETIMEFROMPARTS(2019, 1, 1, 00, 00, 00, 1)
    AND cnt1.ValidTo &gt;= DATETIMEFROMPARTS (2019, 12, 31, 23, 59, 59, 0)
    AND cnt2.ValidFrom &lt;= DATETIMEFROMPARTS(2019, 1, 1, 00, 00, 00, 1)
    AND cnt2.ValidTo &gt;= DATETIMEFROMPARTS (2019, 12, 31, 23, 59, 59, 0);

I wanted to ask the community, if there is a room for the optimisation of this query so that they do are not too long and still understandable and as per SQLs best practices. The concerns I have is more on the joins to the same table multiple time or same &quot;WHERE&quot; conditions for almost all tables.
The possible optimisation suggestions to the query above are sufficient for me to rewrite some bits, which eventually should be corrected. Thanks.
The following are a few suggestions on how I'd write the SQL.

define your date parameters as variables
create a CTE of table expressions to simplify parts of your query

Source Control
If you don't already have a database project, create one in Visual Studio. Then check it in to source control. Microsoft Azure DevOps Services is free &amp; private for teams of 5 or less (this is per project, so 5 developers per project). Then you'll be able to track changes you make to your stored procedures, views, tables, etc.
Formatting
I use the following tools for SSMS and Visual Studio, ApexSQL Refactor and Poor Man's T-Sql Formatter. I use it when I have to edit other developer's code. It's a great way to standardize your SQL. I find it does most of the formatting for me, but I'll still make a few changes after.

Also check out the book Clean Code. It will change the way you think about naming conventions.


Revised SQL
Without table definitions and sample records I was unable to test this, but it should give you a good start.
DECLARE @ValidFrom  AS DATETIME = '01-Jan-2019';
DECLARE @ValidTo    AS DATETIME = '31-Dec-2019 23:59:59';

WITH
agreements_list
AS
(
   SELECT
        [agr].[Name]
      , [agr].[Link]
   FROM 
      [dbo].[Agreements] 
   WHERE 
      1=1
      AND [TenantId] = 1
      AND [ValidFrom] &gt;= @ValidFrom 
      AND [ValidTo] &lt;= @ValidTo 
)
,
companies_list
AS
(
   SELECT 
        [PartyName] = comp.[Name]
      , [CountryName] = cnt.[Name]
   FROM 
      [dbo].[Companies] AS comp
      LEFT JOIN [dbo].[Countries] AS cnt ON comp.[CountryId] = cnt.[BusinessId] AND comp.[TenantId] = cnt.[TenantId]
   WHERE 
      1=1
      AND comp.[ValidFrom] &gt;= @ValidFrom 
      AND comp.[ValidTo] &lt;= @ValidTo
)
SELECT 
     [agr].[Name]
   , [agr].[Link]
   , [Party 1] = [comp1].[PartyName]
   , [Party 1 Country] = [comp1].[CountryName]
   , [Party 2] = [comp2].[PartyName]
   , [Party 2 Country] = [comp2].[CountryName]
FROM
   agreements_list AS [agr]
   LEFT JOIN companies_list AS [comp1] ON [agr].[Party1CompanyId] = [comp1].[BusinessId] AND [agr].[TenantId] = [comp1].[TenantId]
   LEFT JOIN companies_list AS [comp2] ON [agr].[Party2CompanyId] = [comp2].[BusinessId] AND [agr].[TenantId] = [comp2].[TenantId]