I'm having a great time with Dapper and having no real issues up to this one, and it's driving me mad.
Given this call to an Oracle method inside a package
begin
package.method(in_table => :in_table,
in_clob_type => :in_clob_type,
out_error_table => :out_error_table);
end;
Same SQL, same parameters, same database and same user. Every other piece of SQL within the application works perfectly
var errorTable = string.Empty;
var parameters = new DynamicParameters();
parameters.Add("in_table", "table-name");
parameters.Add("in_clob_type", 0);
parameters.Add("out_error_table", dbType: DbType.String, size: 32, direction: ParameterDirection.Output);
db.Query("package.nethod", parameters, commandType: CommandType.StoredProcedure);
// Query or Execute makes no difference
// db.Execute"package.nethod", parameters, commandType: CommandType.StoredProcedure);
errorTable = parameters.Get<string>("out_error_table");
Any one have any ideas on the best way to debug this?
UPDATE 1:
Both WebAPI and the console code produce 1708 distinct SQL statements for insertion and update processes within the package function. It just takes a longer between SQL calls but I can't see a pattern as yet.
UPDATE 2:
Digging deeper, not my code so it's taking a little longer, found a call that creates some temp tables into which we load the data required for the process. If I comment this out and just provide an existing table name, 2-3 seconds.
Something in the creation of the tables seems to be blocking the rest of the process? If I mark all the methods PRAGMA AUTONOMOUS_TRANSACTION 10-12 seconds. If I create tables in or out of a specific or shared transaction, 10-12 seconds. If I create them without a transaction 10-12 seconds.
I couldn't work out why the table creation would be causing any sort of lag within the process, in fact I don't see any possible way it could given the very simple nature of the tables, so I went a different direction.
Instead of creating temp tables in the users schema I created the tables as global temporary tables marked as ON COMMIT DELETE ROWS.
Now everything is running in 2 seconds or less as expected :-)
Thanks for all your help, and if you find any ideas for the lag then please feel free to share.