如何使用嵌套循环加速查询(How to speed up a query with nested loops)

如何使用嵌套循环加速查询(How to speed up a query with nested loops)

我有这个查询，但它很慢

SELECT ID_NODE, -- this case slows down the query!!! CASE WHEN (EXISTS (SELECT MV.ID_CHILD FROM MYVIEW MV INNER JOIN MYTABLE1 MT1 ON MT1.ID_NODE = MV.ID_CHILD WHERE MV.ID_PARENT = CA.ID_NODE AND ID_FATHER IS NOT NULL)) THEN 'Y' ELSE 'N' END AS HAVE_CHILDREN, OTHER_FIELDS FROM MYTABLE2

更新 ：在第一个回答之后，我意识到我的样本并不完美，所以我修改了它做了2次更改（ CA到MT1 ，写入MT1.ID_FATHER不是ID_FATHER ）

SELECT ID_NODE, -- this case slows down the query!!! CASE WHEN (EXISTS (SELECT MV.ID_CHILD FROM MYVIEW MV INNER JOIN MYTABLE1 MT1 ON MT1.ID_NODE = MV.ID_CHILD WHERE MV.ID_PARENT = MT2.ID_NODE AND MT1.ID_FATHER IS NOT NULL)) THEN 'Y' ELSE 'N' END AS HAVE_CHILDREN, OTHER_FIELDS FROM MYTABLE2

更新结束

基本上我想要一个'y'/'n'结果“这个节点有孩子吗？

在执行计划中，我只看到一个警告：

嵌套循环（内部连接））43％

你能否建议对查询进行改进？

作为exterme解决方案，我可以将HAVE_CHILDREN值存储在表中作为新字段，但我不喜欢这样，因为它是“通向错误的公路”。

赏金注意事项：

我在这里发布原始表，视图（用CREATE语句创建）和查询来帮助提供回复：

