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?
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.
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.