CODEWITHSUNDEEP
c#oracle-database
Rickless
Rickless

Reputation: 1455

Send precise time estimation for the user while querying and exporting report

I have the requirement of exporting a report to excel file. The report will contain a huge number of rows (100K or even 280K rows). It's required to send an email to the user with the estimated time to query the database, get the data, and export them into an excel file to know for how long he/she will wait until he/she receives an email with the download link.
What I did is the following:

  1. Execute the query with the requested filters on the database (Using Oracle.DataAccess.Client) but with Count(*) to retrieve the total number of rows to be generated.
  2. Multiply that number with a constant (I get the constant using System.Diagnostics.Stopwatch class to measure the elapsed time in the worst case scenario to fetch x number of rows)

I know that approach is not practical, because in this case I'll have to execute the same query twice for each report.
Is there a better approach to achieve this without having to execute the same query twice?

Upvotes: 1

Views: 50

Answers (1)

OldProgrammer
OldProgrammer

Reputation: 12169

This is probably not 100% accurate. If you are executing the same query each time ( with changed parameters), you can look at the elapsed time for the sql in V$sqlarea view. To find the query, execute something like:

select sql_text, sql_id from v$sqlarea where sql_text like '%foo%;

(replace foo with a unique set of text from the query).

Once you have the sql_id, you can get the average elapsed time (in milliseconds) of the sql:

select  (ELAPSED_TIME/executions)/1000 from v$sqlarea
where sql_id = '5bmcms41p86gf'

replace '5bmcms41p86gf' with the sql_id you obtained from the previous query. Note that records in v$sqlarea may be discarded after some amount of time.

Upvotes: 1

Related Questions