CODEWITHSUNDEEP
sqlsql-server
yakya
yakya

Reputation: 5210

Query performance changes dramatically between two similar queries

I have a simple query like below:

SELECT 
    COUNT(*)
FROM  
    [dbo].[Tenants] AS [Extent1]
LEFT OUTER JOIN 
    [dbo].[Sectors] AS [Extent2] ON [Extent1].[SectorId] = [Extent2].[Id]
WHERE 
    (CONTAINS([Extent1].[Name], '"asdf*"')) OR 
    (CONTAINS([Extent2].[Name], '"asdf*"'))

Table named Tenants has 386533 rows and Sectors has 40 rows. I have full text search enabled for both of the columns. And the execution plan of this query is below:

enter image description here

This query takes 8 seconds to work. However, when I comment one of the filters out (true for both of the filters), they work instantly. For example, the following works instantly:

SELECT 
    COUNT(*)
FROM  
    [dbo].[Tenants] AS [Extent1]
LEFT OUTER JOIN 
    [dbo].[Sectors] AS [Extent2] ON [Extent1].[SectorId] = [Extent2].[Id]
WHERE 
    --(CONTAINS([Extent1].[Name], '"asdf*"')) OR 
    (CONTAINS([Extent2].[Name], '"asdf*"'))

And the query execution plan for this query is below:

enter image description here

I tried rebuilding indexes and statistics but no help. In addition, I tried giving PK indexes as hint (since the second query uses it whereas the first one doesn't) but made the things worse. What can I do to speed up the first query? I don't want to divide the query into 2 and sum the counts up (that works instantly though..)

Edit: The SQL Server version is "Microsoft SQL Server 2014 - 12.0.2342.0 (X64)"

Edit 2: Client statistics:

enter image description here

Upvotes: 2

Views: 70

Answers (2)

CrApHeR
CrApHeR

Reputation: 2655

In the first case, SQL Server needs to do the OUTER JOIN because you are using both tables to filter the information.

In the second case, SQL Server just needs to do an INNER JOIN because you are already filtering the information in one of the tables and you don't care about the other one because that does not affect your count(*) statement.

Let me give you more detail about the second case:

if you filter by Extend1.Name, SQL Server does not care about the Extend2 table because you already filtered the information and the Extend2 table does not change the count of records.

if you filter by Extend2.Name, SQL Server does not need the NULL values generated by the OUTER JOIN, that means that all the records are already in the Extend1 table. (You don't need an outer join for that case, an inner join is enough)

(You can see this in both query plans)

You can change the column SectorId to NOT NULL in the table Tenants and assign a default value. After that, you can change the OUTER JOIN to INNER JOIN and the performance should be much better.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270091

Try using UNION:

SELECT COUNT(*)
FROM ((SELECT . . .
       FROM [dbo].[Tenants] [Extent1] LEFT OUTER JOIN 
            [dbo].[Sectors] [Extent2]
            ON [Extent1].[SectorId] = [Extent2].[Id]
       WHERE CONTAINS([Extent1].[Name], '"asdf*"')
      ) UNION
      (SELECT . . .
       FROM [dbo].[Tenants] [Extent1] INNER JOIN 
            [dbo].[Sectors] [Extent2]
            ON [Extent1].[SectorId] = [Extent2].[Id]
       WHERE CONTAINS([Extent2].[Name], '"asdf*"')
      ) 
     ) x;

Notes:

  • Separate queries might allow the optimizer to use the full text index more efficiently.
  • This uses UNION because the two queries might return the same rows.
  • The . . . is for the appropriate columns that identify each row you want to count. This is needed for the UNION.
  • The second JOIN is an inner join. The condition in the WHERE clause means that a match has to exist in the second table.

Upvotes: 2

Related Questions