--This is MYTABLE1 CREATE TABLE [dbo].[MAN_PRG_OPERAZIONI]( [ID_PROG_OPERAZIONE] [int] NOT NULL, [ID_CESPITE] [int] NOT NULL, [ID_TIPO_OPERAZIONE] [int] NOT NULL, [SEQUENZA] [int] NOT NULL, [ID_RESPONSABILE] [int] NULL, [DATA_SCADENZA] [datetime] NULL, [DATA_ULTIMA] [datetime] NULL, [ID_TIPO_FREQUENZA] [int] NOT NULL, [FREQUENZA] [int] NOT NULL, [NOTIFICA_SCADENZA] [nchar](1) NOT NULL, [COSTO_FISSO] [numeric](19, 4) NOT NULL, [NOTE] [nvarchar](max) NULL, [ID_CONTO_FORNITORE] [int] NULL, [ID_ESECUTORE] [int] NULL, [GIORNI_INTERVENTO_PREVISTI] [int] NOT NULL, [RIPETIZIONE] [nchar](1) NOT NULL, [RIPETIZIONE_CONTINUA] [nchar](1) NOT NULL, [RIPETI_FINO_A] [datetime] NULL, [SOSPESO] [nchar](1) NOT NULL, [ORE_LAVORO_PREVISTE] [decimal](8, 2) NOT NULL, [DESCR_TITOLO_OPERAZIONE] [nvarchar](100) NOT NULL, [ID_TEMPLATE] [int] NULL, [TEMPLATE] [nvarchar](25) NULL, [ID_PARENT_TEMPLATE_REMOTE] [int] NULL, [ATTIVO] [nchar](1) NOT NULL, [ID_PARENT_TEMPLATE] [int] NULL, [NOTIFY_RESPONSIBLE] [nchar](1) NULL, [NOTIFY_EXECUTOR] [nchar](1) NULL, [NOTIFY_OTHERS] [nvarchar](200) NULL, [NOTIFY_INADVANCE] [nchar](1) NULL, [NOTIFY_ADVANCE_DAYS] [int] NULL, [NOTIFY_ONEXECUTION] [nchar](1) NULL, [NOTIFY_ONCLOSE] [nchar](1) NULL, [ID_UTENTE_INS] [int] NULL, [DATA_INS] [datetime] NULL, [ID_UTENTE_ULT_MOD] [int] NULL, [DATA_ULTIMA_MOD] [datetime] NULL, [STATO_CKL] [int] NOT NULL, [TAGAPPSYNC] [nchar](1) NOT NULL, [IS_FATHER] [nchar](1) NOT NULL, [ID_FATHER] [int] NULL, [NOTIFY_DELAYS] [nchar](1) NULL, [NOTIFY_DELAYS_DAYS] [int] NULL, [NOTIFY_INS_USER] [nchar](1) NULL, CONSTRAINT [PK_MAN_PRG_OPERAZIONI] PRIMARY KEY CLUSTERED ( [ID_PROG_OPERAZIONE] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY] ) ON [PRIMARY] --This is MYTABLE2 CREATE TABLE [dbo].[CES_ANAGRAFICA]( [ID_CESPITE] [int] NOT NULL, [ID_CESPITE_PADRE] [int] NULL, [COD_CESPITE] [nvarchar](50) NOT NULL, [DESCR_CESPITE] [nvarchar](120) NOT NULL, [IMMATERIALE] [nchar](1) NOT NULL, [DATA_ACQUISTO] [datetime] NULL, [DATA_ENTRATA_FUNZIONE] [datetime] NULL, [DATA_DISMISSIONE] [datetime] NULL, [BENE_USATO] [nchar](1) NOT NULL, [ID_UBICAZIONE] [int] NULL, [NRO_IDENTIFICAZIONE] [nvarchar](50) NULL, [MARCA] [nvarchar](50) NULL, [MODELLO] [nvarchar](50) NULL, [MARCATURA_CE] [nchar](1) NULL, [ANNO_COSTRUZIONE] [int] NULL, [MATRICOLA_COSTRUTTORE] [nvarchar](50) NULL, [COSTRUTTORE] [nvarchar](80) NULL, [ID_CONTO_FORNITORE] [int] NULL, [NOTE] [nvarchar](max) NULL, [ID_TIPO_CESPITE] [int] NOT NULL, [ID_STATO_CESPITE] [int] NULL, [ID_CONTO_PROPRIETA] [int] NULL, [ID_RESPONSABILE] [int] NULL, [DATA_SCAD_GARANZIA] [datetime] NULL, [CAMPO_MISURA] [nvarchar](80) NULL, [CRITERI_ACC] [nvarchar](80) NULL, [RISOLUZIONE] [nvarchar](80) NULL, [ID_USO_STRUMENTO] [int] NULL, [ID_REFERENTE] [int] NULL, [FOTO] [varbinary](max) NULL, [PROF_ID] [int] NOT NULL, [ID_TEMPLATE] [int] NULL, [TEMPLATE] [nvarchar](25) NULL, [ID_PARENT_TEMPLATE_REMOTE] [int] NULL, [ID_PARENT_TEMPLATE] [int] NULL, [ISLOCKED] [nchar](1) NULL, [TAGAPPSYNC] [nchar](1) NOT NULL, [TAGAPPDOCSYNC] [nchar](1) NOT NULL, CONSTRAINT [PK_CES_ANAGRAFICA] PRIMARY KEY CLUSTERED ( [ID_CESPITE] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY] ) ON [PRIMARY] --This is MYVIEW CREATE VIEW [dbo].[V_CESPITE_TREE] AS --BEGIN WITH q AS ( SELECT ID_CESPITE , ID_CESPITE AS ID_CESPITE_ANCESTOR FROM CES_ANAGRAFICA c JOIN CES_TIPI_CESPITE ctc ON ctc.ID_TIPI_INFRSTR = c.ID_TIPO_CESPITE UNION ALL SELECT c.ID_CESPITE, q.ID_CESPITE_ANCESTOR FROM q JOIN CES_ANAGRAFICA c ON c.ID_CESPITE_PADRE = q.ID_CESPITE JOIN CES_TIPI_CESPITE ctc ON ctc.ID_TIPI_INFRSTR = c.ID_TIPO_CESPITE ) select ID_CESPITE AS ID_CHILD, ID_CESPITE_ANCESTOR AS ID_PARENT from q GO -- So my original query was this: SELECT CA.ID_CESPITE,CASE WHEN (EXISTS (SELECT VCA.ID_CHILD FROM V_CESPITE_TREE VCA INNER JOIN MAN_PRG_OPERAZIONI MPO ON MPO.ID_CESPITE = VCA.ID_CHILD WHERE VCA.ID_PARENT = CA.ID_CESPITE AND ID_FATHER IS NOT NULL)) THEN 'Y' ELSE 'N' END AS HAVE_CHILD_PRG, CA.ID_CESPITE_PADRE <Other Fields> FROM CES_ANAGRAFICA CA LEFT OUTER JOIN CES_PERMESSI CP ON ((CA.ID_CESPITE = CP.ID_CESPITE)) INNER JOIN CES_TIPI_CESPITE CTCS ON CA.ID_TIPO_CESPITE = CTCS.ID_TIPI_INFRSTR LEFT OUTER JOIN V_UTENTI_DIPENDENTI VUD ON CA.ID_RESPONSABILE = VUD.ID_DIPENDENTE

