2

I am supporting a third-party application which uses a SQL Server 2016 backend. We have no source code access, and the third-party support is slow to respond. Most of the time, I can index and magic my way out of the sometimes terrible queries that it throws at the database, but there have been multiple occasions where there is quite literally nothing I can do (within constraints of time, money, and storage). The query is just wrong.

Is there a way to administratively substitute the queries submitted by the application for other ones? I am imagining some sort of regex match performed against the batch if the application name matches.

Example:

-- Submitted by application
update Table set IsBilled = 0 where ID = '{EFD5F13C-6123-4913-B530-8941FC71B969}'

-- What I want the query to be (to avoid spurious updates to Table)
-- (Table is large, and the application is submitting this query on every page view)
update Table set IsBilled = 0 where ID = '{EFD5F13C-6123-4913-B530-8941FC71B969}' and IsBilled = 1

-- Proposed regex
-- Match
update Table set IsBilled = 0 where ID = '(\{[A-F0-9\-]+\})'
-- Replace
update Table set IsBilled = 0 where ID = '$1' and IsBilled = 1

Some of my conceived uses would be to avoid useless updates, others would be in rephrasing where clauses to maintain sargability. I am sure there are other scenarios that would come to mind.

8
  • maybe you could use an INSTEAD OF trigger in some of your tables
    – Lamak
    Aug 14 '17 at 15:48
  • @Lamak, Sure, but that would not help with issues in sargability and definitely wouldn't help maintainability. Consider WHERE CAST(EventDate as varbinary(8)) = @EventDate. I could create a computed column and a new index, but the obvious fix is to just remove the cast or to put the cast on @EventDate instead of the column. Table of 200 million rows, that extra index for one query costs quite a bit - especially when you consider that the application loves to use 250 byte composite keys. Multiply this issue by 10 or 15 different queries against this one table...
    – Mitch
    Aug 14 '17 at 15:51
  • I'm not sure you should be doing anything to a 3rd party application's db schema. You might break the next upgrade they put out, or your "fixes" could cause issues in the future. I'm surprised they support you at all if you've been making your own customizations.
    – Andy
    Aug 24 '17 at 1:15
  • @Andy, Making modifications to third party databases certainly has risk and must consider contractual impact, but it can be a bit of an academic argument when you have hundreds of users unable to perform their jobs and a vendor which will not fix the problem quickly. We use a separate environment with a vanilla schema to prove issues to the vendor. Besides, the question was about avoiding having to resort to schema changes.
    – Mitch
    Aug 24 '17 at 1:53
  • Do you know which database interface library/driver the application is using (.Net SqlClient, ODBC, etc.)? Aug 24 '17 at 16:35
5

Is there a way to administratively substitute the queries submitted by the application for other ones?

No, the closest you will get with out of the box functionality is what a plan guide can do.

I am imagining some sort of regex match performed against the batch if the application name matches.

Nothing native inside of SQL Server can do this, currently.

Can I edit the batches received by SQL Server on the fly?

Unfortunately (or fortunately) there is nothing built in to do this. I don't want to give you any ideas but you'd need to have a middle layer between the application and the database server. This intercepting application would need to understand TDS (which is the protocol used with SQL Server) and allow you make changes to (or, again, not) the TDS packets to change the queries on the fly.

If you made this, you should sell it :)

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.