I am getting extremely slow performance using the below query:
Code: Select all
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,
stops.RmaUniqueId,
line.RmaNumber
}
).ToArray();I can run the same query using pgAdminII in just a few seconds.
From dbMonitor:
Code: Select all
SELECT t1.unique_id_no, t1.route_code, t1.customer_reference, t1.stop_name, t1.stop_address, t1.rma_unique_id, 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)Code: Select all
string mySelectQuery = "select distribution_line_items.item_sequence_no, distribution_stop_information.stop_name, distribution_stop_information.stop_address, distribution_stop_information.route_code, distribution_line_items.item_number," +
"distribution_stop_information.unique_id_no,distribution_line_items.rma_original_unique_id, distribution_line_items.rma_number from distribution_line_items join distribution_stop_information on " +
"distribution_line_items.unique_id_no = distribution_stop_information.unique_id_no where distribution_line_items.datetime_created > '2/21/2017' and distribution_stop_information.customer_no = '91000'";
PgSqlConnection pgConnection = new PgSqlConnection(myConnection);
PgSqlCommand pgCommand = new PgSqlCommand(mySelectQuery, pgConnection);
pgConnection.Open();
PgSqlDataReader pgReader = pgCommand.ExecuteReader();
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();
}
Joe
