performance issue

[orturjamen1990]

hey I used dapper to work with oracle db with sp.. the problem that I saw working with OracleDataAdapter brings the data faster then dapper, I dont know why..

here my both examples: OracleDataAdapter example :

List<OracleParameter> parameters = new List<OracleParameter>(); parameters.Add(new OracleParameter("ItemID_in", OracleDbType.Int32, filters.ItemId, ParameterDirection.Input)); parameters.Add(new OracleParameter("PlayerID_in", OracleDbType.NVarchar2, null, ParameterDirection.Input)); parameters.Add(new OracleParameter("PlayerNickName_in", OracleDbType.NVarchar2, null, ParameterDirection.Input)); parameters.Add(new OracleParameter("PlayerLoginName_in", OracleDbType.Int32, null, ParameterDirection.Input)); parameters.Add(new OracleParameter("StartDate_in", OracleDbType.Date, filters.StartDate, ParameterDirection.Input)); parameters.Add(new OracleParameter("EndDate_in", OracleDbType.Date, filters.EndDate, ParameterDirection.Input)); parameters.Add(new OracleParameter("BoUser_in", OracleDbType.Varchar2, null, ParameterDirection.Input)); parameters.Add(new OracleParameter("BOUserID_in", OracleDbType.Int32, 0, ParameterDirection.Input)); parameters.Add(new OracleParameter("ActionDescription_in", OracleDbType.NVarchar2, null, ParameterDirection.Input)); parameters.Add(new OracleParameter("ActionType_in", OracleDbType.NVarchar2, "-1" , ParameterDirection.Input)); parameters.Add(new OracleParameter("ReferenceID_in", OracleDbType.Int32, 0, ParameterDirection.Input)); parameters.Add(new OracleParameter("IsError_in", OracleDbType.Int32, "-1", ParameterDirection.Input)); parameters.Add(new OracleParameter(SQLParams._PageIndex, OracleDbType.Int32, pagaingParams.PageIndex, ParameterDirection.Input)); parameters.Add(new OracleParameter(SQLParams._PageSize, OracleDbType.Int32, pagaingParams.PageSize, ParameterDirection.Input)); parameters.Add(new OracleParameter(SQLParams._SortColumn, OracleDbType.Varchar2, "actiontime", ParameterDirection.Input)); parameters.Add(new OracleParameter(SQLParams._SortDirection, OracleDbType.Varchar2, "asc", ParameterDirection.Input)); parameters.Add(new OracleParameter(SQLParams._IOCursor, OracleDbType.RefCursor, ParameterDirection.Output)); parameters.Add(new OracleParameter(SQLParams._RowCount, OracleDbType.Int32, ParameterDirection.Output));

        try
        {

            OracleCommand command = new OracleCommand();
            command.Connection= new OracleConnection(_context.ConnectionString);
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText =OraclePackagesHelpers.ActionPermissionPKG._PackageName + OraclePackagesHelpers.ActionPermissionPKG._SupportLogSearch;
            command.Parameters.AddRange(parameters.ToArray());
            using (OracleDataAdapter da = new OracleDataAdapter(command))
            {

                DataTable dt = new DataTable();

                // Fill the DataSet using default values for DataTable names, etc
                da.Fill(dt);

                // Detach the OracleParameters from the command object, so they can be used again
                command.Parameters.Clear();


                // Return the dataset
                return dt;
            }

        }
        catch (ArgumentOutOfRangeException ex)
        {
            throw;
        }

dapper example:

var parameters = new DynamicParameters(); parameters.Add("ItemID_in", filters.ItemId, dbType: DbType.Int32, ParameterDirection.Input); parameters.Add("PlayerID_in", null, DbType.String, ParameterDirection.Input); parameters.Add("PlayerNickName_in", null, DbType.String, ParameterDirection.Input); parameters.Add("PlayerLoginName_in", null, DbType.String, ParameterDirection.Input); parameters.Add("StartDate_in", filters.StartDate, dbType: DbType.Date, ParameterDirection.Input); parameters.Add("EndDate_in", filters.EndDate, dbType: DbType.Date, ParameterDirection.Input); parameters.Add("BoUser_in", null, DbType.String, ParameterDirection.Input); parameters.Add("BOUserID_in", null, DbType.String, ParameterDirection.Input); parameters.Add("ActionDescription_in", filters.ActionDescription, DbType.String, ParameterDirection.Input); parameters.Add("ActionType_in", filters.ActionType.Length > 0 ? string.Join(",", filters.ActionType) : null, DbType.String, ParameterDirection.Input); parameters.Add("ReferenceID_in", filters.ReferenceID, DbType.String, ParameterDirection.Input); parameters.Add("IsError_in", null, DbType.String, ParameterDirection.Input); parameters.Add(SQLParams._PageIndex, pagaingParams.PageIndex, dbType: DbType.Int32, ParameterDirection.Input); parameters.Add(SQLParams._PageSize, pagaingParams.PageSize, dbType: DbType.Int32, ParameterDirection.Input); parameters.Add(SQLParams._SortColumn, new DbString() { Value = "actiontime", IsAnsi = true }); parameters.Add(SQLParams._SortDirection, new DbString() { Value = "asc", IsAnsi = true }); parameters.Add(SQLParams._IOCursor, new OracleRefCursorParam(SQLParams._IOCursor)); parameters.Add(SQLParams._RowCount, null, DbType.Int32, ParameterDirection.Output);

        try
        {

            var reader = _context.ExecuteReader(
               OraclePackagesHelpers.ActionPermissionPKG._PackageName + OraclePackagesHelpers.ActionPermissionPKG._SupportLogSearch, parameters, commandType: CommandType.StoredProcedure);

            var dataTable = new DataTable();
            dataTable.Load(reader);

            reader.Close();

            return dataTable;
        }
        catch (ArgumentOutOfRangeException ex)
        {
            throw;
        }

i see that dapper take almost 18 seconds to return the data while OracleDataAdapter return the data in 3 seconds