动态sql存储过程对于大量记录来说是一件坏事吗？

我有一个包含近800,000条记录的表,我目前正在使用动态sql在后端生成查询.前端是一个搜索页面,它占用大约20个参数,并且根据是否选择了参数,它会向基本查询添加"AND ...".我很好奇动态sql是否是正确的方法(似乎不是因为它运行缓慢).我正在考虑用我的所有数据创建一个非规范化表.这是一个好主意还是我应该一起构建查询,而不是使用动态sql逐个构建它.最后一点,有没有办法加快动态SQL？

您的索引(或缺少索引)更可能导致比动态SQL慢.

执行计划是什么样的？在SSMS中执行时查询速度是否相同？什么时候它在存储过程中？

如果您的表是一个未编制索引的堆,它将随着记录数量的增长而表现不佳 - 这与查询无关,并且随着表性质的变化,动态查询实际上可以更好地执行,因为动态查询更有可能具有查询当计划不在缓存中时,计划会重新评估.这通常不是问题(我不会将其归类为动态查询的设计优势),除非在系统的早期阶段没有重新编译SP,但统计和查询计划已经过时,但是数据刚刚发生了巨大的变化.

还不是静态的.我有动态查询,但它没有给出任何优化.如果我使用静态查询运行它并给出了建议,应用它们会影响动态查询吗？ - Xaisoft(41分钟前)

是的,除非您分析了工作负载文件,否则可能不会分析动态查询(EXEC(@sql)). - Cade Roux(33分钟前)

当您在多个连接的表中进行搜索查询时,带索引的列必须是搜索列以及主键/外键列 - 但它取决于各个表的基数.调谐分析仪应该显示这一点. - Cade Roux(22分钟前)

我只想指出,如果你使用这种可选参数:

AND (@EarliestDate is Null OR PublishedDate < @EarliestDate)

查询优化器在生成查询计划时不知道参数是否存在.我见过优化器在这些情况下做出错误选择的情况.更好的解决方案是构建仅使用所需参数的sql.在这些情况下,优化器将制定最有效的执行计划.请务必使用参数化查询,以便它们可以在计划缓存中重复使用.