Oracle SQL performance booster
Oracle SQL performance booster
I want to share with you one of the best performance boosters i had come across.
In most of the applications i was working on (in the past and present) we did not use datasets, but built our own custom objects. The data access layer usually consisted of calls to stored procedures, functions and code generated SQLs all going through the MS data access application block.
Calling stored procedures and functions was easy enough, we used the generated SQL only when we needed to insert large amount of data (fields) which was easier done through generated SQL.
At some of the business logic code we needed to insert data to one table, then insert different data to a second table and update data at a third table.
This can be done using 3 separated calls to the database wrapped with a transaction (all done in code).
There is a different and much better solution that calls the database only once for all three SQL statements and wraps it with an automatic transaction.
This can be done by wrapping the 3 SQL statements with a Begin and End.
For example:
BEGIN
//statement one
insert into is_me_main
(id, drivertype, girteid, isserverok, errordescription, errorname, errorcode,
servicename, occurtime)
values
(v_id, v_drivertype, v_girteid, v_isserverok, v_errordescription, v_errorname,
v_errorcode, v_servicename, v_occurtime);
//statement two
insert into is_me_services
(id, driver_id, service_name, is_active, priority, girtes)
values
(v_id, v_driver_id, v_service_name, v_is_active, v_priority, v_girtes);
//statement three
update is_me_lanes
set id = v_id,
lineid = v_lineid,
isok = v_isok,
errordescription = v_errordescription,
errorname = v_errorname,
errorcode = v_errorcode,
occurtime = v_occurtime
where id = v_id
and laneid = v_laneid;
END;
Wrap all this with a string variable and make the call to the database:
Database db = DatabaseFactory.CreateDatabase();
using (DbCommand dbCommand = db.GetSqlStringCommand(sql))
{
Logger.WriteToLog(dbCommand);
db.ExecuteNonQuery(dbCommand);
}
Beyond the performance boost that this solution gives you when you only create a single call to the database instead of three, it also acts as a single transaction and if one statement fails an automatic rollback occurs.
If you look even more closely at this example, you may realize that this can be expanded towards building whole SQL generated stored procedures on the fly.