4

We have an application built on top of SQL server 2005 that we have no control over. We've recently discovered that this application is sending some very inefficient SELECT queries to SQL that is causing serious capacity issues on the database. I know the right solution is to crack open the code for the application and change the queries, but for reasons I won't go into that's going to take a very long time. Is there some way we can intercept this specific query on SQL server and selectively re-write it to something more optimal?

Andrew Burke
  • 93
  • 1
  • 7
  • If this turned out to be possible, it would be the worst hack I've seen so far this year. If you can't fix the queries, then you need to buy more hardware to improve the performance of the queries. The cost of the extra hardware will teach you why you should arrange to be able to fix bugs in code. – John Saunders Jun 24 '09 at 05:42
  • I appreciate the sentiment. This whole thing is making me cringe, but we have no control over the application, nor do we have the source code. If I owned all the pieces here or had any control over the original architecture choices, we would would have never been in this position to begin with, but as is often the case in business, I am inheriting somebody else's mess. – Andrew Burke Jun 24 '09 at 05:51
  • What is the application responsible for this dodginess, is it a C# / .Net app. or is it coming from c++ land – Sam Saffron Jun 24 '09 at 06:03
  • The app is a mix of .NET and COM. The object that is making the SQL call appears to be C++/COM. – Andrew Burke Jun 24 '09 at 07:44

5 Answers5

2

You could use this approach. It works like a charm for me:)

Rather than attempting to intercept and modify SQL calls originating from the application, perhaps you can instead implement an abstraction layer without changing the application's SQL. For example, if you can modify the DSN or login connection string for the application, then connsider the following. Let's assume the current database is [A]. Create a new database [B] that contains views and functions (but not tables) with the same name as what is in [A], then modify them to reference the tables in [A]. Add whatever additional joins, filtering, etc. are needed to implement your (what I'm assuming) row based security. Then, modify the application DSN to use database [B] instead of [A].

link

Sergey Mirvoda
  • 3,209
  • 2
  • 26
  • 30
2

You could try plan guides. This may allow you to tune/optimise the queries without changing the actual call.

From "Understanding Plan Guides"

This procedure can be used when you cannot or do not want to change the text of the query directly. Plan guides can be useful when a small subset of queries in a database application deployed from a third-party vendor are not performing as expected. Plan guides influence optimization of queries by attaching query hints to them.

This could also be useful to make the query really run like a lame dog, so that the developers come and ask for your help... ;-)

gbn
  • 422,506
  • 82
  • 585
  • 676
1

It depends on what they are doing and what the queries are. Of course, if they are using sprocs or UDF's you can replace those without changing the application. You can also consider adding some indexes that are "optimized" for their bad SQL (though that may affect legitimate users of the database). You might also check the queries they are doing and see if you can replace the tables they are hitting with a more efficient view, but then you are messing with your DDL just to deal with a bad apple. Your best bet is probably to migrate legitimate applications off that particular server and leave the offender alone to rot.

JP Alioto
  • 44,864
  • 6
  • 88
  • 112
1

Have you taken a resource editor/reflector to the executable files? If you're lucky and the SQL Statements are static you may be able to change them.

Without more info about the app it's difficult to determine if this is possible. If the SQL is dynamically generated then this isn't an option.

Joel Mansford
  • 1,306
  • 7
  • 13
1

You always have the option to use a database proxy (e.g. Gallium Data) to rewrite the queries before they hit the database.

Is that the right thing to do? Well, it's certainly not as clean as updating the app, but it may be your only option.

Some may consider this "hacky", but when you have a choice between a hacky solution and no solution at all, the hacky solution usually wins.

Max Tardiveau
  • 31
  • 3
  • I recommend against rhetoric questions in answers. They risk being misunderstood as not an answer at all. You are trying to answer the question at the top of this page, aren't you? Otherwise please delete this post. – Yunnosch Mar 27 '22 at 20:31