在表中获取许多记录时在Oracle DBA中有很多记录的问题 [英] Problem when get a number of record in a table have alot of record in Oracle DBA

问题描述

大家好，
我有C#代码

Hi everybody,
I have C# codes

OracleConnection cnn
   = new OracleConnection("data source=orcl;persist security info=True;user id=xldt;password=xldt");
cnn.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = cnn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "Select * from sodk where sodk_id between 1 and 20";
OracleDataAdapter da = new OracleDataAdapter(cmd);
cmd.ExecuteOracleScalar();
DataSet ds = new DataSet();
da.Fill(ds);

该代码将从一个名为"Sodk"的表中获取20条记录. "Sodk"总共有60000条记录.

当我执行此代码并在VS2008中对其进行调试时，当它运行到代码行" da.Fill(ds)"时，就会出现问题.它在那里呆了很多时间(大约15秒).
我不知道为什么，因为我只选了20条记录.为什么这么长呢?

之后，我使用了另一种方法来更改数据，方法是使用"cmd.CommandType = CommandType.StoredProcedure"，并希望它更快.

This code will take 20 records from a table which have name is "Sodk". "Sodk" have total 60000 records.

When I execute this code and debug it in VS2008, and problem appear when it run to the code line "da.Fill(ds)". It spend alot of time in there (about 15 second).
I didn''t known why because I only selected out 20 record. Why it so long like that.

After that I used another the way to get data by change to using "cmd.CommandType =CommandType.StoredProcedure" and hope it faster

cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "SODK_GETPAGED";
cmd.Parameters.Add("p_WhereClause", OracleType.NVarChar);
cmd.Parameters.Add("p_OrderBy", OracleType.NVarChar);
cmd.Parameters.Add("p_PageIndex", OracleType.Number);
cmd.Parameters.Add("p_PageSize", OracleType.Number);
cmd.Parameters.Add("cur_OUT", OracleType.Cursor);
cmd.Parameters["cur_OUT"].Direction = ParameterDirection.Output;
cmd.Parameters["p_PageIndex"].Value = 0;
cmd.Parameters["p_PageSize"].Value = 20;
OracleDataAdapter da = new OracleDataAdapter(cmd);
cmd.ExecuteOracleScalar();
DataSet ds = new DataSet();
da.Fill(ds);

但不是更好，它像在步骤"da.Fill(ds)"中一样花费时间.

然后我删除表Sodk中的部分记录，而Sodk保留为20000条记录.

下一步，我在上面执行了代码，结果更好，代码"da.Fill(ds)"中的时间减少了.

我真的不知道要解决这个问题.
如果有人知道为什么，请告诉我.

But not better, It spend among time like before in step "da.Fill(ds)".

Then I delete a part of record in table Sodk, and Sodk remain 20000 record.

Next step I executed code above and the result was better, time was reduce in code "da.Fill(ds)".

I really don''t know to resolve this problem.
If anybody know why,please tell me.
Thanks alot!

推荐答案

表格Sodk有多少列?

How many columns does the tabl Sodk have?

我的表"Sodk"有97个列.但是我只在StoredProcedure中选择10列

My table "Sodk" have 97 colunms. But I only select out 10 columns in my StoredProcedure

这篇关于在表中获取许多记录时在Oracle DBA中有很多记录的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！