Hash Match (Inner Join) cost 50% in my query

Question

I have never interacted with Hash Join yet. According to picture below, do I need an index on table tblFin_Invoices?

Or which table do I need to create index in order to avoid this Hash Join?

Is any chance I can do something to alleviate the query?

------------------------------------------------------------------------

declare  
        @EffDateFrom datetime = '2017-02-01',  
        @EffDateTo  datetime= '2017-12-31'

DECLARE @ValidInvoicesTable TABLE (InvoiceNum INT PRIMARY KEY)   
INSERT INTO @ValidInvoicesTable
SELECT DISTINCT INV.InvoiceNum
FROM            tblFin_Invoices INV
    INNER JOIN      tblQuotes ON INV.QuoteID = tblQuotes.QuoteID
    --INNER JOIN        tblClientOffices ON tblQuotes.QuotingLocationGuid = tblClientOffices.OfficeGUID 
WHERE           (INV.Failed = 0) 

                AND dateDiff(d, @EffDateFrom, dbo.tblQuotes.EffectiveDate) >= 0 
                AND dateDiff(d, @EffDateTo, dbo.tblQuotes.EffectiveDate) <= 0 
                AND dbo.tblQuotes.LineGUID = '6E00868B-FFC3-4CA0-876F-CC258F1ED22D'  

DECLARE @TempData TABLE(
    [QuoteID] int,
    [QuoteGUID] [uniqueidentifier] NOT NULL,
    [CompanyLocationGuid] [uniqueidentifier] NULL,
    [UnderwriterUserGuid] [uniqueidentifier] NULL,
    [InsuredGuid] [uniqueidentifier] NULL,
    [ProducerGuid] [uniqueidentifier] NULL,
    [ProducerContactGuid] [uniqueidentifier] NULL,
    [EffectiveDate] datetime NULL,
    --[InvoiceDate] datetime NULL,
    [AccountingDate] datetime NULL,
    [PolicyTypeID] tinyint NOT NULL,  --------------
    [TransactionTypeID] varchar(2)  NULL,  
    [QuoteStatusID] tinyint NOT NULL,
    [PolicyNumber] [varchar](150) NULL,
    [StateID] [char](2) NULL,
    [Premium] [money] NULL,
    BondRate decimal (5,4) NULL,
    [PenalLiability] [money] NULL,
    [CompanyCommission] decimal(3,2) NULL
)

    INSERT INTO @TempData
    SELECT      
                INV.QuoteID,
                tblQuotes.QuoteGUID,
                tblQuotes.CompanyLocationGuid,
                tblQuotes.UnderwriterUserGuid,
                tblSubmissionGroup.InsuredGuid,
                tblProducerLocations.ProducerGUID,
                tblQuotes.ProducerContactGuid,
                INV.EffectiveDate,
                INV.InvoiceDate,
                --dbo.CalcAccountingDate(tblQuotes.QuoteStatusID,INV.invoicedate,INV.effectivedate, tblQuotes.EndorsementEffective) AccountingDate,
                tblQuotes.PolicyTypeID,
                tblQuotes.TransactionTypeID, 
                tblQuotes.QuoteStatusID,
                tblQuotes.PolicyNumber,         
                tblQuotes.StateID, 
                (SELECT ISNULL(SUM(tblFin_InvoiceDetails.AmtBilled), 0)
                 FROM tblFin_InvoiceDetails
                 WHERE (tblFin_InvoiceDetails.ChargeType = 'P')
                 AND (tblFin_InvoiceDetails.InvoiceNum = INV.InvoiceNum))
                 AS Premium,
                 [Dynamic_Data_SuretyPRC].BondRate,
                 [Dynamic_Data_SuretyPRC].BondAmount,
                 [Dynamic_Data_SuretyPRC].CompanyComm   

    FROM            tblFin_Invoices INV
    INNER JOIN      tblQuotes ON INV.QuoteID = tblQuotes.QuoteID
    INNER JOIN      tblProducerLocations ON INV.ProducerLocationGUID = tblProducerLocations.ProducerLocationGUID  
    INNER JOIN      tblSubmissionGroup ON tblQuotes.SubmissionGroupGuid = tblSubmissionGroup.SubmissionGroupGUID 
    LEFT  JOIN      [dbo].[Dynamic_Data_SuretyPRC] ON Dynamic_Data_SuretyPRC.QuoteGUID = tblQuotes.QuoteGUID 
    WHERE       INV.InvoiceNum IN (SELECT * FROM @ValidInvoicesTable)           
    ORDER BY    INV.InvoiceDate
--select * from @TempData

Answer 1

