Query with Join of tables is taking long time to execute 5 min

Question

SELECT
    B.AccountBranchID
    ,B.VoucherNo
    ,B.BranchName AS BranchName
    ,B.InvoiceNo
    ,CONVERT(VARCHAR, B.InvoiceDate, 103) AS InvoiceDate
    ,CONVERT(VARCHAR, B.VoucherDate, 103) AS VoucherDate
    ,B.CustomerName
    ,B.RefID
    ,LN.AccountName AS LedgerName
    ,b.SalesPersonName AS SalesPersonName
    ,LN.LedgerCode
    ,B.AgentName
    ,B.ShipperName
    ,B.Segment
    ,B.TransactionType
    ,B.JobNo
    ,CONVERT(VARCHAR, B.JOBDate, 103) AS JOBDate
    ,B.MAWBNo
    ,B.HAWBNo
    ,B.AccountName
    ,B.LedgerCode AS AccountLedgerCode
    ,B.CurrencyCode
    ,ISNULL(B.Amount, 0) AS Amount
    ,B.ChargeExRate
    ,(CASE B.CRDR
        WHEN 'CR' THEN (B.ChargeBaseAmount * -1)
        ELSE B.ChargeBaseAmount
    END) AS ChargeBaseAmount
    ,(CASE B.CRDR
        WHEN 'CR' THEN 'Credit'
        ELSE 'Debit'
    END) AS CRDR

FROM VW_VoucherTR AS B
INNER JOIN VW_VoucherTR AS LN
    ON B.VoucherID = LN.VoucherID
WHERE B.CompanyID = @CompanyID
AND (CASE @Type
    WHEN 'I' THEN B.InvoiceDate
    ELSE B.VoucherDate
END) BETWEEN ISNULL(@FromDate, (SELECT
        FYearStart
    FROM Secmst_FinancialYear
    WHERE FyearId = @yearID)
) AND ISNULL(@ToDate, GETDATE())
AND (@Segment IS NULL
OR B.Segment = @Segment)
AND (@BranchMappingID IS NULL
OR B.BranchMappingID = @BranchMappingID)
AND B.VoucherTypeCode IN ('sv')
AND B.IsDeleted = 0
AND (B.GroupName <> 'Sundry Creditors'
AND B.GroupName <> 'Sundry Debtors')
AND LN.GroupName IN ('Sundry Debtors', 'Sundry Creditors')

Answer 1

The subquery in the BETWEEN is probably what is killing you. Have you looked at the execution plan?

BETWEEN ISNULL(@FromDate, ( SELECT FYearStart FROM Secmst_FinancialYear WHERE FyearId = @yearID )) AND ISNULL(@ToDate, GETDATE())

What's happening is you are running that query across every row, and by my looks, that's unnecessary because you are only needing static dates there (not anything based on the joined rows.)

Try this:

DECLARE @FromDateActual datetime = ISNULL(@FromDate, (
                SELECT FYearStart
                FROM Secmst_FinancialYear
                WHERE FyearId = @yearID
                ));
DECLARE @ToDateActual datetime = ISNULL(@ToDate, GETDATE());

SELECT B.AccountBranchID
    ,B.VoucherNo
    ,B.BranchName AS BranchName
    ,B.InvoiceNo
    ,convert(VARCHAR, B.InvoiceDate, 103) AS InvoiceDate
    ,convert(VARCHAR, B.VoucherDate, 103) AS VoucherDate
    ,B.CustomerName
    ,B.RefID
    ,LN.AccountName AS LedgerName
    ,b.SalesPersonName AS SalesPersonName
    ,LN.LedgerCode
    ,B.AgentName
    ,B.ShipperName
    ,B.Segment
    ,B.TransactionType
    ,B.JobNo
    ,convert(VARCHAR, B.JOBDate, 103) AS JOBDate
    ,B.MAWBNo
    ,B.HAWBNo
    ,B.AccountName
    ,B.LedgerCode AS AccountLedgerCode
    ,B.CurrencyCode
    ,ISNULL(B.Amount, 0) AS Amount
    ,B.ChargeExRate
    ,(
        CASE B.CRDR
            WHEN 'CR'
                THEN (B.ChargeBaseAmount * - 1)
            ELSE B.ChargeBaseAmount
            END
        ) AS ChargeBaseAmount
    ,(
        CASE B.CRDR
            WHEN 'CR'
                THEN 'Credit'
            ELSE 'Debit'
            END
        ) AS CRDR
FROM VW_VoucherTR AS B
INNER JOIN VW_VoucherTR AS LN ON B.VoucherID = LN.VoucherID
WHERE B.CompanyID = @CompanyID
    AND (
        CASE @Type
            WHEN 'I'
                THEN B.InvoiceDate
            ELSE B.VoucherDate
            END
        ) BETWEEN @FromDateActual
        AND @ToDateActual
    AND (
        @Segment IS NULL
        OR B.Segment = @Segment
        )
    AND (
        @BranchMappingID IS NULL
        OR B.BranchMappingID = @BranchMappingID
        )
    AND B.VoucherTypeCode IN ('sv')
    AND B.IsDeleted = 0
    AND (
        B.GroupName <> 'Sundry Creditors'
        AND B.GroupName <> 'Sundry Debtors'
        )
    AND LN.GroupName IN (
        'Sundry Debtors'
        ,'Sundry Creditors'
        )

Beyond that you could consider adding non-clustered indexes. The Query Analyzer may even suggest a couple. But you'll want to be careful there, depending on how the data is used and loaded, as too many indexes or large ones can lead to further performance issues in other places (row insertions, page fragmentation, etc).

Answer 2

There could be many reasons for it, but one thing is quite plain. The following part is not sargable.

 (CASE @Type
    WHEN 'I' THEN B.InvoiceDate
    ELSE B.VoucherDate
END) BETWEEN ISNULL(@FromDate, (SELECT
        FYearStart
    FROM Secmst_FinancialYear
    WHERE FyearId = @yearID)
) AND ISNULL(@ToDate, GETDATE())

Should be rewritten to be sargable, so that indexes can be used.

SELECT @FromDate = ISNULL(@FromDate, (SELECT
        TOP 1 FYearStart 
        FROM Secmst_FinancialYear
        WHERE FyearId = @yearID)) )

SELECT @ToDate = ISNULL(@ToDate, GETDATE())

SELECT
...
WHERE
...
AND
((@Type='I' AND B.InvoiceDate BETWEEN @FromDate AND @ToDate)
OR
(@Type<>'I' AND B.VoucherDate BETWEEN @FromDate AND @ToDate))
AND 
...

Of course proper indexing is the way how to speed up your query, if no indexes are on InvoiceDate, VoucherDate, etc. then your query will use full table scan instead of index seek and it will be slow.