This thread looks to be a little on the old side and therefore may no longer be relevant. Please see if there is a newer thread on the subject and ensure you're using the most recent build of any software if your question regards a particular product.
This thread has been locked and is no longer accepting new posts, if you have a question regarding this topic please email us at support@mindscape.co.nz
|
Hello! The SQL in obsolete PHP part of the system: "SELECT c . * , COUNT( a.Id ) as articles FROM clientdb AS c LEFT JOIN printingarticles AS a ON a.ClientId = c.cliid WHERE a.DeletedOn IS NULL GROUP BY c.cliid ORDER BY cliname"; Linq:
(I tried client/article the other way around, join into (which gave unsupported exception (why?))) SQL by Lightspeed:
Highlight: IN (24, 108) in the last WHERE fills me with fear! PS: all on release build. Cheers, Dennis |
|
|
Hi Dennis, The SQL emitted by the group by query is in two parts because this is how our querying API operates. When you are asking for a grouped result in LINQ we need to first issue a grouping query (the first query in the list) which returns the grouping keys. We then need to fetch the actual result sets because you are projecting those sets not just the grouping keys, so the second query is issued where the IN() contains the grouping keys we discovered in the first part. With the exception you saw, if you are able to send through a small repro project I can have a look at what exactly is occuring if that would be of use to you.
|
|
|
Hi Jeremy, It is running very late here, so I am going to take a look at the repro project tomorrow. However about the grouping, now I have only two items in the IN() statement, but I fear that in production it is going to be thousands, of course I can make a view, but still.... What is your thought on this? Thanks, Dennis |
|
|
It could be problematic if you have a very large amount of keys - see http://www.mindscapehq.com/forums/thread/323207 as an example of this occurring in practice. Unfortunately there is nothing we can do about this right now. This is something that is being actively worked on for improvement in 5.0 by allowing our native API grouping use to be used in conjunction with projections etc rather than to just return a list of grouping keys. My suggestion would be to try and simulate a "greater than expected in production" workload and assess the behavior against that data, that will then let you understand if it is going to be a problem. An alternative here if it is possible would be to issue this specific statement via a stored procedure or by calling FindBySql which allows you to escape hatch down to using direct SQL.
|
|
|
Hey Jeremy, Thanks for your explanation. It is no problem for me to work around it, knowing that you guys have it on the radar for future releases. I am looking forward to the 5.0 release! ;-). Cheers, Dennis |
|