All the tables you use in the first query for @ValidInvoicesTable are also in the second query. Instead of using @ValidInvoicesTable, use the WHERE conditions from that SELECT and replace the WHERE condition in the second SELECT statement.

I'd also use BETWEEN instead of two statements for the date comparison, to get the same result you'll need to CAST/CONVERT the EffectiveDate, if the DateTime field actually contains Time.

I'd also make a join out of tblFin_InvoiceDetails.

I assume that you are going to use the @TempData table for something other the just a SELECT statement. If I'm wrong, you should remove the DECLARE and INSERT INTO regarding @TempData table completely.

Below is my suggestion.

declare  
     @EffDateFrom datetime = '2017-02-01',  
     @EffDateTo  datetime= '2017-12-31'

DECLARE @TempData TABLE(
    [QuoteID] int,
    [QuoteGUID] [uniqueidentifier] NOT NULL,
    [CompanyLocationGuid] [uniqueidentifier] NULL,
    [UnderwriterUserGuid] [uniqueidentifier] NULL,
    [InsuredGuid] [uniqueidentifier] NULL,
    [ProducerGuid] [uniqueidentifier] NULL,
    [ProducerContactGuid] [uniqueidentifier] NULL,
    [EffectiveDate] datetime NULL,
    --[InvoiceDate] datetime NULL,
    [AccountingDate] datetime NULL,
    [PolicyTypeID] tinyint NOT NULL,  --------------
    [TransactionTypeID] varchar(2)  NULL,  
    [QuoteStatusID] tinyint NOT NULL,
    [PolicyNumber] [varchar](150) NULL,
    [StateID] [char](2) NULL,
    [Premium] [money] NULL,
    BondRate decimal (5,4) NULL,
    [PenalLiability] [money] NULL,
    [CompanyCommission] decimal(3,2) NULL
)

    INSERT INTO @TempData
    SELECT      
                INV.QuoteID,
                tblQuotes.QuoteGUID,
                tblQuotes.CompanyLocationGuid,
                tblQuotes.UnderwriterUserGuid,
                tblSubmissionGroup.InsuredGuid,
                tblProducerLocations.ProducerGUID,
                tblQuotes.ProducerContactGuid,
                INV.EffectiveDate,
                INV.InvoiceDate,
                tblQuotes.PolicyTypeID,
                tblQuotes.TransactionTypeID, 
                tblQuotes.QuoteStatusID,
                tblQuotes.PolicyNumber,         
                tblQuotes.StateID, 
                SUM(ISNULL(tblDetailed.AmtBilled, 0)) AS Premium,
                 [Dynamic_Data_SuretyPRC].BondRate,
                 [Dynamic_Data_SuretyPRC].BondAmount,
                 [Dynamic_Data_SuretyPRC].CompanyComm
    FROM            tblFin_Invoices INV
    INNER JOIN      tblQuotes ON INV.QuoteID = tblQuotes.QuoteID
    INNER JOIN      tblProducerLocations ON INV.ProducerLocationGUID = tblProducerLocations.ProducerLocationGUID  
    INNER JOIN      tblSubmissionGroup ON tblQuotes.SubmissionGroupGuid = tblSubmissionGroup.SubmissionGroupGUID 
    LEFT  JOIN      [dbo].[Dynamic_Data_SuretyPRC] ON Dynamic_Data_SuretyPRC.QuoteGUID = tblQuotes.QuoteGUID
    LEFT  JOIN      tblFin_InvoiceDetail tblDetail ON tblDetail.InvoiceNum = INV.InvoiceNum AND tblDetail.ChargeType = 'P'
    WHERE 
            INV.Failed = 0
            AND CAST(dbo.tblQuotes.EffectiveDate as date) BETWEEN @EffDateFrom AND @EffDateTo 
            AND dbo.tblQuotes.LineGUID = '6E00868B-FFC3-4CA0-876F-CC258F1ED22D'
    GROUP BY INV.QuoteID,
                tblQuotes.QuoteGUID,
                tblQuotes.CompanyLocationGuid,
                tblQuotes.UnderwriterUserGuid,
                tblSubmissionGroup.InsuredGuid,
                tblProducerLocations.ProducerGUID,
                tblQuotes.ProducerContactGuid,
                INV.EffectiveDate,
                INV.InvoiceDate,
                tblQuotes.PolicyTypeID,
                tblQuotes.TransactionTypeID, 
                tblQuotes.QuoteStatusID,
                tblQuotes.PolicyNumber,         
                tblQuotes.StateID, 
                 [Dynamic_Data_SuretyPRC].BondRate,
                 [Dynamic_Data_SuretyPRC].BondAmount,
                 [Dynamic_Data_SuretyPRC].CompanyComm
    ORDER BY    INV.InvoiceDate