Similar questions have been asked earlier, and I did perform my research; e.g. here, here, here and here.
However what we saw recently was quite boggling. We have a very complex query for some of our Invoice reconciliation that we were trying to optimise:
WITH cte1 AS
(
SELECT a.*
FROM APInvoices a WITH (NOLOCK)
JOIN Profiles p WITH (NOLOCK) ON a.ProfileNumber = p.ProfileNumber
AND Round(a.Balance, 2) <> 0
),
cte2 AS
(
SELECT
a.InvoiceID AS ID, a.OriginalInvoiceDetailID, a.ARInvoiceID, 'S' AS Type, 'I' AS Department,
a.APTransactionType, a.InvoiceNumber, a.BranchNumber, a.ProfileNumber,
a.InvoiceDate, a.Description, a.PayableAccount AS AccountNumber, a.InvoiceAmount AS InvoiceAmount, a.Balance AS Amount,
a.CurrencyCode, ISNULL(a.ExchangeRate, 1) AS ExchangeRate, --Mike: Can we trap for that, i.e. case arinvoiceblaances.exchangerate = Null then 1 else exchangerate?
ISNULL(d.TicketNumber, '') AS TicketNumber,
a.ProfileName AS FullName,
a.CashSelected, a.ChequeSelected, a.OnHold, a.DueDate,
Profiles.ProfileType,
ISNULL(d.PassengerName, '') AS PassengerName,
d.TransactionDate,
d.TravelDate, d.ReturnDate, d.VatOnCommission AS VatOnCommission, d.ReservationID,
d.ChainCode, d.ProductCode, d.SubmitTo, d.VoucherStatus,
d.InvoiceDetailID, d.CommissionAmount, d.GrossAmount, d.GroupCode, d.PstOnCommission,
d.TransactionType, d.CityCode, d.TransactionCode
FROM
APInvoices a WITH (NOLOCK)
JOIN
Profiles WITH (NOLOCK) ON a.ProfileNumber = Profiles.ProfileNumber
JOIN
ARInvoiceDetails d WITH (NOLOCK) ON d.InvoiceID = a.ARInvoiceID
WHERE
d.TransactionType IN (1, 3)
AND ((d.InvoiceDetailID = a.ARInvoiceDetailID)
OR
(a.ARInvoiceDetailID = 0
AND a.ARInvoiceID = d.InvoiceID
AND a.TicketNumber <> ''''
AND a.TicketNumber IS NOT NULL
AND a.TicketNumber = d.TicketNumber
AND a.CurrencyCode = d.CurrencyCode
AND (
CASE WHEN (LEN(a.ProfileNumber) = 8 AND a.ProfileNumber = d.VendorNumber) OR LEN(a.ProfileNumber) = 6
THEN 1
ELSE 0
END) = 1
)
OR
(
a.ARInvoiceDetailID = 0
AND a.ARInvoiceID = d.InvoiceID
AND (a.TicketNumber = '''' OR a.TicketNumber IS NULL)
AND a.CurrencyCode = d.CurrencyCode
AND (
CASE WHEN (LEN(a.ProfileNumber) = 8 AND a.ProfileNumber = d.VendorNumber) OR LEN(a.ProfileNumber) = 6
THEN 1
ELSE 0
END) = 1
)
)
UNION
SELECT a.InvoiceID AS ID, a.OriginalInvoiceDetailID, a.ARInvoiceID, 'S' AS Type, 'I' AS Department,
a.APTransactionType, a.InvoiceNumber, a.BranchNumber, a.ProfileNumber,
a.InvoiceDate, a.Description, a.PayableAccount AS AccountNumber, a.InvoiceAmount AS InvoiceAmount, a.Balance AS Amount,
a.CurrencyCode, ISNULL(a.ExchangeRate, 1) AS ExchangeRate, --Mike: Can we trap for that, i.e. case arinvoiceblaances.exchangerate = Null then 1 else exchangerate?
ISNULL(a.TicketNumber, '') AS TicketNumber,
a.ProfileName AS FullName,
a.CashSelected, a.ChequeSelected, a.OnHold,
a.DueDate,
Profiles.ProfileType,
'' AS PassengerName,
NULL as TransactionDate,
NULL as TravelDate, NULL as ReturnDate, NULL as VatOnCommission, 0 as ReservationID,
'' as ChainCode, NULL as ProductCode, NULL as SubmitTo, NULL as VoucherStatus,
NULL as InvoiceDetailID, NULL as CommissionAmount, NULL as GrossAmount, '' as GroupCode, NULL as PstOnCommission,
NULL as TransactionType,
'' as CityCode,
'' as TransactionCode
FROM APInvoices a WITH (NOLOCK)
JOIN Profiles WITH (NOLOCK) ON a.ProfileNumber = Profiles.ProfileNumber
WHERE a.arinvoiceid = 0
)
SELECT DISTINCT TOP (2) t2.*
FROM cte2 t2
WHERE (t2.ProfileType = 2 OR t2.ProfileType = 3)
AND ((t2.SubmitTo = 2 AND t2.VoucherStatus = 0) OR t2.SubmitTo <> 2) AND Len(t2.profilenumber) = 8 AND t2.ProfileType = 2 AND t2.TicketNumber = '12345';
The execution time for the above query was well over 30 seconds (the default Sql timeout in .Net), when it produced hardly 0-2 result rows.
Trying to optimise the above query, all we accidently did was to introduce variables:
declare @profilenumberlength varchar(10)
declare @profileType int
declare @ticketNum varchar(10)
set @profilenumberlength = 8
set @profileType = 2
set @ticketNum = '12345';
WITH cte1 AS
(
SELECT a.*
FROM APInvoices a WITH (NOLOCK)
JOIN Profiles p WITH (NOLOCK) ON a.ProfileNumber = p.ProfileNumber
AND Round(a.Balance, 2) <> 0
),
cte2 AS
(
SELECT a.InvoiceID AS ID, a.OriginalInvoiceDetailID, a.ARInvoiceID, 'S' AS Type, 'I' AS Department,
a.APTransactionType, a.InvoiceNumber, a.BranchNumber, a.ProfileNumber,
a.InvoiceDate, a.Description, a.PayableAccount AS AccountNumber, a.InvoiceAmount AS InvoiceAmount, a.Balance AS Amount,
a.CurrencyCode, ISNULL(a.ExchangeRate, 1) AS ExchangeRate, --Mike: Can we trap for that, i.e. case arinvoiceblaances.exchangerate = Null then 1 else exchangerate?
ISNULL(d.TicketNumber, '') AS TicketNumber,
a.ProfileName AS FullName,
a.CashSelected, a.ChequeSelected, a.OnHold,
a.DueDate,
Profiles.ProfileType,
Isnull(d.PassengerName, '') AS PassengerName,
d.TransactionDate,
d.TravelDate, d.ReturnDate, d.VatOnCommission AS VatOnCommission, d.ReservationID,
d.ChainCode, d.ProductCode, d.SubmitTo, d.VoucherStatus,
d.InvoiceDetailID, d.CommissionAmount, d.GrossAmount, d.GroupCode, d.PstOnCommission,
d.TransactionType,
d.CityCode,
d.TransactionCode
FROM APInvoices a WITH (NOLOCK)
JOIN Profiles WITH (NOLOCK) ON a.ProfileNumber = Profiles.ProfileNumber
JOIN ARInvoiceDetails d WITH (NOLOCK) ON d.InvoiceID = a.ARInvoiceID
WHERE
d.TransactionType IN (1, 3)
AND
(
(d.InvoiceDetailID = a.ARInvoiceDetailID)
OR
(
a.ARInvoiceDetailID = 0
AND a.ARInvoiceID = d.InvoiceID
AND a.TicketNumber <> '''' AND a.TicketNumber IS NOT NULL AND a.TicketNumber = d.TicketNumber
AND a.CurrencyCode = d.CurrencyCode
AND (
CASE WHEN (LEN(a.ProfileNumber) = 8 AND a.ProfileNumber = d.VendorNumber) OR LEN(a.ProfileNumber) = 6
THEN 1
ELSE 0
END) = 1
)
OR
(
a.ARInvoiceDetailID = 0
AND a.ARInvoiceID = d.InvoiceID
AND (a.TicketNumber = '''' OR a.TicketNumber IS NULL)
AND a.CurrencyCode = d.CurrencyCode
AND (
CASE WHEN (LEN(a.ProfileNumber) = 8 AND a.ProfileNumber = d.VendorNumber) OR LEN(a.ProfileNumber) = 6
THEN 1
ELSE 0
END) = 1
)
)
UNION
SELECT a.InvoiceID AS ID, a.OriginalInvoiceDetailID, a.ARInvoiceID, 'S' AS Type, 'I' AS Department,
a.APTransactionType, a.InvoiceNumber, a.BranchNumber, a.ProfileNumber,
a.InvoiceDate, a.Description, a.PayableAccount AS AccountNumber, a.InvoiceAmount AS InvoiceAmount, a.Balance AS Amount,
a.CurrencyCode, ISNULL(a.ExchangeRate, 1) AS ExchangeRate, --Mike: Can we trap for that, i.e. case arinvoiceblaances.exchangerate = Null then 1 else exchangerate?
ISNULL(a.TicketNumber, '') AS TicketNumber,
a.ProfileName AS FullName,
a.CashSelected, a.ChequeSelected, a.OnHold,
a.DueDate,
Profiles.ProfileType,
'' AS PassengerName,
NULL as TransactionDate,
NULL as TravelDate, NULL as ReturnDate, NULL as VatOnCommission, 0 as ReservationID,
'' as ChainCode, NULL as ProductCode, NULL as SubmitTo, NULL as VoucherStatus,
NULL as InvoiceDetailID, NULL as CommissionAmount, NULL as GrossAmount, '' as GroupCode, NULL as PstOnCommission,
NULL as TransactionType,
'' as CityCode,
'' as TransactionCode
FROM APInvoices a WITH (NOLOCK)
JOIN Profiles WITH (NOLOCK) ON a.ProfileNumber = Profiles.ProfileNumber
WHERE a.arinvoiceid = 0
)
SELECT DISTINCT TOP (2) t2.*
FROM cte2 t2
WHERE (t2.ProfileType = 2 OR t2.ProfileType = 3)
AND ((t2.SubmitTo = 2 AND t2.VoucherStatus = 0) OR t2.SubmitTo <> 2) AND Len(t2.profilenumber) = @profileNumberLength AND t2.ProfileType = @profileType AND t2.TicketNumber = @ticketNum;
There is absolutely no change in the query logic itself, just that inline constant values in WHERE clause are replaced by variables declared before-hand. This returned in like < 1 second. SQL Server Management Studio showed an execution time of 00:00:00.
If I add a "OPTION (RECOMPILE)" hint to the second query above (which was performing way better), leaving the variables intact, the performance degrades to the same as that of the first query.
The above performance results were consistent over hundreds of executions in a loop for each query variant.
Now I do understand from the literature SQL Server re-creates the execution plans for inline constant values while cached plans are used if the query contained variables. I also understand in some cases, inline constant values can generate better plans as the Query Optimiser can guess the number of rows better upfront if it has actual values during plan generation.
What baffles me is the time taken to generate the Execution plan itself. Is it normal for SQL Server to take 30+ seconds in generating an execution plan only? Please note the queries executed on a production strength server, 32 cores, 70 gig RAM (dedicated to SQL Server, there's more RAM for OS and other apps), with CPU utilisation hovering between 0-3% at the time tests were run (during late night hours).
3 tables in the query contained between 1 - 1.2 million rows each, all other participating tables hardly had a few hundred. The database in question itself is around 10 gigs.