I have this query that works but it is slow

SELECT ID_NODE, -- this case slows down the query!!! CASE WHEN (EXISTS (SELECT MV.ID_CHILD FROM MYVIEW MV INNER JOIN MYTABLE1 MT1 ON MT1.ID_NODE = MV.ID_CHILD WHERE MV.ID_PARENT = CA.ID_NODE AND ID_FATHER IS NOT NULL)) THEN 'Y' ELSE 'N' END AS HAVE_CHILDREN, OTHER_FIELDS FROM MYTABLE2

Update: After the first answer I realized my sample was not perfect, so i modified it doign 2 changes (CA to MT1, and writing MT1.ID_FATHER isntead of ID_FATHER)

SELECT ID_NODE, -- this case slows down the query!!! CASE WHEN (EXISTS (SELECT MV.ID_CHILD FROM MYVIEW MV INNER JOIN MYTABLE1 MT1 ON MT1.ID_NODE = MV.ID_CHILD WHERE MV.ID_PARENT = MT2.ID_NODE AND MT1.ID_FATHER IS NOT NULL)) THEN 'Y' ELSE 'N' END AS HAVE_CHILDREN, OTHER_FIELDS FROM MYTABLE2

End of update

Basically I want a 'y'/'n' result about "does this node have a child?

In execution plan i see only one warning that is:

Nested Loop (inner Join)) 43%

Could you please suggest an improvement to the query?

As exterme solution i can store in the table the HAVE_CHILDREN value as a new field, but i don't like this because it is an "highway to bugs".

Note for Bounty:

I post here the original tables, view (made with CREATE statements) and query to help provide a reply:

