http://ayende.comAyende @ RahienCopyright (C) Ayende Rahien 2004 - 2021 (c) 202660Perhaps is the time to add a new "warning" when the user use something like "select id from children where date = (select max(date) from children where id = parentid)" for a many-to-one instead a simple and efficient client-server de-normalization. http://ayende.com/4408/profiler-new-feature-too-many-joins-detection#comment15http://ayende.com/4408/profiler-new-feature-too-many-joins-detection#comment15Sun, 28 Feb 2010 04:42:40 GMTDepends on tables to join. For Table which contains LookUp Types with few items, I don't think it's an issue. Jerome http://ayende.com/4408/profiler-new-feature-too-many-joins-detection#comment14http://ayende.com/4408/profiler-new-feature-too-many-joins-detection#comment14Fri, 26 Feb 2010 08:17:50 GMTPetar, Just by looking at the number of joins, yes. And yes, that query would be caught, properly, I think. And yes, it is configurable. http://ayende.com/4408/profiler-new-feature-too-many-joins-detection#comment13http://ayende.com/4408/profiler-new-feature-too-many-joins-detection#comment13Thu, 25 Feb 2010 11:31:50 GMTHi, Ayende. The question of kk is how you can judge that a query contains to much joins ? Is there any thresholds for the number of joins ? For example a query containing Blogs,Posts, Comment, do this query will trigger the alert ? Is the number of maximum joins configurable ? http://ayende.com/4408/profiler-new-feature-too-many-joins-detection#comment12http://ayende.com/4408/profiler-new-feature-too-many-joins-detection#comment12Thu, 25 Feb 2010 11:29:00 GMTIsn't that pre-mature optimization as there ever is one? You can't decide based on the # of joins if it's more efficient to do it differently. So this warning/error gives a false notion to the user: the user might go and do things differently which are less efficient (as in-memory hash-loops to merge sets is always slower as data has to move to the client and the client can't decide which set to do first based on statistics, the rdbms can) http://ayende.com/4408/profiler-new-feature-too-many-joins-detection#comment11http://ayende.com/4408/profiler-new-feature-too-many-joins-detection#comment11Thu, 25 Feb 2010 10:35:35 GMTkk, Yes, that is basically it. http://ayende.com/4408/profiler-new-feature-too-many-joins-detection#comment10http://ayende.com/4408/profiler-new-feature-too-many-joins-detection#comment10Wed, 24 Feb 2010 17:25:06 GMTCan we get a warning about Cartesian products? http://ayende.com/4408/profiler-new-feature-too-many-joins-detection#comment9http://ayende.com/4408/profiler-new-feature-too-many-joins-detection#comment9Wed, 24 Feb 2010 16:24:39 GMTthere is usually explicit support of cross join in SQL, that is, if you meant it, you better write it down. However I don't actually know if it is supported through an OR/M like NH. http://ayende.com/4408/profiler-new-feature-too-many-joins-detection#comment8http://ayende.com/4408/profiler-new-feature-too-many-joins-detection#comment8Wed, 24 Feb 2010 16:15:15 GMTI'm also not convinced that this is a useful feature. "For OLTP systems, you should consider simplifying your queries or simplifying the data model. While I do not recommend avoiding joins completely, I strong discourage queries with large numbers of joins." OLTP systems typically are normalized to process many transactions as fast as possible. Querying an OLTP system will most likely always mean joining several tables. Simplifying the data model may actually be a BAD idea (in most cases it will be...). "Another issue to pay attention to is possible Cartesian products in queries contains joins, it is very easy to create such a thing and not notice it during development." If you don't notice that during development something is seriously wrong with your testing process. And sometimes you may actually need a crossjoin. http://ayende.com/4408/profiler-new-feature-too-many-joins-detection#comment7http://ayende.com/4408/profiler-new-feature-too-many-joins-detection#comment7Wed, 24 Feb 2010 15:33:20 GMTi mean that when the profiler takes a sql query A how it decides that it must generate a warning of the type 'This statement has too many joins...'? my hypothesis is that it do a simple check on the count of the number of joint into the query so it will do if (joinCount > MaxAllowedJoin) { //generate warning } http://ayende.com/4408/profiler-new-feature-too-many-joins-detection#comment6http://ayende.com/4408/profiler-new-feature-too-many-joins-detection#comment6Wed, 24 Feb 2010 15:32:30 GMTAs you use inner joins, the smallest set filters the rest. This means that if the DB, based on statistics, first joins the pairs with the smallest sets, the temporary set in each hash loop is small. This means that you can't conclude without statistics (and how the optimizer of the DB works) that a given number of joins is 'too much' and will perform poorly, in fact, it might very well be much more efficient than alternatives. http://ayende.com/4408/profiler-new-feature-too-many-joins-detection#comment5http://ayende.com/4408/profiler-new-feature-too-many-joins-detection#comment5Wed, 24 Feb 2010 14:58:09 GMTtobi, This is just an example. http://ayende.com/4408/profiler-new-feature-too-many-joins-detection#comment4http://ayende.com/4408/profiler-new-feature-too-many-joins-detection#comment4Wed, 24 Feb 2010 12:52:59 GMTI think that your general point about joins is correct. However in general many joins at once can very well be more efficient than separate queries if the problem of a cartesian product does not occur. In your example i do not understand why you do inner joins from Blog to Posts and Comments. You would not get Blogs without Posts or without Comments. http://ayende.com/4408/profiler-new-feature-too-many-joins-detection#comment3http://ayende.com/4408/profiler-new-feature-too-many-joins-detection#comment3Wed, 24 Feb 2010 12:29:37 GMTkk, I am not sure that I understand your question http://ayende.com/4408/profiler-new-feature-too-many-joins-detection#comment2http://ayende.com/4408/profiler-new-feature-too-many-joins-detection#comment2Wed, 24 Feb 2010 11:18:54 GMTwhat's the condition to assert that there're too many join? (i mean for example 'join count > 3') http://ayende.com/4408/profiler-new-feature-too-many-joins-detection#comment1http://ayende.com/4408/profiler-new-feature-too-many-joins-detection#comment1Wed, 24 Feb 2010 11:05:17 GMT