自定义 ADO.NET 提供程序来拦截和修改 sql 查询
我们的客户有一个在数据库中存储 blob 的应用程序，该应用程序现在已经增长到足以影响 SQL Server 的性能。为了解决这个问题，我们计划将所有 blob 卸载到文件系统，并将文件路径保留在用户表的新列中。
就像用户有一个包含 id、name 和 content (blob) 列的表 docs 一样；我们会要求他在此表中添加一个新列“文件路径”。
我们的客户愿意对此数据库进行更改。但是，当涉及到更改 sql 查询以读取和写入此表时，他们还没有准备好接受这一点。实际上，他们不希望任何导致重新编译和部署的更改。
现在我们计划编写一个自定义 ADO.NET 提供程序，它将
- 拦截选择查询
- ，在选择语句末尾添加“文件路径”列
- ，检索结果集并根据“文件路径”值修改“内容”列
值您认为这种方法肯定会失败吗？
我知道这听起来很脏，但是我们有更好的方法吗？
Our client has an application that stores blobs in database which has now grown enough to impact the performance of SQL Server. To overcome this issue, we are planning to offload all blobs to file system and leave the path of file in a new column in user table.
Like if user has a table docs with columns id, name and content (blob); we would ask him to add a new column 'filepath' in this table.
Our client is willing to make this change in this database. But when it comes to changing the sql queries to read and write into this table, they are not ready to accep this. Actually, they don't want any change that results in recompilation and deployment.
Now we are planning to write a custom ADO.NET provider that will
- intercept the select queries
- add a column 'filepath' at the end of the select statement
- retieve the result set and modify the 'content' column value based on 'filepath' value
Is there any use case that you think will certainly fail with this approach?
I know this sounds dirty but do we have a better way?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您是否尝试过将该列切换到
FILESTREAM数据类型？这会完成您所描述的所有操作，将数据推出数据库（到文件系统上）。当然，访问仍然需要像TDS一样通过服务器，但您可能会发现影响较小，并且您可以逐步更改代码以更直接地访问文件（为了提高性能） - 然而，最后一步将涉及更改查询。Have you tried switching the column to the
FILESTREAMdata type? This does everything you describe, pushing the data out of the database (onto the file-system). Of course, access still needs to go though the server as TDS, but you may find the impact is less, and you can progressively change code to access the file more directly (for improved performance) - this last step would involve altering queries, however.