SQL Server 2005 中的拦截和重写查询 [英] Intercept and Rewrite Queries in SQL Server 2005
问题描述
我们有一个基于 SQL Server 2005 构建的应用程序，我们无法控制它.我们最近发现，该应用程序向 SQL 发送了一些非常低效的 SELECT 查询，这会导致数据库出现严重的容量问题.我知道正确的解决方案是打开应用程序的代码并更改查询，但由于我不会讨论的原因，这将花费很长时间.有什么方法可以拦截 SQL 服务器上的这个特定查询，并有选择地将其重写为更优化的内容吗?
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?
推荐答案
您可以使用这种方法.它对我来说就像一个魅力:)
You could use this approach. It works like a charm for me:)
而不是试图拦截并修改源自应用程序，也许你可以而是实现一个抽象层无需更改应用程序的SQL.例如，如果您可以修改DSN 或登录连接字符串应用程序，然后考虑下列的.让我们假设当前数据库是 [A].创建一个新的数据库[B] 包含视图和函数(但不是表)同名[A] 中的内容，然后将它们修改为参考 [A] 中的表格.添加无论额外的连接，过滤，等需要实现您的(我假设)基于行安全.然后，修改应用程序DSN 使用数据库 [B] 而不是[A].
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].
这篇关于SQL Server 2005 中的拦截和重写查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
