Strangely constructed slow query

damon.cognito · Post by **damon.cognito** » Wed 22 Jul 2009 10:41

The following code

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.

AndreyR · Post by **AndreyR** » Thu 23 Jul 2009 14:01

We are investigating this situation.
I will let you know about the results of our investigation.

philpastor · Post by **philpastor** » Thu 23 Jul 2009 15:34

We are experiencing similar inexplicable slowness. What was the solution to this issue?

AndreyR · Post by **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.

damon.cognito · Post by **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.

AndreyR · Post by **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.

damon.cognito · Post by **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?

AndreyR · Post by **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.

Devart Forums

Strangely constructed slow query

Strangely constructed slow query

Was there a solution to this issue?