--This is MYTABLE1 CREATE TABLE [dbo].[MAN_PRG_OPERAZIONI]( [ID_PROG_OPERAZIONE] [int] NOT NULL, [ID_CESPITE] [int] NOT NULL, [ID_TIPO_OPERAZIONE] [int] NOT NULL, [SEQUENZA] [int] NOT NULL, [ID_RESPONSABILE] [int] NULL, [DATA_SCADENZA] [datetime] NULL, [DATA_ULTIMA] [datetime] NULL, [ID_TIPO_FREQUENZA] [int] NOT NULL, [FREQUENZA] [int] NOT NULL, [NOTIFICA_SCADENZA] [nchar](1) NOT NULL, [COSTO_FISSO] [numeric](19, 4) NOT NULL, [NOTE] [nvarchar](max) NULL, [ID_CONTO_FORNITORE] [int] NULL, [ID_ESECUTORE] [int] NULL, [GIORNI_INTERVENTO_PREVISTI] [int] NOT NULL, [RIPETIZIONE] [nchar](1) NOT NULL, [RIPETIZIONE_CONTINUA] [nchar](1) NOT NULL, [RIPETI_FINO_A] [datetime] NULL, [SOSPESO] [nchar](1) NOT NULL, [ORE_LAVORO_PREVISTE] [decimal](8, 2) NOT NULL, [DESCR_TITOLO_OPERAZIONE] [nvarchar](100) NOT NULL, [ID_TEMPLATE] [int] NULL, [TEMPLATE] [nvarchar](25) NULL, [ID_PARENT_TEMPLATE_REMOTE] [int] NULL, [ATTIVO] [nchar](1) NOT NULL, [ID_PARENT_TEMPLATE] [int] NULL, [NOTIFY_RESPONSIBLE] [nchar](1) NULL, [NOTIFY_EXECUTOR] [nchar](1) NULL, [NOTIFY_OTHERS] [nvarchar](200) NULL, [NOTIFY_INADVANCE] [nchar](1) NULL, [NOTIFY_ADVANCE_DAYS] [int] NULL, [NOTIFY_ONEXECUTION] [nchar](1) NULL, [NOTIFY_ONCLOSE] [nchar](1) NULL, [ID_UTENTE_INS] [int] NULL, [DATA_INS] [datetime] NULL, [ID_UTENTE_ULT_MOD] [int] NULL, [DATA_ULTIMA_MOD] [datetime] NULL, [STATO_CKL] [int] NOT NULL, [TAGAPPSYNC] [nchar](1) NOT NULL, [IS_FATHER] [nchar](1) NOT NULL, [ID_FATHER] [int] NULL, [NOTIFY_DELAYS] [nchar](1) NULL, [NOTIFY_DELAYS_DAYS] [int] NULL, [NOTIFY_INS_USER] [nchar](1) NULL, CONSTRAINT [PK_MAN_PRG_OPERAZIONI] PRIMARY KEY CLUSTERED ( [ID_PROG_OPERAZIONE] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY] ) ON [PRIMARY] --This is MYTABLE2 CREATE TABLE [dbo].[CES_ANAGRAFICA]( [ID_CESPITE] [int] NOT NULL, [ID_CESPITE_PADRE] [int] NULL, [COD_CESPITE] [nvarchar](50) NOT NULL, [DESCR_CESPITE] [nvarchar](120) NOT NULL, [IMMATERIALE] [nchar](1) NOT NULL, [DATA_ACQUISTO] [datetime] NULL, [DATA_ENTRATA_FUNZIONE] [datetime] NULL, [DATA_DISMISSIONE] [datetime] NULL, [BENE_USATO] [nchar](1) NOT NULL, [ID_UBICAZIONE] [int] NULL, [NRO_IDENTIFICAZIONE] [nvarchar](50) NULL, [MARCA] [nvarchar](50) NULL, [MODELLO] [nvarchar](50) NULL, [MARCATURA_CE] [nchar](1) NULL, [ANNO_COSTRUZIONE] [int] NULL, [MATRICOLA_COSTRUTTORE] [nvarchar](50) NULL, [COSTRUTTORE] [nvarchar](80) NULL, [ID_CONTO_FORNITORE] [int] NULL, [NOTE] [nvarchar](max) NULL, [ID_TIPO_CESPITE] [int] NOT NULL, [ID_STATO_CESPITE] [int] NULL, [ID_CONTO_PROPRIETA] [int] NULL, [ID_RESPONSABILE] [int] NULL, [DATA_SCAD_GARANZIA] [datetime] NULL, [CAMPO_MISURA] [nvarchar](80) NULL, [CRITERI_ACC] [nvarchar](80) NULL, [RISOLUZIONE] [nvarchar](80) NULL, [ID_USO_STRUMENTO] [int] NULL, [ID_REFERENTE] [int] NULL, [FOTO] [varbinary](max) NULL, [PROF_ID] [int] NOT NULL, [ID_TEMPLATE] [int] NULL, [TEMPLATE] [nvarchar](25) NULL, [ID_PARENT_TEMPLATE_REMOTE] [int] NULL, [ID_PARENT_TEMPLATE] [int] NULL, [ISLOCKED] [nchar](1) NULL, [TAGAPPSYNC] [nchar](1) NOT NULL, [TAGAPPDOCSYNC] [nchar](1) NOT NULL, CONSTRAINT [PK_CES_ANAGRAFICA] PRIMARY KEY CLUSTERED ( [ID_CESPITE] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY] ) ON [PRIMARY] --This is MYVIEW CREATE VIEW [dbo].[V_CESPITE_TREE] AS --BEGIN WITH q AS ( SELECT ID_CESPITE , ID_CESPITE AS ID_CESPITE_ANCESTOR FROM CES_ANAGRAFICA c JOIN CES_TIPI_CESPITE ctc ON ctc.ID_TIPI_INFRSTR = c.ID_TIPO_CESPITE UNION ALL SELECT c.ID_CESPITE, q.ID_CESPITE_ANCESTOR FROM q JOIN CES_ANAGRAFICA c ON c.ID_CESPITE_PADRE = q.ID_CESPITE JOIN CES_TIPI_CESPITE ctc ON ctc.ID_TIPI_INFRSTR = c.ID_TIPO_CESPITE ) select ID_CESPITE AS ID_CHILD, ID_CESPITE_ANCESTOR AS ID_PARENT from q GO -- So my original query was this: SELECT CA.ID_CESPITE,CASE WHEN (EXISTS (SELECT VCA.ID_CHILD FROM V_CESPITE_TREE VCA INNER JOIN MAN_PRG_OPERAZIONI MPO ON MPO.ID_CESPITE = VCA.ID_CHILD WHERE VCA.ID_PARENT = CA.ID_CESPITE AND ID_FATHER IS NOT NULL)) THEN 'Y' ELSE 'N' END AS HAVE_CHILD_PRG, CA.ID_CESPITE_PADRE <Other Fields> FROM CES_ANAGRAFICA CA LEFT OUTER JOIN CES_PERMESSI CP ON ((CA.ID_CESPITE = CP.ID_CESPITE)) INNER JOIN CES_TIPI_CESPITE CTCS ON CA.ID_TIPO_CESPITE = CTCS.ID_TIPI_INFRSTR LEFT OUTER JOIN V_UTENTI_DIPENDENTI VUD ON CA.ID_RESPONSABILE = VUD.ID_DIPENDENTE

