使用RefCursor准备语句与存储过程

I've been calling Oracle stored procedures that return RefCursors in my C# application. A sample stored procedure is given below.

我一直在调用Oracle存储过程,它在我的C#应用程序中返回RefCursors。下面给出了一个样本存储过程。

CREATE OR REPLACE
PROCEDURE "DOSOMETHING"(
    P_RECORDS OUT SYS_REFCURSOR)
AS
BEGIN
    OPEN P_RECORDS FOR
    SELECT SOMETHING FROM SOMETABLE;
END;

When using the OracleDataReader to read the results for this, everytime the procedure is called the database parses the procedure. After quite a bit of searching I found out that eliminating this parse call is impossible with .NET when using RefCursor.

使用OracleDataReader读取结果时,每次调用该过程时,数据库都会解析该过程。经过相当多的搜索后,我发现在使用RefCursor时,使用.NET无法消除此解析调用。

However if I just call the procedure using a prepared statement as below, this parse call can be avoided.

但是,如果我只是使用如下的预准备语句调用该过程,则可以避免此解析调用。

public void DoSomething()
{
    var command = ServerDataConnection.CreateCommand();
    command.CommandType = CommandType.Text;
    command.CommandText = "SELECT SOMETHING FROM SOMETABLE";
    command.Prepare();

    using (var reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            DoSomethingToResult();
        }
    }
}

My question is which of these methods will have a minimum performance impact? Will changing the procedures to prepared statements to avoid parse calls have an even more negative impact on the application performance?

我的问题是,哪种方法对性能影响最小?将程序更改为准备好的语句以避免解析调用会对应用程序性能产生更大的负面影响吗?

Please note that these select statements can return a large set of results. Possibly thousands of rows.

请注意,这些select语句可以返回大量结果。可能有数千行。

1 个解决方案

#1

Using a ref cursor in PL/SQL will provoke a parse call each time the cursor is opened. The same parse call will be issued each time you call command.Prepare(). As it is now, your .NET code would parse the query as much as the PL/SQL code.

在PL / SQL中使用引用游标将在每次打开游标时引发解析调用。每次调用command.Prepare()时都会发出相同的解析调用。就像现在一样,您的.NET代码将像PL / SQL代码一样解析查询。

You could reuse your command object without additional parse calls if you need to issue the exact same query (with just a change in parameters). However, those parses would be soft parses so the performance may not be noticeable (most of the work is done in the hard parse, the first time the database encounters a query). Since your query returns lots of rows, the amount of work involved in a soft parse is certainly negligible compared to the amount of work to actually fetch those rows.

如果需要发出完全相同的查询(只需更改参数),则可以重复使用命令对象而无需额外的解析调用。但是,这些解析将是软解析,因此性能可能不明显(大多数工作是在硬解析中完成的,这是数据库第一次遇到查询时)。由于您的查询返回了大量行,因此与实际获取这些行的工作量相比,软解析中涉及的工作量肯定可以忽略不计。

秒客网

使用RefCursor准备语句与存储过程

1 个解决方案

#1

#1

相关文章