带存储过程的 SQL 游标与带 UDF 的查询
我正在尝试优化我正在维护的存储过程，并且想知道是否有人可以告诉我以下选项的性能优点/缺点。 对于我的解决方案，我基本上需要对存储在表中 IMAGE 列中的图像运行转换程序。 转换过程位于外部 .EXE 文件中。 以下是我的选择：
将目标表的结果拉入临时表，然后使用游标遍历表中的每一行并在 IMAGE 列上运行存储过程。 存储过程调用 .EXE。
创建一个调用 .EXE 文件的 UDF，并运行类似于“select UDFNAME(Image_Col) from TargetTable”的 SQL 查询。
创建
我想我正在寻找的是创建游标会增加多少开销，而不是作为一个集合来执行？
一些附加信息：
- 在这种情况下，集合的大小是最大的。 1000
- 正如下面的答案提到的，如果与UDF一起完成，是否意味着外部程序一次打开1000次？ 或者是否有相应的优化？ 显然，在多处理器系统上，运行多个进程实例可能并不是一件坏事，但 1000 个可能有点多了。
I'm trying to optimize a stored procedure I'm maintaining, and am wondering if anyone can clue me in to the performance benefits/penalities of the options below. For my solution, I basically need to run a conversion program on an image stored in an IMAGE column in a table. The conversion process lives in an external .EXE file. Here are my options:
Pull the results of the target table into a temporary table, and then use a cursor to go over each row in the table and run a stored procedure on the IMAGE column. The stored proc calls out to the .EXE.
Create a UDF that calls the .EXE file, and run a SQL query similar to "select UDFNAME(Image_Col) from TargetTable".
I guess what I'm looking for is an idea of how much overhead would be added by the creation of the cursor, instead of doing it as a set?
Some additional info:
- The size of the set in this case is max. 1000
- As an answer mentions below, if done as a set with a UDF, will that mean that the external program is opened 1000 times all at once? Or are there optimizations in place for that? Obviously, on a multi-processor system, it may not be a bad thing to have multiple instances of the process running, but 1000 might be a bit much.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
在这种情况下定义集合基数？
如果您有 100 行，这会一次性打开应用程序 100 次吗？ 我会说测试，只是因为您可以从 UDF 调用扩展过程，所以我仍然会使用游标，因为在这种情况下 setbased 并不重要，因为您没有直接操作表中的数据
define set base in this context?
If you have 100 rows will this open up the app 100 times in one shot? I would say test and just because you can call an extended proc from a UDF I would still use a cursor for this because setbased doesn't matter in this case since you are not manipulating data in the tables directly
我做了一些测试和实验，当在 UDF 中完成时，它确实一次处理每一行 - SQL Server 不会为 100 行中的每一行运行 100 个进程（我认为不会）。
然而，我仍然相信作为 UDF 而不是作为游标来执行此操作会更好，因为我的研究往往表明，必须在游标中提取数据的额外开销会减慢速度。 它可能不会产生巨大的差异，但与首先将所有数据提取到临时表中相比，它可能会节省时间。
I did a little testing and experimenting, and when done in a UDF, it does indeed process each row at a time - SQL server doesn't run 100 processes for each of the 100 rows (I didn't think it would).
However, I still believe that doing this as a UDF instead of as a cursor would be better, because my research tends to show that the extra overhead of having to pull the data out in the cursor would slow things down. It may not make a huge difference, but it might save time versus pulling all of the data out into a temporary table first.