Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > LLBLGen Pro Runtime Framework> Slow Any()/Exists vs quick Count()
 

Pages: 1
LLBLGen Pro Runtime Framework
Slow Any()/Exists vs quick Count()
Page:1/1 

  Print all messages in this thread  
Poster Message
TomDog
User



Location:
Wellington, New Zealand
Joined on:
25-Oct-2005 22:21:17
Posted:
570 posts
# Posted on: 31-Aug-2018 04:27:20.  
I can't repro this in anything but our own DB but this in LINQPad:
Code:
var occ = from oc in OccurrenceView.Where(ov => ov.StaffMemberID == 10082)
             where oc.OccurrenceNo == "O1001-09"
             select oc;
occ.Count().Dump();
returns straight away while replacing the Count with Any times out:
Code:
occ.Any().Dump();
Simplifying the generated SQL to this:
Code:
SELECT TOP(1 /* @p4 */) CASE
                 WHEN EXISTS
                     (SELECT [LPLA_1].[Occurrence_No]
                        FROM (SELECT [LPLA_1].[Occurrence_No]
                                FROM [AQD].[oc_Occ_ViewSecured] [LPLA_1]
                                WHERE ((([LPLA_1].[Staff_Member_ID] = 10082 /* @p1 */))
                                    AND ([LPLA_1].[Occurrence_No] = 'O1001-09' /* @p2 */))) [LPA_L2]) THEN 1
                 ELSE 0
                END AS [LPFA_1]
FROM [AQD].[oc_Occ_ViewSecured] [LPLA_1]
and it still times out in SSMS but if I change it to:
Code:
SELECT TOP(1 /* @p4 */) CASE
                 WHEN EXISTS
                     (SELECT COUNT(0)
                        FROM (SELECT [LPLA_1].[Occurrence_No]
                                FROM [AQD].[oc_Occ_ViewSecured] [LPLA_1]
                                WHERE ((([LPLA_1].[Staff_Member_ID] = 10082 /* @p1 */))
                                    AND ([LPLA_1].[Occurrence_No] = 'O1001-09' /* @p2 */))) [LPA_L2]) THEN 1
                 ELSE 0
                END AS [LPFA_1]
FROM [AQD].[oc_Occ_ViewSecured] [LPLA_1]
or
Code:
SELECT TOP(1 /* @p4 */) CASE
                 WHEN EXISTS
                     (SELECT [Occurrence_No]
                        FROM (SELECT [LPLA_1].[Occurrence_No]
                                FROM [AQD].[oc_Occ_ViewSecured] [LPLA_1]
                                WHERE ((([LPLA_1].[Staff_Member_ID] = 10082 /* @p1 */))
                                    AND ([LPLA_1].[Occurrence_No] = 'O1001-09' /* @p2 */))) [LPA_L2]) THEN 1
                 ELSE 0
                END AS [LPFA_1]
it returns straight away.

Also not actually a LINQ problem QuerySpec also times out
Code:
var qa = qf.Create().Select(qf.OccurrenceView.Where(OccurrenceViewFields.StaffMemberID == 10082)
.AndWhere(OccurrenceViewFields.OccurrenceNo == "O1001-09").Any());
var exists = dap.FetchScalar<bool>(qa);


This is in version 5.4.3 but same problem in any version.
We've had to work around this for years and I'm only now getting a chance to report it.
Jeremy Thomas
VS 2017 C#, LLBLGen v5.4, Winforms, WPF and ASP.NET MVCLLBL & LinqPad
 
Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14569 posts
# Posted on: 31-Aug-2018 11:02:01.  
I tried the last example on Northwind, as below using v.5.4.0, it returns instantaneously.
Code:

using (var adapter = new DataAccessAdapter())
{
    var qf = new QueryFactory();
    var qa = qf.Create().Select(qf.Customer.Where(CustomerFields.Country == "USA")
    .AndWhere(CustomerFields.Region == "CA").Any());
    var exists = adapter.FetchScalar<bool>(qa);
}


  Top
