数据挖掘/检索问题
我们的 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!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您应该阅读动态 SQL 的诅咒与祝福，以了解其中的一些陷阱。
You should read The Curse and Blessings of Dynamic SQL to be aware of some of the pitfalls.
我建议创建一个报告数据库来对数据进行非规范化，这样您就不需要复杂的查询。还要确保所有查询都被参数化以避免 SQL 注入攻击。
希望这有帮助。
I would suggest creating a reporting database that de-normalizes your data, so you don't need the complicated queries. Also make sure that any queries are parameterized to avoid sql injection attacks.
Hope this helps.