I have a 3rd party application that I cannot change that runs a "bad" select query that can run very slowly in some circumstances (we have killed it after 6 hrs on occasion). This is after we've done what we could to speed it up via indexing, which is something we can control.
SELECT *
FROM findetail
WHERE fintransact is null
and (validation is null or validation <= ' ' or validation = ' Zero value transaction;')
and (exists (select *
from counterparty
where counterparty.counterparty = findetail.counterparty
and counterparty.status = 'ACTIVE')
or findetail.counterparty is null)
and (findetail.acctstatus = 'FINAL' or findetail.acctstatus = 'PROVISIONAL')
and (findetail.transactiontype = 'AP' or findetail.transactiontype = 'AR')
and ((findetail.position not in (select position.position
from position
where position.positionmode = 'FINANCIAL'
and position.exchange is not null)
)
or (findetail.fee in (select fee
from fee, feetype
where findetail.fee = fee.fee
and fee.feetype = feetype.feetype
and feetype.excludemargin = 1 )
)
)
and ( findetail.counterparty = 'ACPTY' )
It is easy to re-write the query to be at least somewhat "better":
SELECT *
FROM findetail
WHERE fintransact is null
and (validation is null or validation <= ' ' or validation = ' Zero value transaction;')
and (exists (select *
from counterparty
where counterparty.counterparty = findetail.counterparty
and counterparty.status = 'ACTIVE')
or findetail.counterparty is null)
and (findetail.acctstatus = 'FINAL' or findetail.acctstatus = 'PROVISIONAL')
and (findetail.transactiontype = 'AP' or findetail.transactiontype = 'AR')
and (not exists (select position.position
from position
where position.positionmode = 'FINANCIAL'
and position.exchange is not null
and position = findetail.position)
or exists (select fee
from fee, feetype
where findetail.fee = fee.fee
and fee.feetype = feetype.feetype
and feetype.excludemargin = 1
and findetail.fee = fee)
)
AND ( findetail.counterparty = 'ACPTY' )
This one is still not perfectly optimal but runs in about 8s which is much preferable to multiple hours. It also keeps the rough structural format of the original which is important because a last-ditch option is to try and edit the query string (which is visible but broken into sections as it’s dynamically built up using some “if” statements in the code) in one of the third party application’s .dll’s.
Aside: When I completely re-wrote the query to be "even better" (down to 3s runtime) using proper joins, SQL server would no longer recognize this "even better" execution plan as valid for the "bad" query when I tried to use it via USE PLAN query hint. Plus, it wouldn’t be possible to edit that one back into the .dll if I end up trying that.
So I captured the execution plan of the "better" query, and tried to force it onto the "bad" query using the USE PLAN hint. SQL Server accepts the forced plan as valid (at least, doesn't complain like it did for the "even better" plan), but it also doesn't actually use the "better" plan. Show estimated execution plan shows the same bad plan as before, and actually executing results in the query running for at least 10 minutes before I killed it.
I've tried this in SSMS with both a USE PLAN hint at the end, and through creating a stored query plan. Either way, SQL Server when the query is run continues to use the "bad" plan. Any ideas on why if it thinks the plan is valid it’s still not using it?
Any help is appreciated, Thanks!