Imagine you have those 3 tables:
想象一下,你有这3个表:
And imagine there is massive data according to this schema.
并且假设根据此模式存在大量数据。
When I run a query like this:
当我运行这样的查询时:
SELECT DISTINCT tPerson.Name, tPerson.Town
FROM tPerson
JOIN tPersonTypeCodeMap ON tPersonTypeCodeMap.PersonId = tPerson.Id
JOIN tPersonHobbyCodeMap ON tPersonHobbyCodeMap.PersonId = tPerson.Id
WHERE tPersonTypeCodeMap.TypeCode IN ('C', 'S', 'P')
It works quite fast!
它工作得很快!
But when I add the second condition (NOT IN) the query takes ages:
但是当我添加第二个条件(NOT IN)时,查询需要很长时间:
SELECT DISTINCT tPerson.Name, tPerson.Town
FROM tPerson
JOIN tPersonTypeCodeMap ON tPersonTypeCodeMap.PersonId = tPerson.Id
JOIN tPersonHobbyCodeMap ON tPersonHobbyCodeMap.PersonId = tPerson.Id
WHERE tPersonTypeCodeMap.TypeCode IN ('C', 'S', 'P')
OR tPersonHobbCodeMap.HobbyCode NOT IN ('SKATE','CLIMBING')
Can you tell me what is the reason that slows down the query and how can I make it work faster?
你能告诉我减慢查询的原因是什么,以及如何让它更快地运行?
2 个解决方案
#1
2
In the first query, most of the filtering can be done by looking only at a single table (tpersonTypeCodeMap). In the second example, two tables need to be JOINed to perform the filtering. Also, once you introduce "OR", you lose the effect of any index.
在第一个查询中,大多数过滤可以通过仅查看单个表(tpersonTypeCodeMap)来完成。在第二个示例中,需要联接两个表来执行过滤。此外,一旦引入“OR”,您将失去任何索引的效果。
Is it really true that you want "OR" operating on those two filters, and not "AND"? Also, is it true that you want multiple records per person returned, depending on how many TypeCodes they match and how many HobbyCodes they fail to match?
您是否希望“OR”在这两个过滤器上运行,而不是“AND”?此外,您是否希望每个人返回多条记录,具体取决于它们匹配的TypeCodes数量以及它们无法匹配的HobbyCodes数量?
If the condition "OR" is, in fact, what you want, you can use:
如果条件“OR”实际上是你想要的,你可以使用:
SELECT tPerson.Name, tPerson.Town
FROM tPerson JOIN tPersonTypeCodeMap
ON tPersonTypeCodeMap.PersonId = tPerson.Id
WHERE tPersonTypeCodeMap.TypeCode IN ('C', 'S', 'P')
UNION
SELECT tPerson.Name, tPerson.Town JOIN tPersonHobbyCodeMap
ON tPersonHobbyCodeMap.PersonId = tPerson.Id
WHERE tPersonHobbCodeMap.HobbyCode NOT IN ('SKATE','CLIMBING')
This will obtain the two sets of records independently then UNION them together. By using UNION instead of UNION ALL, a DISTINCT operation will be returned to reduce the database to unique rows.
这将独立获得两组记录,然后将它们联合起来。通过使用UNION而不是UNION ALL,将返回DISTINCT操作以将数据库减少为唯一行。
#2
0
The NOT IN portion is not going to perform well. Try this version.
NOT IN部分不会表现良好。试试这个版本。
SELECT tPerson.Name, tPerson.Town
FROM tPerson
JOIN tPersonTypeCodeMap ON tPersonTypeCodeMap.PersonId = tPerson.Id
WHERE tPersonTypeCodeMap.TypeCode IN ('C', 'S', 'P')
OR NOT EXISTS(SELECT NULL
FROM tPersonHobbCodeMap
WHERE tPersonHobbCodeMap.PersonId = tPerson.id
AND tPersonHobbCodeMap.HobbyCode IN ('SKATE','CLIMBING') )
#1
2
In the first query, most of the filtering can be done by looking only at a single table (tpersonTypeCodeMap). In the second example, two tables need to be JOINed to perform the filtering. Also, once you introduce "OR", you lose the effect of any index.
在第一个查询中,大多数过滤可以通过仅查看单个表(tpersonTypeCodeMap)来完成。在第二个示例中,需要联接两个表来执行过滤。此外,一旦引入“OR”,您将失去任何索引的效果。
Is it really true that you want "OR" operating on those two filters, and not "AND"? Also, is it true that you want multiple records per person returned, depending on how many TypeCodes they match and how many HobbyCodes they fail to match?
您是否希望“OR”在这两个过滤器上运行,而不是“AND”?此外,您是否希望每个人返回多条记录,具体取决于它们匹配的TypeCodes数量以及它们无法匹配的HobbyCodes数量?
If the condition "OR" is, in fact, what you want, you can use:
如果条件“OR”实际上是你想要的,你可以使用:
SELECT tPerson.Name, tPerson.Town
FROM tPerson JOIN tPersonTypeCodeMap
ON tPersonTypeCodeMap.PersonId = tPerson.Id
WHERE tPersonTypeCodeMap.TypeCode IN ('C', 'S', 'P')
UNION
SELECT tPerson.Name, tPerson.Town JOIN tPersonHobbyCodeMap
ON tPersonHobbyCodeMap.PersonId = tPerson.Id
WHERE tPersonHobbCodeMap.HobbyCode NOT IN ('SKATE','CLIMBING')
This will obtain the two sets of records independently then UNION them together. By using UNION instead of UNION ALL, a DISTINCT operation will be returned to reduce the database to unique rows.
这将独立获得两组记录,然后将它们联合起来。通过使用UNION而不是UNION ALL,将返回DISTINCT操作以将数据库减少为唯一行。
#2
0
The NOT IN portion is not going to perform well. Try this version.
NOT IN部分不会表现良好。试试这个版本。
SELECT tPerson.Name, tPerson.Town
FROM tPerson
JOIN tPersonTypeCodeMap ON tPersonTypeCodeMap.PersonId = tPerson.Id
WHERE tPersonTypeCodeMap.TypeCode IN ('C', 'S', 'P')
OR NOT EXISTS(SELECT NULL
FROM tPersonHobbCodeMap
WHERE tPersonHobbCodeMap.PersonId = tPerson.id
AND tPersonHobbCodeMap.HobbyCode IN ('SKATE','CLIMBING') )