Monitoring stored procedure runtime (loop query four times)

Database
Enthusiast

Monitoring stored procedure runtime (loop query four times)

Dear colleges,

I had got some unexpected behavior from my stored procedure, probably because the lack of experience on my side . But could anyone help me explain.

The stored procedure is basically simple; it loops a query four times. For performance monitoring I captured the system time in a variable ach time the query loops. The same step I did after the query, so I thought end time minus start time is about my runtime. These measures I logged.

The query itself should run for 30 to 45 minutes for each run (processing tens of millions of records with a lot of joins), so I expected to get mentions runtime result in my log table.

Executing the sp resulted in the overall expected runtime around two and a half hours, but in my log table if found delta times of 4 sec to 0 sec. I monitored the query by viewpoint and it run a single process as expected.

The only reason I can think of, is that the stored procedure fired the loop four times and the time I captured is the time it took to evaluate the four queries for execution. Than the query’s where execution in succession, because I saw only a single process.

If above mentioned is true, does anyone have a suggestion how to capture the “real” runtime of the partial runs? Or is there a way to hold the loop until the query has finished and then resume the loop process?

Thanks for reading any reacting!

2 REPLIES
Teradata Employee

Re: Monitoring stored procedure runtime (loop query four times)

Hello,

you can log not only the delta, but the actual start timestamp and end timestamp for each loop.  It will help to debug the issue, probably something with syntax.

Regards,

Vlad.

Enthusiast

Re: Monitoring stored procedure runtime (loop query four times)

Vlad, thanks for the response. It took some time before I could work this case again and I'm made progress but I'm still puzzled.

I found out that Teradata (in my case) waits at the end of the loop until the previous steps are finished. It does not wait within the loop until de query execution is finished and than resume the loop (what I expected).

So the actual time I recorded was the evaluation time for query before execution. My solution was simple, add a other time stamp after the loop completely finished and deduced other moments to get the right values.

I expected the procedure to process synchronous, for real time it done asynchronous processing; it did not wait at the step I expected.