sql-server - 动态查询优化

Question

我有一项业务任务，基本上就是从数据库 (Microsoft SQL Server 2008) 中提取数据。在此过程中，用户将能够选择要选择的列、选择要从哪个 View 中选择以及构建 WHERE 子句。根据用户的选择，相应地构建 SQL 查询。要求是用户可以从任何 View 中选择任何列，并按 WHERE 子句中的任何列进行过滤。公司不希望解决方案使用数据仓库/OLAP，并希望限制任何第三方软件。所以基本上他们只需要一个 .NET Windows Forms 应用程序，它可以基于 GUI 动态构建 SQL 查询并连接到数据库。

我关心的是如何优化查询。我绝不擅长优化 SQL 查询，但我的第一个想法是:如果用户选择按没有索引的列(在 WHERE 子句中)进行过滤怎么办？通过为用户提供如此大的灵 active ，他们可能会构建非常低效的查询，以至于需要很长时间才能执行。

我意识到，如果对没有索引的列进行过滤，那么对于大量数据，性能永远不会好，但是我可以做些什么来改进它吗？当然，我不能只向所有列添加索引。

我不一定只是在寻找查询优化，但我也在考虑是否可以对服务器进行任何调整，例如缓存？基本上，我会洗耳恭听，寻找任何可以帮助我提高表现的建议。

有什么建议吗？

提前致谢!

Answer 1

除了预见用户可能会做什么之外，您真的无能为力。您处于让 SQL Server 优化器为您完成艰苦工作的有利位置(想象一下在键值存储上构建它!)。

我会在最有可能被过滤或排序的列上创建索引。您应该尝试将这些索引过滤为非空值，这将降低存储成本(假设用户不会过滤空值)。

您还可以尝试使用索引 View 预先计算常见的连接和聚合。如果您愿意在这个问题上投入疯狂数量的 RAM 并且愿意进行缓慢的写入，那么您可以索引并具体化这个数据库。

最后，您可以在只读日志传送目标或类似目标上卸载用户查询。这将沙箱他们可怕的查询。

对于您的查询，您需要对它们进行 dparameterize，但您不需要在所有情况下都缓存它们。如果您的查询往往成本很高(因此编译时间无关紧要)，您将希望使用 OPTION RECOMPILE 运行它们，以便 SQL Server 可以适应所有参数的确切运行时值。

您还应该监控所有查询并查看它们以寻找模式。您的用户可能一直在运行非常相似的查询。他们的索引。

定期运行 sp_updatestats。

最后，我想说，对此没有非常有效的解决方案，因为如果有 SQL Server 会自己实现它们，那么每个人都可以受益。