数据挖掘/检索问题
我们的 SQL Server 2005 数据库有近 100 个不同的表。我们将提供一个功能来根据用户定义的过滤器搜索一些信息。我们的过滤器配置将非常复杂。用户可以轻松创建一个过滤器,连接 20 多个具有复杂条件的表。
为了降低连接的复杂性,我们计划创建几个 SQL Server 视图。但该解决方案本身似乎不足以简化 SQL。我们将根据过滤器配置生成 SQL。 SQL 将动态生成。 (我不确定 SQL 会有多长...SqlCommand 有最大长度吗?) SP 是另一种选择...
您遇到过类似的情况吗?您对我有什么好的建议/解决方案吗?
谢谢你!
Our SQL Server 2005 database has nearly 100 different tables. We are going to provide a feature to search some information according to the user defined filters. Our filter configuration going to be quite complicated. A user can easily create a filter that joins over 20 tables with complicated conditions.
In order to reduce the complexity of the joining, we are planning to create couple of SQL Server views. But that solution itself doesn't seems to be enough to simplify the SQL. We are going to generate the SQL according to the filter configuration. The SQL will be generated dynamically. (I am not sure how long that SQL would be… Is there any maximum length for SqlCommand?) SP is another option...
Have you come across a similar situation? Do you have any good advise/solution for me?
Thank you!
您应该阅读动态 SQL 的诅咒与祝福,以了解其中的一些陷阱。
You should read The Curse and Blessings of Dynamic SQL to be aware of some of the pitfalls.