Last post Feb 02, 2012 11:34 AM by Dr. Acula
Feb 02, 2012 07:03 AM|Dr. Acula|LINK
I have a large group of sql scripts that are run using the Oracle.DataAccess.Client.OracleCommand. What I want to do is record the time it took for the sql to execute on the Oracle DB. I'm thinking I could grab the time before and after the ExecuteReader()
and compare them but I was wondering if there is a better way to do it, is the data I'm looking for returned to the command object and if so how do I get it?
Feb 02, 2012 07:32 AM|kedarrkulkarni|LINK
getting time after and before executereader in c# code is fine... but it probably would not give you correct execution time....
what about latency/delay between web server to Oracle database/service?
instead.... you can set the stored procedure which will return you required result.... (instead of direct query)... from Oracle procedure you can check time difference before and after query execution...
or set timing in oracle.... and read elapsed time and pass it as output parameter from oracle procedure...
hope this helps...
Feb 02, 2012 08:57 AM|Dr. Acula|LINK
Cheers KK, the problem is I don't have access to the DB to create my sql as procedures. I've just inherited a lot of code and am specing an upgrade so will be pushing for access to store the sql as procedures in the futures, but for the moment I'm stuck
with a directory of randomly named txt files...
That's why I was wondering if the information I was after was accessible from the Command object. If not I might have to go with the orriginal idea of comparing times before and after the executereader. Would I be right in thinking that as the sql is all
executed in its own thread the latency is likely to be fairly consistent for each command? So the execution time calculated by this method would still be useful for relative comparison?
Feb 02, 2012 10:39 AM|kedarrkulkarni|LINK
actually there is SqlConnection.RetrieveStatistics Method available for
SqlConnection which does this job...
but of course...... since you are using oracle database.... you cannot simply use it...
however, you might want to take a look at how it works...
I havent tried this for Oracle... but looking at OracleConenction class, it seems simillar method is missing
I suppose you would have to use your approch of time recording in that case.... about the latency factor, we cannot exactly say that the latency would remain uniform for every query fired from application.... since, the latency could be depend on the current
n/w load etc.
moreover, connection pool would play a role in how quicker you get the response in App... as in case of using pooled connections, you might save some time in comparison with the instances where new connection need to be initialised...
even after given all these things.... if the execution time of your queries is significant (more) then all other delay factors caused by application would not affect result much... and you could reasonably use time recording approch
Feb 02, 2012 11:34 AM|Dr. Acula|LINK
Cheers KK, I think my approach is my only option at the moment, hopefully this will only be short term, and I can use the latency inaccuracies to help justify more access enabling me to build a more robust application