> What is the best way to be able to get an accurate progress from a standard
Chris:
It's a bit like trying to figure out how far you are through life: there's no way of really knowing if you don't know the end point (and all the stuff that goes on in between). The best you can do is guess. One way to do that would to watch the record index on the data provider/pipeline as it traverses the data. If it's a two pass report you would have to account for that as well.
Note that the pipeline does go backwards at times--you'll have to play with it to figure out if it works for you (and it may/will be different for different reports).
I figured there would be a bit of 'guess-work' involved. Is there a way to get the number of records before making the query active? I tried using the .prepare on the query, but apparently the query has to be active before getting the RecordCount.
The real problem I'm trying to solve, is that I have a report that has quite a bit of data... 150,000 records. It's a simple select query on a single table, but the report generation (2 pass), ends up causing the report to take over 7+ hours to fully generate.
I do plan on adding some hard cuttoffs for the amount of data I allow to even attempt to be displayed. (because 7 hrs is unreasonable, anything over 5 minutes is not good, for my application.) I understand that working with so much data, there isn't too much that can be done to speed it up. The majority of the time is spent on the actual display (not waiting for the query to return the data).
So other than limiting the data more, I was hoping to try to give the user some sort of estimate. You know, instead of saying, "This report is extremely large, this may take a while." Say something more like "This report has 100,000 records based on your current filter criteria, and will take approximately 6 hours to generate. Are you sure you want to continue.." And possibly show something so the user knows that the system isn't just frozen, it's really working on generating the report.
First, you don't want to fetch all the records if you don't have to. I've seen a call you could make on an Oracle database that returned the record count without returning the records which is faster. I believe it was posted in a thread a couple months ago, but I can't find the thread. Search you database documentation to see if there is such a function you can call to get only the record count to analyze if you want to fetch all of the records.
To estimate how long the report will take to generate, you could generate a report in memory on a sampling of data, such as TopN records. Create a TppDevice and call PrintToDevices on the report to generate x pages in memory. Then you can measure the time it takes to do that and calculate a percentage that this is for the entire record count to get an estimate. Here is an example of using a TppDevice to generate into memory without actually sending pages to a real output device.
Comments
standard
Chris:
It's a bit like trying to figure out how far you are through life: there's
no way of really knowing if you don't know the end point (and all the stuff
that goes on in between). The best you can do is guess. One way to do that
would to watch the record index on the data provider/pipeline as it
traverses the data. If it's a two pass report you would have to account for
that as well.
Note that the pipeline does go backwards at times--you'll have to play with
it to figure out if it works for you (and it may/will be different for
different reports).
Ed Dressel
Team DM
I figured there would be a bit of 'guess-work' involved. Is there a way to
get the number of records before making the query active? I tried using the
.prepare on the query, but apparently the query has to be active before
getting the RecordCount.
The real problem I'm trying to solve, is that I have a report that has quite
a bit of data... 150,000 records. It's a simple select query on a single
table, but the report generation (2 pass), ends up causing the report to
take over 7+ hours to fully generate.
I do plan on adding some hard cuttoffs for the amount of data I allow to
even attempt to be displayed. (because 7 hrs is unreasonable, anything over
5 minutes is not good, for my application.) I understand that working with
so much data, there isn't too much that can be done to speed it up. The
majority of the time is spent on the actual display (not waiting for the
query to return the data).
So other than limiting the data more, I was hoping to try to give the user
some sort of estimate. You know, instead of saying, "This report is
extremely large, this may take a while." Say something more like "This
report has 100,000 records based on your current filter criteria, and will
take approximately 6 hours to generate. Are you sure you want to
continue.." And possibly show something so the user knows that the system
isn't just frozen, it's really working on generating the report.
Any thoughts?
--Chris
seen a call you could make on an Oracle database that returned the record
count without returning the records which is faster. I believe it was posted
in a thread a couple months ago, but I can't find the thread. Search you
database documentation to see if there is such a function you can call to
get only the record count to analyze if you want to fetch all of the
records.
To estimate how long the report will take to generate, you could generate a
report in memory on a sampling of data, such as TopN records. Create a
TppDevice and call PrintToDevices on the report to generate x pages in
memory. Then you can measure the time it takes to do that and calculate a
percentage that this is for the entire record count to get an estimate. Here
is an example of using a TppDevice to generate into memory without actually
sending pages to a real output device.
http://www.digital-metaphors.com/tips/GenerateInMemory.zip
Cheers,
Jim Bennett
Digital Metaphors
http://www.digital-metaphors.com
info@digital-metaphors.com