Search code examples
sqlsql-serverinner-join

Never ending select with troublesome inner join

I have two tables:

enter image description here

enter image description here

enter image description here

Query is (short version):

SELECT TOP (2000) *
  FROM [...].[Documents] AS [D] 
        INNER JOIN dbo.SE_CMSClientMatters AS [mat]  ON [mat].[Mcode] = [D].[custom2]

I cannot figure out for the life of me why it does not work -i.e. never completes execution - ran for 13 hours and still no sign of completing.

Documents table has around 12 million rows, ClientMatters has around 330,000 rows.

The original query has several other left joins and this is the only inner join. If I leave it out, the query completes in 20 seconds!

Its 4am so either I'm losing it or have missed something obvious. PS - I did rebuild the indexes. The Custom2 field is part of a group of fields indexed (see image).

Any help appreciated - thanks!

Solution

  • One of the issues I see is

    • mat.MCode is of type varchar(27)
    • D.custom2 is of type nvarchar(32)

    This is horrible (performance-wise) when joining - one column is Unicode, the other is not.

    Try to cast one to the other - something like this:

    SELECT TOP (2000) *
FROM [...].[Documents] AS [D] 
INNER JOIN dbo.SE_CMSClientMatters AS [mat] 
      ON CAST([mat].[Mcode] AS NVARCHAR(32)) = [D].[custom2]

    As a general rule, you should always try to use the same datatype in columns that you use for joining - and joining is typically much easier and faster on numerical datatypes, rather than string-based datatypes.

    If you could - try to convert one of these two columns to the same datatype as the other - I'm pretty sure that would speed things up significantly.

    And also: an index on Documents where Custom2 is at the second position will NOT be able to be used for this join - try to create a separate index on custom2 alone:

    CREATE NONCLUSTERED INDEX IX_Documents_custom2 ON dbo.Documents(custom2)