DapperLib / DapperLib/Dapper

performance issue

Open
#1,772 0 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Dominant language
C#
Stars
18.4k
Forks
3.7k
Avg merge
5h 8m
Merged PRs (30d)
1

Description

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 parameters = new List();
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

Contributor guide

No contributing guide indexed for this repository

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start by comparing the OracleDataAdapter example with the Dapper path through _context.ExecuteReader and DataTable.Load. Measure where the 18-second delay occurs, including parameter setup and reader loading, and provide a reproducible comparison or identified cause with enough detail to verify that the timings are consistent.

Written by the indexing model from the issue text.

Assessment

Tech stack
csharp
Domain
database
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Needs clarification
Newbie friendliness
25/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.