Help to Build a Total Run Time Formula or Function 1

[DevilsSlide]

Management wanted me to build a report – which I have. I told them while building it that it would be slow (too slow for mass use anyway), but they still had me proceed.

What I need help with is building a Formula or Function that I’d be able to report the following - Start Time/Date, End Time/Date and Total Run Time. I need to be able to evaluate my report on several different versions (Sample & Client DBs) and report back on the times. My small DB that I’ve built and tested it on takes about 1 min. to run on, but on real life DBs it has ran for over 2 hours without finishing. My tweaks that I test on my sample don’t appear to make a lot of time difference, but if I could get real numbers from larger DBs it would me fine tune the report in more real life situations.

Management gets real upset if I sit there and stare at the screen for several hours (I can’t multi task and log accurate run times without effecting the results by use of the CPU for other tasks). So if I could test run the report on 1 computer and work on other assignments they would be happier.

I feel stupid because I feel it should be easy but I just can’t figure how-to do it. This would also help me fine tune others that I’m not real happy with the overall speed.

I've looked over but not tried thread767-947124 - it is for a batch run, could it be adapted to run on a single report?

Any Suggestions, Thoughts or Input would be a Great HELP!

 

[rosemaryl]

Dear DevilsSlide,

You could do a formula that does a datediff in seconds between the data date and time and the printdate and time.

While not very accurate for a quick running report, it should give you an idea for very long running reports.

Place it in the report footer and not in the page footer.

Notice that I am using a sql expression (ms sql server syntax) to get the data date and time, this is slightly more accurate as it gets the moment the database is read.

SQL Expression:

Insert/Field Object/SQL Expression name it Started

(Getdate())

Save and close.

Create following formula and place it in the report footer.

//Convert Total Seconds to D H M S

WhilePrintingRecords;
local NumberVar TotalSec := datediff("s",{%Started}, PrintDate+PrintTime);
local NumberVar Days := Truncate (TotalSec / 86400);
local NumberVar Hours := Truncate (Remainder ( TotalSec,86400) / 3600);
local NumberVar Minutes := Truncate (Remainder ( TotalSec,3600) / 60);
local NumberVar Seconds := Remainder ( TotalSec , 60);

Totalpagecount &' Pages: ' & ' in ' &
Totext ( Days, '00', 0,'') + ':'+
Totext ( Hours, '00', 0,'') + ':'+
Totext ( Minutes,'00', 0,'') + ':'+
Totext ( Seconds,'00', 0,'')


The start date and time is the SQL Expression %Started, the end date and time is the PrintDate Time. Notice that I concatenated the TotalPageCount to ensure that all pages were generated first, to make the time a more accurate reflection.

Note, that if you make a change on the report, but do not refresh the report and you are in preview mode, it will not "reget" the start-time, you must refresh the report each time you need to calculate.

Hope that helps,

ro


Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[DevilsSlide]

Thanks Rosemary

They've dumped more stuff on me since I posted this so it'll likely be a few days before I chance to put it into use, but I think I get enough of the jist that I can figure it out to fit my needs.

Again THANK YOU for your Post and Input

Have a Good ONE!