哪种是性能最佳的页面或存储过程嵌套查询的最佳选择? [英] which is best for good performance nested query on page or stored procedure ?

问题描述

哪种是最佳性能(快速渲染，快速访问，搜索)
在嵌套查询或存储过程中?以及为什么..

Which is best for good performance(fast rendering, quick access,search)
in nested query or stored procedure ? and why..

推荐答案

存储过程:其可重用且已预先完成.因此，每次调用时都会节省编译时间.

Stored Procedure : Its reusable and pre-complied. So, will save compilation time each time when being called.

始终使用存储过程，但........
除外
当您实现多级搜索功能/数据提取时，如果用户向下钻取更详细的信息，则可能需要点击更多表.在这种情况下，使用代码在运行时构建查询并执行该查询将获得最佳结果.如果编写存储过程，则可能需要连接其他基于用户输入可能不相关的表，或者在sp中使用动态sql.两者都是低效的方式.参数嗅探问题也可能发生.

从SQL Server 7.0起，SQL Server不会预编译存储过程.执行计划在运行时创建并缓存以备后用.普通选择查询也是如此.

Always use stored procedures except........

When you are implementing a multi-level search functionality/data extraction which may need to hit more tables if the user drills down to finer details. In this case building the query at run time using the code and executing it would give the best results. If you write stored procedure you may need to join additional tables which may not be relevant based on the user input or use dynamic sql inside sp. Both are inefficient ways. Parameter sniffing issues can also occur.

SQL server DOES NOT PRECOMPILE stored procedures from SQL Server 7.0 onwards. Execution plans are created at run time and cached for later use. It does the same for normal select queries as well.

与存储过程相比，嵌套查询会更快.

Link1 [Link2 [ ^ ]和 Link3 [ ^ ]可能会帮助您

Nested Query will be fast when compared to Stored Procedure.

Link1[^],Link2[^] and Link3[^] might help you

这篇关于哪种是性能最佳的页面或存储过程嵌套查询的最佳选择?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！