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);
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);
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!