最满意答案

使用CTE; 它会加载你的内连接一次，然后缓存它。

注意我不知道CA.ID_NODE来自哪里，因为你没有解释。 您的内部查询也加入MyTable1，但您没有关联MyTable2和子查询。

假设您提供的内容，伪代码应该是这样的:(如果您使用相关信息更新您的问题，我将更新此答案以反映它）。

更新：

这是基于您的架构更新和一些示例数据的更新版本。 我确认这不再导致重复。 真正的问题是你检查它并确保它提高性能，即降低执行时间。

; with hasChildCte(ID_CESPITE, ID_PARENT) As ( SELECT VCA.ID_CHILD, vca.ID_PARENT FROM V_CESPITE_TREE VCA INNER JOIN MAN_PRG_OPERAZIONI MPO ON MPO.ID_CESPITE = VCA.ID_CHILD WHERE ID_FATHER IS NOT NULL ) Select CA.ID_CESPITE, Case When Exists ( Select ID_PARENT From hasChildCte cte Where cte.ID_PARENT = ca.ID_CESPITE ) Then 'Y' Else 'N' End As HAVE_CHILDREN, CA.ID_CESPITE_PADRE From CES_ANAGRAFICA CA

另请注意，如果您没有在所有已连接的列上都有索引，那么将它们放入其中将是一个很好的举措。这将有助于进一步加快查询速度，尤其是在您处理大量数据时。

更新2

关于CTE执行不止一次的评论让我思考，显然由SQL服务器决定是否缓存CTE，而不是总是缓存。 在许多情况下，CTE只会执行一次，但有时候它与SQL服务器中的视图类似，并且不会被缓存。

因此，我修改了代码以使用table variable 。 但是，我没有足够的测试数据来查看哪种性能更好或更快。

试试这个，看看它是否会产生更快的查询执行时间。 另请注意，无论您选择哪种重构方法和性能改进，最好使用JOIN中使用的列上的索引正确设置数据库。 这显着增加了查询执行时间，因为必须更新索引的插入的成本很低。

更新的非CTE代码，使用表变量代替：

Declare @HasChildren table (ID_CESPITE int, ID_PARENT int) Insert into @HasChildren Select VCA.ID_CHILD, vca.ID_PARENT From V_CESPITE_TREE VCA Inner Join MAN_PRG_OPERAZIONI MPO On MPO.ID_CESPITE = VCA.ID_CHILD Where ID_FATHER Is Not Null Select CA.ID_CESPITE, Case When Exists ( Select ID_PARENT From @HasChildren c Where c.ID_PARENT = ca.ID_CESPITE ) Then 'Y' Else 'N' End As HAVE_CHILDREN, CA.ID_CESPITE_PADRE From CES_ANAGRAFICA CA

Use a CTE ; it will load your inner join once and then cache it.

Note I don't know where the CA.ID_NODE comes from as you didn't explain that. Also your inner query joins to MyTable1, but you're not relating MyTable2 and the subquery.

Given off what you've provided, the pseudo-code should be something like this: (If you update your question with pertinent info I'll update this answer to reflect it).

