Strangely constructed slow query

Strangely constructed slow query

Postby damon.cognito » Wed 22 Jul 2009 10:41

The following code

Code: Select all
int oidUser = (int)oid;
         var query = from client in entity.TblClientHead
                join clientName in entity.TblClientName on client equals clientName.TblClientHead
                where clientName.IsPrimary == true
                       && client.FeeEarner.OIDUser == oidUser
                select new
               {
                     client.OIDClient,
                     client.Reference,
                     clientName.TblName.Surname,
                     clientName.TblAddresses.PostCode,
                     client.StampOpen,
                     client.Description
               };


is approximate to the sql

Code: Select all
select * from "TblClientHead" ch, "TblClientName" cn, "TblAddresses" ad, "TblName" na
where ch."OIDClient" = cn."OIDClient" and "OIDFeeEarner" = 16 and "IsPrimary" = true
and ad."OIDAddress" = cn."OIDAddress" and na."OIDName" = cn."OIDName";


which takes 5 seconds. The code version takes 15 seconds and produces this monster...

Code: Select all
2009-07-22 10:54:24 BST LOG:  duration: 15337.592 ms  execute PRSTMT18147748091821867421/PORTAL18147748091821867421: SELECT
   1 AS "C1",
   "Extent1"."OIDClient" AS "OIDClient",
   "Extent1"."Reference" AS "Reference",
   "Extent5"."Forename" AS "Forename",
   "Extent5"."Surname" AS "Surname",
   "Extent6"."Line1" AS "Line1",
   "Extent6"."PostCode" AS "PostCode",
   "Extent1"."StampOpen" AS "StampOpen",
   "Extent1"."Description" AS "Description"
   FROM    public."TblClientHead" AS "Extent1"
   INNER JOIN public."TblClientName" AS "Extent2" ON  EXISTS (SELECT
      true AS "C1"
      FROM    ( SELECT true) AS "SingleRowTable1"
      LEFT OUTER JOIN  (SELECT
         "Extent3"."OIDClient" AS "OIDClient"
         FROM public."TblClientHead" AS "Extent3"
         WHERE "Extent2"."OIDClient" = "Extent3"."OIDClient" ) AS "Project1" ON true = true
      LEFT OUTER JOIN  (SELECT
         "Extent4"."OIDClient" AS "OIDClient"
         FROM public."TblClientHead" AS "Extent4"
         WHERE "Extent2"."OIDClient" = "Extent4"."OIDClient" ) AS "Project2" ON true = true
      WHERE ("Extent1"."OIDClient" = "Project1"."OIDClient") OR (("Extent1"."OIDClient" IS NULL) AND ("Project2"."OIDClient" IS NULL))
   )
   LEFT OUTER JOIN public."TblName" AS "Extent5" ON "Extent2"."OIDName" = "Extent5"."OIDName"
   LEFT OUTER JOIN public."TblAddresses" AS "Extent6" ON "Extent2"."OIDAddress" = "Extent6"."OIDAddress"
   WHERE (true = "Extent2"."IsPrimary") AND ("Extent1"."OIDFeeEarner" = $1)
2009-07-22 10:54:24 BST DETAIL:  parameters: $1 = '16'


240 rows are (correctly) returned, just very slowly.

The above results were on a slow connection. I tried it directly on the server and the sql took 0.5 seconds, where as the code version still took 15 seconds.

Any thoughts please?

cheers, Damon.
damon.cognito
 
Posts: 50
Joined: Wed 22 Jul 2009 09:30

Postby AndreyR » Thu 23 Jul 2009 14:01

We are investigating this situation.
I will let you know about the results of our investigation.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Was there a solution to this issue?

Postby philpastor » Thu 23 Jul 2009 15:34

We are experiencing similar inexplicable slowness. What was the solution to this issue?
philpastor
 
Posts: 2
Joined: Thu 23 Jul 2009 15:31

Postby AndreyR » Mon 27 Jul 2009 10:40

This is a limitation of any ORM-generated queries - they are less optimal than the ones written manually.
There are several ways to speed up the query.
I recommend you to create a view using Defining Query and then simply query this view.
One more way is to create a SQL function and create a function import for it.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby damon.cognito » Mon 27 Jul 2009 11:10

Thanks for the response. There is not optimal, and then there is not optimal. It is nesting two SELECTs so it would appear for a result of 240 rows it is actually running 1 + 240 + 240 separate queries? I think this is a defect.

Adding a Defining Query is an option I agree, but as the problem exists for a fairly straightforward query, I guess I would end up using a Defining Query for a large percentage of the queries - this cannot be right.
damon.cognito
 
Posts: 50
Joined: Wed 22 Jul 2009 09:30

Postby AndreyR » Mon 27 Jul 2009 12:27

We perform only Expresiion Tree translation to SQL.
LINQ-to-Entities query is transformed to the Expression Tree inside Entity Framework, there is no technical possibility to influence this process.
We provide an implementation of LINQ to PostgreSQL (like Microsoft LINQ to SQL), optimizations are available in it, because we have full control in it.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby damon.cognito » Tue 28 Jul 2009 08:56

AndreyR wrote:We perform only Expresiion Tree translation to SQL.
LINQ-to-Entities query is transformed to the Expression Tree inside Entity Framework, there is no technical possibility to influence this process.


Okay, I understand. I think what you are saying is EF isn't good enough for large databases unless using MSSQL. We tried runnning it across a MS SQL Server and it was a lot quicker.

AndreyR wrote:We provide an implementation of LINQ to PostgreSQL (like Microsoft LINQ to SQL), optimizations are available in it, because we have full control in it.


I have tried this and it is certainly a lot quicker than LINQ-to-Entities. Microsoft seem to be getting ready to stop work on their LINQ to SQL, what effect does this have on your LINQ to PostgreSQL and what are it's limitations?
damon.cognito
 
Posts: 50
Joined: Wed 22 Jul 2009 09:30

Postby AndreyR » Tue 28 Jul 2009 11:43

We plan to continue working on LINQ to PostgreSQL. It does not depend on the Microsoft LINQ to SQL implementation.
There are no limitations on it. Please let us know if you find any problems, we appreciate your feedback,
it helps us to improve our products.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16


Return to dotConnect for PostgreSQL