Same query appears twice in Purepaths, with lots of time in between

[AntonioSousa]

I have seen several cases in the past where a same query seems to be executed twice in a row, with lots of time in between. But in one recent case, I have figured out with developers that they are really the same query.

And they have one interesting feature: they return an enormous number of rows! In the example below, you can see the same query separated by more than two minutes. The second says a whopping more than a million rows were returned!

Unfortunately, if you try to find them by total time execution, you won't be able to get them, as they take less than a milissecond... But it does make sense that to transmit those million lines would take some time, and two minutes might represent an enormous quantity of data...

Why does this happen? Is it because the queries are invoked in some special way?

[Julius_Loman]

Last time I ran into this I came to the conclusion the timings in the PurePath are misleading. Response time for the query is measured for the query execution itself. This was fast. "Downloading" of the result set to application was not. You can see that in method hotspots (likely - but since your example is from .NET - the stacktraces are not captured that often as for Java). Dynatrace does not measure processing / downloading the result set from DB query, only the query itself.

[AntonioSousa]

@Julius_Loman,

Sometimes this happens, in most cases it doesn't. Not sure why, but there should be some explanation for why it happens sometimes. I also saw one recent example of big query time, but also in that case with two rows in the Purepath, and also some time in between. Will try to find it and post it here.

[AntonioSousa]

@Julius_Loman,

Figured out a similar example, but in which the first line really shows more time. In this case, a more modest >400K lines returned 🤣

[Julius_Loman]

@AntonioSousa  this screenshot does not reveal much as if it's really the same query, it could be already prefetched in the database.

Always check the code level tab in the Trace at it reveals also the points in the code where Dynatrace is instrumented. 

Timing is measured for the query execution itself only. Not for the reading of the result set in database.