Extremely slow performance

Extremely slow performance

Postby LucidCoder » Wed 22 Feb 2017 22:29

Hello;

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();


The above takes over two minutes and often times out.
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)


I can run the following in just a couple seconds:
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();
            }


But I have no worked with this type of data before -- if I can't speed up linq, how can I return the data using the pgReader in a way I can use linq queries against (strongly typed)?

Joe
LucidCoder
 
Posts: 11
Joined: Thu 05 Feb 2015 19:24

Re: Extremely slow performance

Postby LucidCoder » Fri 24 Feb 2017 10:56

I have two trouble tickets open on this issue because I was not able to figure out how to update them.
I opened a second after not hearing anything on the first - can you track a trouble ticket?.

I spent a full day trying various things with no results.

I am currently switched back to npgsql along with Dapper for a solution until I can get this resolved.

Here is a update - one piece of code, two different ways of accessing the data - one returns results in about a second the second takes from 100 seconds to never returning at all:

Code: Select all
           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();
            }


            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();

LucidCoder
 
Posts: 11
Joined: Thu 05 Feb 2015 19:24

Re: Extremely slow performance

Postby Shalex » Fri 24 Feb 2017 15:58

We have merged both your tickets in our email system and asked for the test project with the corresponding DDL/DML script for reproducing.
Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44

Re: Extremely slow performance

Postby LucidCoder » Fri 24 Feb 2017 16:31

The entire code is above, I can send you that in a project...but you will not be able to login or run queries against the customers database unless they whitelist your IP.

Will sending you the entire project actually help?

I provided a pretty clear example - a linq query that takes minutes to run and a ADO query that runs in a second or two along with all the logs.

Please let me know next steps...I will continue this through email then post the final solution here so as to not keep muddying up this chain.

Joe
LucidCoder
 
Posts: 11
Joined: Thu 05 Feb 2015 19:24

Re: Extremely slow performance

Postby Shalex » Mon 27 Feb 2017 20:15

LucidCoder wrote:I provided a pretty clear example - a linq query that takes minutes to run and a ADO query that runs in a second or two along with all the logs.
Your example doesn't include model settings and mapping. Also there is no DDL/DML script for creating database objects for reproducing.

LucidCoder wrote:Will sending you the entire project actually help?
Please localize the issue and send us a small test project with the corresponding DDL/DML script so that we can reproduce and investigate the issue.
Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44

Re: Extremely slow performance

Postby LucidCoder » Tue 28 Feb 2017 04:12

I'm sorry...I am not sure how to do that.

The database referenced is large and read only from my end - I did not create it and only have read access to it.

I can provide you the entire project as a zip file, will that include the DDL/DML script?

Joe
LucidCoder
 
Posts: 11
Joined: Thu 05 Feb 2015 19:24

Re: Extremely slow performance

Postby Shalex » Tue 28 Feb 2017 14:48

1. Please create a new Console Application, run Create Model Wizard via Add > New Item > Devart Entity Model (*.edml) and select only two distribution_stop_information and distribution_line_items tables. Then add your code to perform your test. Upload this project to our FTP server and confirm this by email.

2. We will create the database structure by running Update To Database wizard in *.edml model. After this, the tables have to be populated with data to reproduce the performance issue.
Send us a backup of data in these tables.
If the data in these two tables are confidential, execute
Code: Select all
select count(*) from distribution_stop_information;
select count(*) from distribution_line_items;
and tell us the number of rows in each table.
Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44

Re: Extremely slow performance

Postby tco95ttocs » Fri 21 Jul 2017 07:06

Hi guys,
is this problem fixed with an actual version of dotconnect?
I have a similar problem when selecting data from my database (about 75 tables). In this case i have 4 tables, if i join 3 of them it takes nearly 30-60 sec to get a result, but if i join all 4 tables it takes at least 1 hour!! IF i do the select directly at the database it takes only 2 minutes!
the amount of rows in the table is:
T1: 860000
T2: 73000
T3: 73000
T4: 280000

So i hope you still have a solution :)
tco95ttocs
 
Posts: 5
Joined: Wed 20 Apr 2016 06:56

Re: Extremely slow performance

Postby Shalex » Tue 25 Jul 2017 13:56

@ tco95ttocs

Please provide a test project and a dump of database (if possible) like we asked LucidCoder at http://forums.devart.com/viewtopic.php?t=35010#p121532.

FTP Server: ftp://ftp.devart.com (credentials: anonymous / yourEmail)
Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for PostgreSQL