等效查询，不同结果和执行时间 [英] Equivalent Queries, different results and execution time

问题描述

查询计划链接到:

错误查询

良好查询

因此，我正在使用MSSQL系统，并且正在重写一些查询.我决定主要通过将谓词从WHERE子句中移出并将其填充到我的JOIN子句中来使用谓词.当我这样做时，它返回的行数是原来的两倍，执行时间从几秒变成了几分钟.

So I am working on an MSSQL system and I am rewriting some queries. I decided to play with my predicates mostly by moving them from the WHERE clause, and stuffing them into my JOIN clauses. When I did that, it returned twice as many rows, and the execution time went from seconds to minutes.

我想知道发生了什么，因为我认为查询分析器经常会将谓词填充到JOIN子句中，因为这些行最终会被丢弃.

I'd like to know what happened because I thought that the Query Analyzer more often than not would stuff the predicates into the JOIN clause since those rows would be discarded eventually.

对于这些查询为什么不等效的任何帮助，我们将不胜感激，我不是DB Strong，足以知道在哪里查找.

Any help on why these queries wouldn't be equivalent would be greatly appreciated, I'm just not DB Strong Enough to know where to look..

良好查询:

FROM [CONNECT_PROD].[dbo].[CRM_C005] c005 
LEFT JOIN [CONNECT_PROD].[dbo].[CRM_FI] fi ON c005.ID_FI = fi.ID
LEFT JOIN [CONNECT_PROD].[dbo].[CRM_CPPE] kp ON c005.ID_PE = kp.ID_PE AND  fi.id = kp.id_fi
LEFT JOIN [CONNECT_PROD].[dbo].[CRM_KA] vko ON c005.F7010 = vko.code 
LEFT JOIN [CONNECT_PROD].[dbo].[CRM_VF] vf on vf.id_fi = fi.id and vf.VerkOrg = vko.code
LEFT JOIN [CONNECT_PROD].[dbo].[CRM_KA] land ON fi.land = land.code 
LEFT JOIN [CONNECT_PROD].[dbo].[CRM_KA] title ON kp.Titel = title.code and title.loskz = 0 and title.KatSperre = 0 and title.sprachenr = 0 and title.katnr = 23
WHERE kp.loskz = 0
and vko.loskz = 0
and vko.KatSperre = 0
and vko.sprachenr = 0
and vko.katnr = 274
and vko.ExtKey = '0014'
and land.loskz = 0
and land.KatSperre = 0
and land.sprachenr = 1000
and land.katnr = 2
and fi.loskz = 0
and fi.F7029 = 0
and vf.loskz = 0
and vf.F7023 = 0
and vf.F7152 = 0
and c005.del = 0
and kp.f7017 = 0

错误查询:

FROM [CONNECT_PROD].[dbo].[CRM_C005] c005 
LEFT JOIN [CONNECT_PROD].[dbo].[CRM_FI] fi ON c005.ID_FI = fi.ID AND fi.loskz = 0 AND fi.F7029 = 0
LEFT JOIN [CONNECT_PROD].[dbo].[CRM_CPPE] kp ON c005.ID_PE = kp.ID_PE AND fi.id = kp.id_fi AND kp.loskz = 0 AND kp.f7017 = 0
LEFT JOIN [CONNECT_PROD].[dbo].[CRM_KA] vko ON c005.F7010 = vko.code AND vko.loskz = 0 AND vko.KatSperre = 0 AND vko.sprachenr = 0 AND vko.katnr = 274 and vko.ExtKey = '0014'
LEFT JOIN [CONNECT_PROD].[dbo].[CRM_VF] vf on vf.id_fi = fi.id and vf.VerkOrg = vko.code AND vf.loskz = 0 AND vf.F7023 = 0 AND vf.F7152 = 0
LEFT JOIN [CONNECT_PROD].[dbo].[CRM_KA] land ON fi.land = land.code AND land.loskz = 0 AND land.KatSperre = 0 AND land.sprachenr = 1000 AND land.katnr = 2
LEFT JOIN [CONNECT_PROD].[dbo].[CRM_KA] title ON kp.Titel = title.code and title.loskz = 0 and title.KatSperre = 0 and title.sprachenr = 0 and title.katnr = 23
WHERE c005.del = 0

推荐答案

假设此示例:我有一个100,000个电话的表.有某些电话类型:入站和出站.当我写的查询是

Suppose this example: I have a table of 100,000 phone calls. There are certain types of phone calls: inbound and outbound. When I write a query that is

SELECT * 
FROM calls c
LEFT JOIN calltypes ct
ON c.calltypeID = ct.calltypeID
WHERE c.calltypeID = 1 --Assume this is inbound

此查询将仅返回我的通话表中的入站通话.

This query will return ONLY the inbound calls from my calls table.

现在，如果我写:

SELECT * 
    FROM calls c
    LEFT JOIN calltypes ct
    ON c.calltypeID = ct.calltypeID
    AND c.calltypeID = 1 

我将返回100,000行.左联接找不到合适的联接条件，因此它为ct表的值返回一个空值.这是一个示例，说明为什么在联接条件中使用where子句时查询可能返回更多行的原因.

I will return 100,000 rows. The left join cannot find a suitable join condition, so it returns a null value for the values of the ct table. This is an example of why your query probably returns so many more rows when you use your where clauses in the join conditions instead.

这篇关于等效查询，不同结果和执行时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！