Click here to Skip to main content
15,117,830 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi everybody,
I have C# codes
C#
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

C#
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!
Posted
Updated 26-Jan-10 2:59am
v2

How many columns does the tabl Sodk have?
   
My table "Sodk" have 97 colunms. But I only select out 10 columns in my StoredProcedure
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900