# Database query time in span is calculated incorrectly

**URL:** <https://discuss.elastic.co/t/database-query-time-in-span-is-calculated-incorrectly/279260>\
**Category:** APM\
**Tags:** dotnet\
**Created:** [July 21, 2021, 12:34pm UTC](https://discuss.elastic.co/t/database-query-time-in-span-is-calculated-incorrectly/279260 "2021-07-21T12:34:42Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![kbalys](https://avatars.discourse-cdn.com/v4/letter/k/8797f3/32.png) [@kbalys](https://discuss.elastic.co/u/kbalys)\
**Post date:** [July 21, 2021, 12:34pm UTC](https://discuss.elastic.co/t/database-query-time-in-span-is-calculated-incorrectly/279260/1 "2021-07-21T12:34:42Z")

</div>

Hi,

when I am using APM agent with Dapper I think the execution time for sql query is calculated incorrectly.

**APM Server version** : 7.13.3

**APM Agent language and version** : dotnet core 1.7.1

**Description of the problem including expected versus actual behavior. Please include screenshots (if relevant)**: Executing sql query does not contain time when reading from stream.

**Steps to reproduce** :

1. Execute query which contains multiple results sets.
2. Query is executed on dapper which db connection is based on standards .net core libraries
3. SQL execution query time does not include time when reading the stream. It only takes into account parsing and submitting the query (I think).

Sample SQL Query placed in [apm-agent-dotnet/ValuesController.cs at master · elastic/apm-agent-dotnet · GitHub](https://github.com/elastic/apm-agent-dotnet/blob/master/sample/WebApiSample/Controllers/ValuesController.cs)

```auto
                [HttpGet("poc")]
		public async Task<ActionResult<string>> Poc()
		{
			using var dbConnection = new SqlConnection("<connection_string>");
			using var multi = await dbConnection.QueryMultipleAsync(@"
				WAITFOR DELAY '00:00:05'
				SELECT 1

				WAITFOR DELAY '00:00:05'

				SELECT 2

				WAITFOR DELAY '00:00:05'

				SELECT 3");

			var one = await multi.ReadFirstAsync<int>();
			var two = await multi.ReadFirstAsync<int>();
			var three = await multi.ReadFirstAsync<int>();

			return $"POC Hello World! {one}, {two}, {three}";
		}

```

Please see below span.

 ![image](https://us1.discourse-cdn.com/elastic/original/3X/b/4/b432d775b2536ab2e7628edd77c1e56b841d9ecc.jpeg)

Project references:

```auto
    <PackageReference Include="Dapper" Version="2.0.35" />
    <PackageReference Include="Microsoft.Data.SqlClient" Version="3.0.0" />

```

---

<div class="post-metadata">

**Author:** ![forloop](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/forloop/32/9021_2.png) [@forloop](https://discuss.elastic.co/u/forloop)\
**Post date:** [July 27, 2021, 4:54am UTC](https://discuss.elastic.co/t/database-query-time-in-span-is-calculated-incorrectly/279260/2 "2021-07-27T04:54:40Z")

</div>

Hi @kbalys,

The instrumentation for SqlClient uses SQL diagnostic events on .NET Core and .NET 5, and SQL event sources on .NET Framework.

In both cases, a DB span is started when the _before_ SQL Command executed event is raised, and ended when the _after_ SQL Command executed event is raised. So the captured DB span won't include read time. It'd be possible however to capture the reads in a span with something like the following

```auto
namespace WebApiSample.Controllers
{
	[Route("api/[controller]")]
	[ApiController]
	public class ValuesController : ControllerBase
	{
		private ITracer _tracer;

		// inject APM agent tracer via DI
		public ValuesController(ITracer tracer) => _tracer;               

        [HttpGet("poc")]
		public async Task<ActionResult<string>> Poc()
		{
			using var dbConnection = new SqlConnection("<connection_string>");
			using var multi = await dbConnection.QueryMultipleAsync(@"
				WAITFOR DELAY '00:00:05'
				SELECT 1

				WAITFOR DELAY '00:00:05'

				SELECT 2

				WAITFOR DELAY '00:00:05'

				SELECT 3");

			return await _tracer.CurrentTransaction.CaptureSpan("DB read", ApiConstants.TypeDb, async () => {
			    var one = await multi.ReadFirstAsync<int>();
			    var two = await multi.ReadFirstAsync<int>();
			    var three = await multi.ReadFirstAsync<int>();

			    return $"POC Hello World! {one}, {two}, {three}";
			}, "read");                       

		}
	}
}

```

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [August 17, 2021, 12:54am UTC](https://discuss.elastic.co/t/database-query-time-in-span-is-calculated-incorrectly/279260/3 "2021-08-17T00:54:45Z")

</div>

This topic was automatically closed 20 days after the last reply. New replies are no longer allowed.