dotconnect for postgresql performance seriously poor on a query

Question

In Visual Studio 2015 3 - C#

version 7.7.837 of the dotNetConnect from Devart for PostgreSQL

EF 6.0

I either need to figure out how to fix the below performance issue or how to use the results from my ADO query - I will open another question on the second part.

I have a critical project that I cannot finish until this is fixed or a work around is in place.

A very simple LINQ query is taking anywhere from 60 seconds to over 5 minutes to complete. I have recreated the same query using ADO and it completes in around a second. I have verified that the same data is being returned both times.

Both of them running in the same session back to back. the 1st takes 1.094 to complete (ADO) the second (LINQ) takes 66.344. The exact same run will sometimes take 100 seconds or up to 5 minutes.

Connection string for the ADO:

string myConnection = "User Id=cops_report;Password=xxxxx;Host=xx.xx.xx.xx;Database=cops_reporting;Persist Security Info=True;Initial Schema=cops_reporting";

Connection string that the connector is using:

<add name="Cops_Reporting_DataContextConnectionString" connectionString="User Id=cops_report;Password=xxxxxxx;Host=xx.xx.xx.xx;Database=cops_reporting;Default Command Timeout = 0;Persist Security Info=True;Initial Schema=cops_reporting" />

Using dbMonitor the ADO code is producing the following:

select distribution_stop_information.unique_id_no, stop_name, stop_address, route_code, customer_reference,distribution_line_items.datetime_created, rma_number 

    from distribution_stop_information join distribution_line_items on distribution_line_items.unique_id_no = distribution_stop_information.unique_id_no 

    where distribution_line_items.datetime_created > '2/22/2017' and customer_no = '91000'

and again from dbMonitor here is the SQL from the Linq:

    SELECT t1.unique_id_no, t1.route_code, t1.customer_reference, t1.stop_name, t1.stop_address, t2.datetime_created, t2.rma_number
FROM cops_reporting.distribution_stop_information t1
INNER JOIN cops_reporting.distribution_line_items t2 ON t1.unique_id_no = t2.unique_id_no
WHERE (t2.datetime_created > :p0) AND (t1.customer_no = :p1)

Here is the full code:

 var dateToCheck = new DateTime(2017, 2, 22);

            //The below string is as close to recreating the problem Linq query as I know how to do

            string mySelectQuery = "select distribution_stop_information.unique_id_no, stop_name, stop_address, route_code, customer_reference,"+
                "distribution_line_items.datetime_created, rma_number from distribution_stop_information join distribution_line_items on " +
                "distribution_line_items.unique_id_no = distribution_stop_information.unique_id_no " +
                "where distribution_line_items.datetime_created > '2/22/2017' and customer_no = '91000'";

            PgSqlConnection pgConnection = new PgSqlConnection(myConnection);
            PgSqlCommand pgCommand = new PgSqlCommand(mySelectQuery, pgConnection);
            pgConnection.Open();
            PgSqlDataReader pgReader = pgCommand.ExecuteReader();

            //steps through it just to verify that the data is in fact coming back
            try
            {
                int counter = 0;
                while (pgReader.Read())
                {
                    counter++;
                    Console.WriteLine(counter.ToString() + " " + pgReader.GetString(0));
                }
            }
            finally
            {
                // always call Close when done reading.
                pgReader.Close();
                // always call Close when done reading.
                pgConnection.Close();
            }

//and here is the Linq equivalent to the above
            var Stops = (from stops in rDb.DistributionStopInformations
                         join line in rDb.DistributionLineItems on stops.UniqueIdNo equals line.UniqueIdNo
                         where line.DatetimeCreated > dateToCheck && stops.CustomerNo == TNGCustNo
                         select new
                         {
                             stops.UniqueIdNo,
                             stops.StopName,
                             stops.StopAddress,
                             stops.RouteCode,
                             stops.CustomerReference,
                             line.DatetimeCreated,
                             line.RmaNumber
                         }
                 ).ToArray();

In response to some questions on the data being passed in I have modifed the code as follows:

  var Stops = (from stops in rDb.DistributionStopInformations
                     join line in rDb.DistributionLineItems on stops.UniqueIdNo equals line.UniqueIdNo
                     where line.DatetimeCreated > Convert.ToDateTime("2/2/2017") && stops.CustomerNo == 91000
                     select new
                     {
                         stops.UniqueIdNo,
                         stops.StopName,
                         stops.StopAddress,
                         stops.RouteCode,
                         stops.CustomerReference,
                         line.DatetimeCreated,
                         line.RmaNumber
                     }
             ).ToArray();
        int stopsCount = Stops.Count();

The resulting SQL is now as follows (same performance, no improvement)

SELECT t1.unique_id_no, t1.route_code, t1.customer_reference, t1.stop_name, t1.stop_address, t2.datetime_created, t2.rma_number
FROM cops_reporting.distribution_stop_information t1
INNER JOIN cops_reporting.distribution_line_items t2 ON t1.unique_id_no = t2.unique_id_no
WHERE (t2.datetime_created > :p0) AND (t1.customer_no = :p1)