ExecuteReader appears to query data twice

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
Post Reply
wdnichols
Posts: 9
Joined: Thu 24 May 2018 12:49

ExecuteReader appears to query data twice

Post by wdnichols » Fri 08 Feb 2019 21:07

We are using Dot Connect for PostGreSQL with PostGreSQL 10.5.

While using ANTS Performance Profiler (v 10.1.3.1233) to track down some performance issues, I noticed that the profiler indicates that some database calls are being performed twice. I cannot see the duplicate database calls directly, but I am confident they are actually occurring twice, and not merely being reported twice, since the elapsed times per duplicate calls, while very close, are not exactly the same.

Below is a sample console app that demonstrates performing a simple select statement two different ways.

Code: Select all

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Devart.Data.PostgreSql;

namespace CGMUSLAB_13142
{
    class Program
    {
        static void Main(string[] args)
        {
            string pw = Console.ReadLine();
            
            string connectionString = $@"User Id=exp_load_191;Password={pw};Host=D-LAB-TESTSVR2.CGMUS.LOCAL;Port=5432;Database=cgmlab;Unicode=True;Connection Timeout=0;Pooling=True;Max Pool Size=100";

            var connection = new PgSqlConnection(connectionString);
            connection.Open();

            string sql = "select * from organization_detail";

            using (var table1 = new DataTable())
            {
                LoadATable(connection, sql, table1);
            }

            using (var table2 = new DataTable())
            {
                LoadBTable(connection, sql, table2);
            }
        }

        private static void LoadATable(PgSqlConnection connection, string sql, DataTable dataTable)
        {
            using (var dataAdapter = new PgSqlDataAdapter(sql, connection))
            {
                dataAdapter.Fill(dataTable);
            }
        }

        private static void LoadBTable(PgSqlConnection connection, string sql, DataTable dataTable)
        {
            using (var command = new PgSqlCommand(sql, connection))
            {
                using (var olReader = command.ExecuteReader())
                {
                    dataTable.Load(olReader);
                }
            }
        }
    }
}
When run through ANTS Performance Profiler, its "Database calls" tab shows that the select statement was run 3 times: Once from LoadATable and twice from LoadBTable. Note that LoadATable uses a PgSqlDataAdapter and its Fill method, while LoadBTable uses PgSqlCommand and its ExecuteReader method.

Can you tell me if my use of the ExecuteReader method has an error that causes PostGreSQL to read the data twice?

Or is this a bug in Dot Connect for PostGreSQL?

Thanks for your assistance.

Bill.

Shalex
Site Admin
Posts: 8592
Joined: Thu 14 Aug 2008 12:44

Re: ExecuteReader appears to query data twice

Post by Shalex » Thu 14 Feb 2019 14:51

We will investigate the question of why LoadBTable calls the query twice and notify you about the result.

daniel.schipper
Posts: 1
Joined: Thu 21 Feb 2019 13:40

Re: ExecuteReader appears to query data twice

Post by daniel.schipper » Thu 21 Feb 2019 13:42

This issue is a significant problem and we need a resolution ASAP. Please assist.

Shalex
Site Admin
Posts: 8592
Joined: Thu 14 Aug 2008 12:44

Re: ExecuteReader appears to query data twice

Post by Shalex » Thu 21 Feb 2019 19:23

We have analyzed our source code. Actually, LoadBTable sends only one request to the database.

Please contact the support team of ANTS Performance Profiler to find out:
1) why the tool identified two calls in this case
2) how it calculates the time of call (the time till the 1st database response or the time of the whole fetch)

Post Reply