1

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!

  • 2
    I think everyone should watch SQL Query Optimization: Why is it so hard to get right. Estimated and Actual Execution plans will give you different results with the latter being the accurate one. If the plan is in still in the plan cache, it is still valid and thus could be used. – scsimon Oct 24 at 20:51
  • I have cleared the plan cache using DBCC FREEPROCCACHE and it doesn't appear to make any difference, re-running afterwards still does not use the optimized plan. – Guest Oct 24 at 21:05
  • If you cleared the proc cache, there is no plan for it to use. It has to generate a new one. The plan it chooses to create is up to the optimizer and the video explains why that is hard. – scsimon Oct 24 at 21:14
  • 2
    Pastetheplan is a good site to use – scsimon Oct 24 at 23:25
  • 2
    The "bad plan" is a SELECT * but the "good plan" is a SELECT COUNT(*) so they are not the same queries. – MJH Oct 25 at 15:21
1

For the purposes of answering, let's label these as Query A and Query B. Query A and Query B are not identical (even if they produce equivalent results).

You cannot force SQL to use a plan generated for Query B when asking it to execute Query A.

USE PLAN is for running a specific plan when multiple plans exist for a single query. https://www.brentozar.com/archive/2018/03/why-multiple-plans-for-one-query-are-bad/

  • Ok, thanks, that makes sense... damn, no way to "fix" the bad query then, short of doing "bad" things" to the .dll. Argh. Thanks everyone for the help. – Guest Oct 28 at 14:45
  • I've never tried to do what you're looking to do, but there are a fair number of tuning improvements that you can typically make to a SQL install. There's also crazier ideas like building an interception program to listen in between SQL and your problem program and substitute the better query. Is the 3rd party defunct and so you can't ask them to fix it? – Rob Pearson Oct 28 at 17:00
  • Also, could you try downloading sentryone plan explorer and posting the graphic it generates of the bad plan? I'd like to take a look as a second pair of eyes on the indexing. – Rob Pearson Oct 28 at 17:02
  • 1
    Hey Rob: posted as an answer below as the answer allowed image post, but comments don't seem to. – Guest Oct 28 at 18:30
  • 1
    I ran Blitzindex the other day and it didn't find anything. However, after your suggestion I just ran blitzcache, and low-and-behold it suggested a new (and very lightweight) index that has sped up the query in my initial testing by 93%. That'll probably hold us for now if it works as well in Prod. Thanks for all your help, I'll report back if this doesn't work! – Guest Nov 1 at 15:08
0

enter image description here

Rob, here is the plan from sentryone plan explorer:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.