Count taking too long in nested query
-
I am trying to retreive an entire object(including related/nested objects -- there are 2 such collections in the object) using the Include Method(Eager-Loading) i am calling this object as "EveryThing" and 2 of its contained collections as A and B. i also need to retreive "EveryThing" based on wether a '=' or LIKE condition is satisfied in A. i.e. i am checking something like "EveryThing".A.FirstName=="abc", then retrieve "EveryThing" and its two collections. The query is like:
string searchQuery1 = @"SELECT VALUE TOP(20) sSet FROM EveryThing as sSet where sSet.SPERID LIKE '%1020%' AND COUNT(SELECT VALUE A.ADR FROM sSet.SADDR AS A WHERE A.ADR LIKE '%BOR%') >= 0";
The query executes fine and returns prefect results, the problem is that it takes around 6-7 minutes to retrieve 20 results. if on the other hand we use EXISTS, the query takes only a fraction of second to execute. I know that exists works, but what is wrong with COUNT? , also is there some other way to acomplish the same thing? Also is there a tool that can help build Entity SQL queries, do the typechecking and other stuff?
-
I am trying to retreive an entire object(including related/nested objects -- there are 2 such collections in the object) using the Include Method(Eager-Loading) i am calling this object as "EveryThing" and 2 of its contained collections as A and B. i also need to retreive "EveryThing" based on wether a '=' or LIKE condition is satisfied in A. i.e. i am checking something like "EveryThing".A.FirstName=="abc", then retrieve "EveryThing" and its two collections. The query is like:
string searchQuery1 = @"SELECT VALUE TOP(20) sSet FROM EveryThing as sSet where sSet.SPERID LIKE '%1020%' AND COUNT(SELECT VALUE A.ADR FROM sSet.SADDR AS A WHERE A.ADR LIKE '%BOR%') >= 0";
The query executes fine and returns prefect results, the problem is that it takes around 6-7 minutes to retrieve 20 results. if on the other hand we use EXISTS, the query takes only a fraction of second to execute. I know that exists works, but what is wrong with COUNT? , also is there some other way to acomplish the same thing? Also is there a tool that can help build Entity SQL queries, do the typechecking and other stuff?
Hi, Your query should be like SELECT VALUE TOP(20) sSet FROM EveryThing as sSet where sSet.SPERID LIKE '%1020%' AND exists(SELECT VALUE COUNT(A.ADR) FROM sSet.SADDR AS A WHERE A.ADR LIKE '%BOR%')"; If you put the coutn keywork outside then for each sset all the "A" records will be fetched . This will impact performace. Thanks, Thani