Slow Any()/Exists vs quick Count()

Posts   
 
    
TomDog
User
Posts: 618
Joined: 25-Oct-2005
# Posted on: 31-Aug-2018 04:27:20   

I can't repro this in anything but our own DB but this in LINQPad:

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:

occ.Any().Dump();

Simplifying the generated SQL to this:

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:

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

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

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
Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# 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.


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

TomDog
User
Posts: 618
Joined: 25-Oct-2005
# 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
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# 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 | Lead developer LLBLGen Pro
TomDog
User
Posts: 618
Joined: 25-Oct-2005
# 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
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# 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 | Lead developer LLBLGen Pro
TomDog
User
Posts: 618
Joined: 25-Oct-2005
# 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

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
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# 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:

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 | Lead developer LLBLGen Pro