TomDog
User



Location:
Wellington, New Zealand
Joined on:
25-Oct-2005 22:21:17
Posted:
570 posts
# Posted on: 31-Aug-2018 11:11:04.  
Like I said I can't reproduce it on another DB, nor do I expect anyone else could easily.
Jeremy Thomas
VS 2017 C#, LLBLGen v5.4, Winforms, WPF and ASP.NET MVCLLBL & LinqPad
 
Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37803 posts
# Posted on: 31-Aug-2018 12:40:13.  
If I recall correctly, your system also runs on Oracle, does it time out on oracle as well? as it times out inside the DB, it's a SQL Server problem, however I don't know what to suggest here to fix it, other than perhaps to add an index on the column in the select.

Additionally, do any SQL server tools report anything here? If you run the sql profiler along with the query, does it report anything?


Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
TomDog
User



Location:
Wellington, New Zealand
Joined on:
25-Oct-2005 22:21:17
Posted:
570 posts
# Posted on: 31-Aug-2018 13:23:06.  
The SQL DB that it timeouts on has 200K rows in the underlying table the only Oracle DB I have access to right now only has 10K and returns straight away. Also that table has 200 columns in it which some say can be a factor with Exist.

That view is always meant to be uses with a Staff_Member_ID where clause and doing a simple SELECT TOP(@p2) COUNT(*) AS [LPAV_] FROM [AQD].[oc_Occ_ViewSecured] [LPLA_1] also times out.

I will do more digging next week but I do wonder why the second 'FROM [AQD].[oc_Occ_ViewSecured]' is there, it seems to serve no purpose and removing it fixes things for me.
Jeremy Thomas
VS 2017 C#, LLBLGen v5.4, Winforms, WPF and ASP.NET MVCLLBL & LinqPad
 
Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37803 posts
# Posted on: 31-Aug-2018 13:31:20.  
it's there due to how any is handled in the linq provider. Any()/All() are ending up as an expression which has a source which is a full query. The thing is that we simply project the first field in the query in the exists clause to avoid augmenting the source query.

so
SELECT A, B, C FROM T WHERE B=@p

becomes
WHERE EXISTS SELECT A FROM (SELECT A, B, C FROM T WHERE B=@p) X

to make sure the query used as source isn't altered. The above example could be simplified, but a query with an aggregate that's also used in an order by on that etc. can't.

Anyway, the whole query should run properly just fine without problems so it's not the sql we generate. As it's a view you're targeting, re-try with the view copy/pasted in-place in the query as that's the real query ran at runtime.


Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
TomDog
User



Location:
Wellington, New Zealand
Joined on:
25-Oct-2005 22:21:17
Posted:
570 posts
# Posted on: 31-Aug-2018 13:58:02.  
Its the last from cause I'm referring to which gets added for QuerySpec as well.
To complete your example
Code:
SELECT TOP(1 /* @p4 */)

CASE
                 WHEN

WHERE EXISTS SELECT A FROM (SELECT A, B, C FROM T WHERE B=@p) X

THEN 1
ELSE 0

END

FROM T
Why that last from T?
Jeremy Thomas
VS 2017 C#, LLBLGen v5.4, Winforms, WPF and ASP.NET MVCLLBL & LinqPad
 
Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37803 posts
# Posted on: 31-Aug-2018 14:56:38.  
it always generates a query with a FROM clause, as it is often required to do so:

Quote:
In the SELECT statement, the FROM clause is required except when the select list contains only constants, variables, and arithmetic expressions (no column names).


https://docs.microsoft.com/en-us/sql/t-sql/queries/from-transact-sql?view=sql-server-2017

so it then requires analysis and cumbersome logic to determine if it shouldn't generate a FROM clause, so we simply generate the FROM clause as it is often required to do so anyway.

But all this is besides the point: the query runs into something inside SQL Server, which is something we can't fix. The query should run properly, but doesn't.


Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.