SQL游标的替代方法?[已解决] [英] Alternative for SQL Cursor?[Solved]

问题描述

我想知道哪种是SQL中游标的最佳选择.从VB 6.0开始，我在BL/DL中使用 Loop编码，很少使用 Cursor 进行行数据库操作.但是我想知道什么是游标的最佳替代品，因为它有很多缺点.我在Google上进行搜索，发现了诸如WHILE LOOP，TEMP Tables，用户定义的函数等选项，但我也希望得到CP人士的意见.

[注意:现在我正在使用WHILE LOOP.]

谢谢.
thatraja

解决方案

RDBMS专为基于集合的逻辑而设计.任何时候您可以通过设置操作执行的更新，选择，转换，插入和删除操作都将比光标选项无限快.

在SQL Server中，通常隐藏了游标的使用，因此让我向您展示一个快速提示，以了解何时有游标.每当您在数据上循环时，便会有一个游标.将数据加载到代码中时，必须使用游标执行此操作.因此，在C#中对数据进行while循环确实曾经在SQL Server中始终使用游标.

在您问之前，概述基于集合的操作可以完成的内容远远超出了快速提问的范围，甚至超出了CP文章的范围，因为该主题跨越了几门高等大学课程.

<我认为这取决于您想要做什么.我使用while循环和用户定义的函数

您的问题有点令人困惑，因为您在谈论游标，然后在谈论VB6.我猜您正在从VB6在SQL Server上打开一个游标.我通常不喜欢这种方法(如您所说)-但尤其是当您将光标连接到非临时表时.

如果您有机会重新设计数据库及其交互，那么我强烈建议您将尽可能多的逻辑转移到存储过程中，并避免在VB6中使用游标.

如果您不喜欢这种方式，那么我建议您使用游标或多个insert语句来加载临时表，然后使用TSQL使用临时表来插入/更新永久表.这个想法是通过将永久的表转换为很多小的动作或使其更快地运行来使所有长期运行的活动脱离永久表.

我使用SQL Server和VB6看到的最佳性能是通过使用断开的记录集进行读取(将整个记录集加载到客户端中，然后在其上循环时不使用游标).然后使用单独的多个插入物以另一种方式进行通信.这似乎是反直观的，但实际上它为RDBMS提供了优化自身的最佳机会.

Hi,

I want to know which is the best alternative for Cursor in SQL. Since VB 6.0, I am using Loop coding in BL/DL and rarely Cursor for row wise database operations. But I want to know what is the best alternative for cursor because it has many disadvantages. I was searching on Google and found options like WHILE LOOP, TEMP Tables, User defined functions, etc., but I want opinions from CP people too.

[Note: Now I''m using WHILE LOOP.]

Thank you.
thatraja

解决方案

RDBMS are designed for set based logic. Anytime update, select, transform, insert, and delete that you can perform with a set operation will be infinitely faster than the cursor option.

In SQL Server the use of a cursor is often hidden so let me show a quick tip to know when you have a cursor. Anytime you have a loop on data you have a cursor. When you load the data into code, you have to use a cursor to do so. Thus a while loop in C# on data did at one point use a cursor in SQL Server, always.

And before you ask, an overview of what can be accomplished in set based operations is well beyond the scope of a quick question, or even a CP article as the topic spans several upper level university courses.

I think it depends on what you want to do. I use while loops and user defined functions

Your question is a little confusing because you are talking about a cursor and then about VB6. I guess you are opening a cursor on SQL Server from VB6. I generally don''t like this approach (as you said) - but especially if you are connecting your cursor to a non temporary table.

If you have the chance to redesign the database and its interactions, then I would strongly suggest moving as much logic into stored procedures as possible and so avoid cursors in VB6 that way.

If you do not have this luxury, then I would suggest you use cursors or multiple insert statements to load up a temporary table and then use TSQL to use the temporary table to insert/update the permanent one. The idea is to get all long running activities off permanent tables by either converting them into lots of little actions or making them happen faster.

The best performance I have seen using SQL Server and VB6 is via using disconnected record sets for reads (the whole record set is loaded into the client and then when you loop over it you are not using a cursor). Then separate multiple inserts are used to communicate the other way. This might seem the counter intuitive, but it actually gives the RDBMS the best chance to optimize its self.

这篇关于SQL游标的替代方法?[已解决]的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！