Update:

Here's an updated version based on your schema update and some sample data. I confirmed this no longer causes duplicates. The real question is for you to check it and make sure it increases the performance, aka lowers execution time.

; with hasChildCte(ID_CESPITE, ID_PARENT) As ( SELECT VCA.ID_CHILD, vca.ID_PARENT FROM V_CESPITE_TREE VCA INNER JOIN MAN_PRG_OPERAZIONI MPO ON MPO.ID_CESPITE = VCA.ID_CHILD WHERE ID_FATHER IS NOT NULL ) Select CA.ID_CESPITE, Case When Exists ( Select ID_PARENT From hasChildCte cte Where cte.ID_PARENT = ca.ID_CESPITE ) Then 'Y' Else 'N' End As HAVE_CHILDREN, CA.ID_CESPITE_PADRE From CES_ANAGRAFICA CA

Note also if you don't have indexes on all of the joined columns, it would be a good move to put those in. This will help to speed up the query further especially if you're working with a large amount of data.

Update 2

The comment about the CTE executing more than once got me thinking, and it is apparently up to SQL server to decide to cache a CTE or not, rather than always caching. In many cases CTE will execute only once, but other times it's similar to a view in SQL server and doesn't get cached.

Consequently, I've modified the code to use a table variable instead. I don't have enough test data to see which performs better or faster, though.

Try this and see if it yields a faster query execution time. Note also regardless of which method of refactoring and performance improvement you choose, it's a good idea to have your database properly setup with indexes on the columns you are using in JOIN's. This increases query execution time significantly, at the slight cost on inserts of the index having to be updated.

The updated, non-CTE, code, using a table variable instead:

Declare @HasChildren table (ID_CESPITE int, ID_PARENT int) Insert into @HasChildren Select VCA.ID_CHILD, vca.ID_PARENT From V_CESPITE_TREE VCA Inner Join MAN_PRG_OPERAZIONI MPO On MPO.ID_CESPITE = VCA.ID_CHILD Where ID_FATHER Is Not Null Select CA.ID_CESPITE, Case When Exists ( Select ID_PARENT From @HasChildren c Where c.ID_PARENT = ca.ID_CESPITE ) Then 'Y' Else 'N' End As HAVE_CHILDREN, CA.ID_CESPITE_PADRE From CES_ANAGRAFICA CA