对于大量记录来说，动态 sql 存储过程是一件坏事吗? [英] Is a dynamic sql stored procedure a bad thing for lots of records?

问题描述

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

I have a table with almost 800,000 records and I am currently using dynamic sql to generate the query on the back end. The front end is a search page which takes about 20 parameters and depending on if a parameter was chosen, it adds an " AND ..." to the base query. I'm curious as to if dynamic sql is the right way to go ( doesn't seem like it because it runs slow). I am contemplating on just creating a denormalized table with all my data. Is this a good idea or should I just build the query all together instead of building it piece by piece using the dynamic sql. Last thing, is there a way to speed up dynamic sql?

推荐答案

与动态 SQL 相比，您的索引(或缺乏索引)更有可能导致速度缓慢.

It is more likely that your indexing (or lack thereof) is causing the slowness than the dynamic SQL.

执行计划是什么样的?在 SSMS 中执行相同的查询时是否很慢?当它在存储过程中时呢?

What does the execution plan look like? Is the same query slow when executed in SSMS? What about when it's in a stored procedure?

如果你的表是一个未索引的堆，随着记录数量的增加，它的性能会很差——这与查询无关，动态查询实际上可以随着表性质的变化而表现得更好，因为动态查询更有可能当它不在缓存中时，重新评估它的查询计划.这通常不是问题(我不会将其归类为动态查询的设计优势)，除非在系统的早期阶段，此时 SP 尚未重新编译，但统计信息和查询计划已过时，但是数据刚刚发生了巨大变化.

If your table is an unindexed heap, it will perform poorly as the number of records grows - this is regardless of the query, and a dynamic query can actually perform better as the table nature changes because a dynamic query is more likely to have its query plan re-evaluated when it's not in the cache. This is not normally an issue (and I would not classify it as a design advantage of dynamic queries) except in the early stages of a system when SPs have not been recompiled, but statistics and query plans are out of date, but the volume of data has just drastically changed.

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

Not the static one yet. I have with the dynamic query, but it does not give any optimizations. If I ran it with the static query and it gave suggestions, would applying them affect the dynamic query? – Xaisoft (41 mins ago)

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

Yes, the dynamic query (EXEC (@sql)) is probably not going to be analyzed unless you analyzed a workload file. – Cade Roux (33 mins ago)

当您跨多个连接的表进行搜索查询时，带有索引的列需要是搜索列以及主键/外键列 - 但这取决于各种表的基数.调谐分析器应该显示这一点.– Cade Roux(22 分钟前)

When you have a search query across multiple tables that are joined, the columns with indexes need to be the search columns as well as the primary key/foreign key columns - but it depends on the cardinality of the various tables. The tuning analyzer should show this. – Cade Roux (22 mins ago)

这篇关于对于大量记录来说，动态 sql 存储过程是一件坏事吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！