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?
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...
Cheers!
KK
Please mark as Answer if post helps in resolving your issue
My Site
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?
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
hope this helps...
Cheers!
KK
Please mark as Answer if post helps in resolving your issue
My Site
Marked as answer by Dr. Acula on Feb 02, 2012 03:10 PM
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
Dr. Acula
Participant
1441 Points
319 Posts
get the time it took for a command to execute?
Feb 02, 2012 11:03 AM|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?
kedarrkulkar...
All-Star
34295 Points
5514 Posts
Re: get the time it took for a command to execute?
Feb 02, 2012 11:32 AM|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...
KK
Please mark as Answer if post helps in resolving your issue
My Site
Dr. Acula
Participant
1441 Points
319 Posts
Re: get the time it took for a command to execute?
Feb 02, 2012 12:57 PM|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?
kedarrkulkar...
All-Star
34295 Points
5514 Posts
Re: get the time it took for a command to execute?
Feb 02, 2012 02:39 PM|LINK
hummmm...
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...
http://dbtutorials.com/advanced/dac-execution-time-cs.aspx
http://codebetter.com/davidhayden/2005/11/02/sqlconnection-retrievestatistics-in-net-2-0/
I havent tried this for Oracle... but looking at OracleConenction class, it seems simillar method is missing
http://msdn.microsoft.com/en-us/library/system.data.oracleclient.oracleconnection.aspx
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
hope this helps...
KK
Please mark as Answer if post helps in resolving your issue
My Site
Dr. Acula
Participant
1441 Points
319 Posts
Re: get the time it took for a command to execute?
Feb 02, 2012 03:34 PM|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