1

i have a sql query running from database 1 that joins table company on to a view

SELECT DISTINCT RTRIM([K].[cmpCompanyCode]) AS [cmpCompanyCode]
FROM [dbo].[view] AS [K]  

JOIN [database2].[dbo].[Company] AS [C]  
ON [K].[cmpCompanyCode] = [C].[CmpCompanyCode];

this is my view

SELECT RTRIM([CM].[fkOrganizationIDParent]) AS [fkOrganizationIDParent],
          [C].[cmpCoID],
          RTRIM([CE].[ClientNumber]) AS [cmpCompanyCode],

          ISNULL([CE].[z_DoNotReplicate], 0) AS [DoNotReplicateSupervisors],
          ISNULL([CE].[z_StopPTOReplication], 0) AS [StopPtoReplication]        
    FROM   [dbo].[Client] AS [CM] WITH(NOLOCK)
          JOIN [dbo].[Entity] AS [CE] WITH(NOLOCK) ON [CM].[fkProjectID] = [CE].[fkProjectID]
          JOIN [database2].[dbo].[Company] AS [C] ON [CE].[ClientNumber] = [C].[CmpCompanyCode]
    WHERE  [CM].[z_K] = 1
          AND ISNULL([CE].[z_Exclude], 0) = 0   
          AND [CM].[crStatus] <> 'Terminated'
          AND [CM].[IsActive] = 1
          AND [CE].[IsActive] = 1
          AND ISNULL(RTRIM([CE].[ClientNumber]), '') <> '';

but when i run the first query my execution plan shows me a high hash.

Output List says dbo.company.CmpCoid, expr1016 hash

i have indexes on the dbo.company table as follows

CREATE NONCLUSTERED INDEX [IDX_CompanyCode] ON [dbo].[Company]
(
    [CmpCompanyCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

CREATE CLUSTERED INDEX [CDX_CompanyName] ON [dbo].[Company]
(
    [CmpCompanyName] ASC,
    [CmpCoID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85) ON [PRIMARY]
GO

im not sure how to fix this?

4
  • I don't see a problem. Are you referring to the Cost % metric?...That's just an estimate (so it's not very useful to look at) and every execution plan's costs have to add up to 100% (sans rounding errors), so your plan will always have costs distributed throughout it like such. Provide the actual execution plan via Paste The Plan which tells us how long in execution time each operator ran for (generally).
    – J.D.
    Commented Feb 15 at 16:30
  • i thought if i improved the efficiency of the join the cost metric would be cheaper?
    – deanpillow
    Commented Feb 15 at 17:05
  • You may reduce the cost without having any impact on the runtime whatsoever. Is this query actually slow? Commented Feb 15 at 17:23
  • 1
    @deanpillow The cost has to go somewhere, it's relative to the entire plan, it'll always add up (roughly) to 100%. That's not actually indicating a problem (or that there's anything to improve) by itself though. To re-emphasize what Erik said, is this query actually slow running and if so, what operator in the actual execution plan has the longest runtime?
    – J.D.
    Commented Feb 15 at 19:57

0