SQLServerCentral.com / T-SQL (SS2K8) / SQL Server 2008 / How is SQL query processed in this example? / Latest PostsInstantForum.NET v99.99.99SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 09 Dec 2016 18:42:23 GMT20RE: How is SQL query processed in this example?http://www.sqlservercentral.com/Forums/Topic1432788-392-1.aspx[quote][b]Alan.B (3/19/2013)[/b][hr]If you have some sample data and DDL I can tell you what I think. I don't know if you have a clustered index on either table. If there was a clustered index or unique index you will likely see nested loops for the inner join queries. [/quote]You are right. I should have given the table structures. Here it is:[code]CREATE TABLE [dbo].[Names]( [_id] [int] IDENTITY(1,1) NOT NULL, [Name] [varchar](50) NOT NULL) ON [PRIMARY]CREATE TABLE [dbo].[TableB]( [_id] [int] IDENTITY(1,1) NOT NULL, [ChildCount] [int] NULL) ON [PRIMARY][/code]I ran both these queries:[code]select n.[_id]from Names ninner join TableB b on b.[_id] = n.[_id] and b.[ChildCount] &gt; 50where n.[Name] Like 'A%'select n.[_id]from Names ninner join TableB b on b.[_id] = n.[_id] and b.[ChildCount] &gt; 50 and n.[Name] Like 'A%'[/code]And you were right! In both cases, hash match was used for the merger.Wed, 20 Mar 2013 12:26:22 GMTcnayanRE: How is SQL query processed in this example?http://www.sqlservercentral.com/Forums/Topic1432788-392-1.aspx[quote][b]cnayan (3/19/2013)[/b][hr]Thanks Alan.B for testing it extensively! :-)When I ran your queries, like:[code="sql"]select n.*from Names nFULL JOIN TableB b ON n._id=b._idWHERE LEFT(name,1)='A'AND ChildCount&gt;50[/code]it showed Nested Loop as you suggested.But, I ran my 2 queries, and the Actual EPs also showed Nested Loop for Inner Join.Any idea why you suspected otherwise?[/quote]If you have some sample data and DDL I can tell you what I think. I don't know if you have a clustered index on either table. If there was a clustered index or unique index you will likely see nested loops for the inner join queries. Let's take the following three examples. In the first I am performing three queries against two heaps (no clustered index is present)... Note my comments[code="sql"]IF OBJECT_ID('tempdb..#names') IS NOT NULL DROP TABLE #names;IF OBJECT_ID('tempdb..#TableB') IS NOT NULL DROP TABLE #TableB;CREATE TABLE #names (id int, name char(4));CREATE TABLE #TableB (id int, ChildCount int);INSERT INTO #names SELECT 1,'A100' UNION ALL SELECT 2,'A100' UNION ALL SELECT 3,'A100' UNION ALL SELECT 4,'A200';INSERT INTO #TableB SELECT 1,10 UNION ALL SELECT 2,25 UNION ALL SELECT 3,10 UNION ALL SELECT 4,70;-- all the work is done durning the hash matchselect n.idfrom #Names nINNER JOIN #TableB b on b.id = n.id and b.ChildCount &gt; 50where n.Name Like 'A%'-- all the work is done durning the hash matchselect n.idfrom #Names nINNER JOIN #TableB b on b.[ID] = n.[ID] and b.ChildCount &gt; 50 and LEFT(n.Name,1)='A'--all the work is done during the table scanselect n.[ID]from #Names nRIGHT JOIN #TableB b ON n.id=b.idWHERE LEFT(n.name,1)='A'AND b.ChildCount&gt;50[/code]Now lets add a clustered index...Again, note my comments...[code="sql"]IF OBJECT_ID('tempdb..#names') IS NOT NULL DROP TABLE #names;IF OBJECT_ID('tempdb..#TableB') IS NOT NULL DROP TABLE #TableB;CREATE TABLE #names (id int, name char(4));CREATE TABLE #TableB (id int, ChildCount int);CREATE CLUSTERED INDEX idx_n_id ON #names(id);CREATE CLUSTERED INDEX idx_tb_id ON #TableB(id);INSERT INTO #names SELECT 1,'A100' UNION ALL SELECT 2,'A100' UNION ALL SELECT 3,'A100' UNION ALL SELECT 4,'A200';INSERT INTO #TableB SELECT 1,10 UNION ALL SELECT 2,25 UNION ALL SELECT 3,10 UNION ALL SELECT 4,70;--all the work is done during the clustered index scanselect n.idfrom #Names nINNER JOIN #TableB b on b.id = n.id and b.ChildCount &gt; 50where n.Name Like 'A%'--all the work is done during the clustered index scanselect n.idfrom #Names nINNER JOIN #TableB b on b.[ID] = n.[ID] and b.ChildCount &gt; 50 and LEFT(n.Name,1)='A'--all the work is done during the clustered index scanselect n.[ID]from #Names nRIGHT JOIN #TableB b ON n.id=b.idWHERE LEFT(n.name,1)='A'AND b.ChildCount&gt;50GO[/code]Now, if there is a unique constraint but no clustered index then you will see nested loops for all three queries but the RIGHT join will still perform the best. [code="sql"]IF OBJECT_ID('tempdb..#names') IS NOT NULL DROP TABLE #names;IF OBJECT_ID('tempdb..#TableB') IS NOT NULL DROP TABLE #TableB;CREATE TABLE #names (id int unique, name char(4));CREATE TABLE #TableB (id int unique, ChildCount int);INSERT INTO #names SELECT 1,'A100' UNION ALL SELECT 2,'A100' UNION ALL SELECT 3,'A100' UNION ALL SELECT 4,'A200';INSERT INTO #TableB SELECT 1,10 UNION ALL SELECT 2,25 UNION ALL SELECT 3,10 UNION ALL SELECT 4,70;-- most of the work is done during the index seek and table scans, the rest by an RID lookupselect n.idfrom #Names nINNER JOIN #TableB b on b.id = n.id and b.ChildCount &gt; 50where n.Name Like 'A%'-- most of the work is done during the index seek and table scans, the rest by an RID lookupselect n.idfrom #Names nINNER JOIN #TableB b on b.[ID] = n.[ID] and b.ChildCount &gt; 50 and LEFT(n.Name,1)='A'--all the work is done during the table scanselect n.[ID]from #Names nRIGHT JOIN #TableB b ON n.id=b.idWHERE LEFT(n.name,1)='A'AND b.ChildCount&gt;50[/code]Tue, 19 Mar 2013 16:04:17 GMTAlan.BRE: How is SQL query processed in this example?http://www.sqlservercentral.com/Forums/Topic1432788-392-1.aspxThanks Alan.B for testing it extensively! :-)When I ran your queries, like:[code="sql"]select n.*from Names nFULL JOIN TableB b ON n._id=b._idWHERE LEFT(name,1)='A'AND ChildCount&gt;50[/code]it showed Nested Loop as you suggested.But, I ran my 2 queries, and the Actual EPs also showed Nested Loop for Inner Join.Any idea why you suspected otherwise?[quote]This is because the OUTER joins will produce plans with nested loops whereas the queries above will still generate a hash match[/quote][quote][b]Alan.B (3/19/2013)[/b][hr][quote][b]cnayan (3/19/2013)[/b][hr]My question is the latter one - which is more efficient.[/quote]I tested this a little and here's what I determined: Both queries you posted will produce the exact same query plan and therefore are equally efficient. Taking the sample data provided (and not knowing if either id represented a clustered index), the following queries will most likely produce the exact same query plan (regardless of there are any clustered indexes present):[code="sql"]IF OBJECT_ID('tempdb..#names') IS NOT NULL DROP TABLE #names;IF OBJECT_ID('tempdb..#TableB') IS NOT NULL DROP TABLE #TableB;CREATE TABLE #names (id int, name char(4));CREATE TABLE #TableB (id int, ChildCount int);INSERT INTO #names SELECT 1,'A100' UNION ALL SELECT 2,'A100' UNION ALL SELECT 3,'A100' UNION ALL SELECT 4,'A200';INSERT INTO #TableB SELECT 1,10 UNION ALL SELECT 2,25 UNION ALL SELECT 3,10 UNION ALL SELECT 4,70;select n.[ID]from #Names ninner join #TableB b on b.[ID] = n.[ID] and b.[ChildCount] &gt; 50where n.[Name] Like 'A%'select n.[ID]from #Names ninner join #TableB b on b.[ID] = n.[ID] and b.[ChildCount] &gt; 50 and n.[Name] Like 'A%'select n.[ID]from #Names ninner join #TableB b on b.[ID] = n.[ID] and LEFT(n.[Name],1)='A' and b.[ChildCount] &gt; 50select n.[ID]from #Names ninner join #TableB b on b.[ID] = n.[ID] and n.[Name] Like 'A%' and b.[ChildCount] &gt; 50select n.[ID]from #Names ninner JOIN #TableB b ON n.id=b.idWHERE LEFT(name,1)='A'AND ChildCount&gt;50;WITH filtered_names AS ( select [ID] from #Names WHERE [Name] Like 'A%')SELECT n.idFROM #TableB b inner join filtered_names n ON n.id=b.idWHERE b.ChildCount&gt;50select n.[ID]from #Names nCROSS JOIN #TableB bWHERE b.[ID] = n.[ID]and b.[ChildCount] &gt; 50and n.[Name] Like 'A%'select n.[ID]from #Names nCROSS APPLY #TableB bWHERE b.[ID] = n.[ID]and b.[ChildCount] &gt; 50and n.[Name] Like 'A%'[/code]If you don't have a clustered index on either table then each of the above queries will be equally slow and the queries below will produce the best plan. This is because the OUTER joins will produce plans with nested loops whereas the queries above will still generate a hash match:[code="sql"]select n.[ID]from #Names nFULL JOIN #TableB b ON n.id=b.idWHERE LEFT(name,1)='A'AND ChildCount&gt;50select n.[ID]from #Names nRIGHT JOIN #TableB b ON n.id=b.idWHERE LEFT(name,1)='A'AND ChildCount&gt;50select n.[ID]from #TableB bLEFT JOIN #Names n ON n.id=b.idWHERE LEFT(name,1)='A'AND ChildCount&gt;50[/code]Edit: typo & bonus code[/quote]Tue, 19 Mar 2013 13:00:14 GMTcnayanRE: How is SQL query processed in this example?http://www.sqlservercentral.com/Forums/Topic1432788-392-1.aspx[quote][b]cnayan (3/19/2013)[/b][hr]My question is the latter one - which is more efficient.[/quote]I tested this a little and here's what I determined: Both queries you posted will produce the exact same query plan and therefore are equally efficient. Taking the sample data provided (and not knowing if either id represented a clustered index), the following queries will most likely produce the exact same query plan (regardless of there are any clustered indexes present):[code="sql"]IF OBJECT_ID('tempdb..#names') IS NOT NULL DROP TABLE #names;IF OBJECT_ID('tempdb..#TableB') IS NOT NULL DROP TABLE #TableB;CREATE TABLE #names (id int, name char(4));CREATE TABLE #TableB (id int, ChildCount int);INSERT INTO #names SELECT 1,'A100' UNION ALL SELECT 2,'A100' UNION ALL SELECT 3,'A100' UNION ALL SELECT 4,'A200';INSERT INTO #TableB SELECT 1,10 UNION ALL SELECT 2,25 UNION ALL SELECT 3,10 UNION ALL SELECT 4,70;select n.[ID]from #Names ninner join #TableB b on b.[ID] = n.[ID] and b.[ChildCount] &gt; 50where n.[Name] Like 'A%'select n.[ID]from #Names ninner join #TableB b on b.[ID] = n.[ID] and b.[ChildCount] &gt; 50 and n.[Name] Like 'A%'select n.[ID]from #Names ninner join #TableB b on b.[ID] = n.[ID] and LEFT(n.[Name],1)='A' and b.[ChildCount] &gt; 50select n.[ID]from #Names ninner join #TableB b on b.[ID] = n.[ID] and n.[Name] Like 'A%' and b.[ChildCount] &gt; 50select n.[ID]from #Names ninner JOIN #TableB b ON n.id=b.idWHERE LEFT(name,1)='A'AND ChildCount&gt;50;WITH filtered_names AS ( select [ID] from #Names WHERE [Name] Like 'A%')SELECT n.idFROM #TableB b inner join filtered_names n ON n.id=b.idWHERE b.ChildCount&gt;50select n.[ID]from #Names nCROSS JOIN #TableB bWHERE b.[ID] = n.[ID]and b.[ChildCount] &gt; 50and n.[Name] Like 'A%'select n.[ID]from #Names nCROSS APPLY #TableB bWHERE b.[ID] = n.[ID]and b.[ChildCount] &gt; 50and n.[Name] Like 'A%'[/code]If you don't have a clustered index on either table then each of the above queries will be equally slow and the queries below will produce the best plan. This is because the OUTER joins will produce plans with nested loops whereas the queries above will still generate a hash match:[code="sql"]select n.[ID]from #Names nFULL JOIN #TableB b ON n.id=b.idWHERE LEFT(name,1)='A'AND ChildCount&gt;50select n.[ID]from #Names nRIGHT JOIN #TableB b ON n.id=b.idWHERE LEFT(name,1)='A'AND ChildCount&gt;50select n.[ID]from #TableB bLEFT JOIN #Names n ON n.id=b.idWHERE LEFT(name,1)='A'AND ChildCount&gt;50[/code]Edit: typo & bonus codeTue, 19 Mar 2013 12:26:45 GMTAlan.BRE: How is SQL query processed in this example?http://www.sqlservercentral.com/Forums/Topic1432788-392-1.aspx[quote][b]cnayan (3/19/2013)[/b][hr]My question is the latter one - which is more efficient.[/quote]So do as Lynn suggested IF they both return correct results.[quote] Looks to me like what you need to do is run some tests to determine 1) You get the results you expect, 2) which one is more effecient if both return the same results.[/quote]I suspect they will both produce the same execution plan. That means that neither one of them is going to be any different. However the first one is FAR easier to read and understand.By far the best way to get answers about which of two approaches performs better is to test them on your system!!!Tue, 19 Mar 2013 12:11:29 GMTSean LangeRE: How is SQL query processed in this example?http://www.sqlservercentral.com/Forums/Topic1432788-392-1.aspxMy question is the latter one - which is more efficient.Tue, 19 Mar 2013 12:03:39 GMTcnayanRE: How is SQL query processed in this example?http://www.sqlservercentral.com/Forums/Topic1432788-392-1.aspxNot sure what your problem is there. Looks to me like what you need to do is run some tests to determine 1) You get the results you expect, 2) which one is more effecient if both return the same results.Tue, 19 Mar 2013 11:11:33 GMTLynn PettisHow is SQL query processed in this example?http://www.sqlservercentral.com/Forums/Topic1432788-392-1.aspxHi,I have already posted the problem on Stackoverflow. Can you please help?[url=http://stackoverflow.com/questions/15505165/how-is-sql-query-processed-in-this-example]http://stackoverflow.com/questions/15505165/how-is-sql-query-processed-in-this-example[/url]Thanks much in advance!Regards,NayanTue, 19 Mar 2013 11:04:43 GMTcnayan