Search code examples
sql-serverquery-optimizationquery-cache

SQL Server plan caching on very similar but different queries


Say I have a query like this:

SELECT COUNT(*) FROM Investors
WHERE 1=1  
AND 
(
    investerName IN (SELECT investerName FROM Assets WHERE Assets.CompanyID=Investors.CompanyID AND Assets.CompanyID = @companyID )
    OR investerName IN 
        (
            SELECT investerName FROM InvestmentEnrollments WHERE InvestmentEnrollments .CompanyID=Investors.CompanyIDAND InvestmentEnrollments .state IN (3,4) AND InvestmentEnrollments.CompanyID = @companyID         
        )
) 
AND Investors.CompanyID=@companyID

and another similar query like so:

SELECT COUNT(*) FROM Investors
WHERE 1=1  
AND 
(
    investerName IN (SELECT investerName FROM Assets WHERE Assets.CompanyID=Investors.companyID AND Assets.companyID = @companyID )
    OR investerName IN 
        (
            SELECT investerName FROM InvestmentEnrollments WHERE InvestmentEnrollments .companyID=Investors.companyID AND InvestmentEnrollments.state IN (3,4) AND InvestmentEnrollments.companyID = @companyID         
        )
) 
AND Investors.companyID=@companyID
AND Investors.userName = @someInvestorUserName

Both queries are similar enough but the second one has he additional where predicate. So what does SQL Server do in this case? Does it try to fit both into the same query plan? Or are these considered two distinct queries requiring different query plans?


Solution

  • The queries are not only different text, they are semantically different. These are distinct queries, requirying distinct execution plans. Nothing in common.