SQL找到短，胖，愚蠢的人 [英] SQL to find short, fat and stupid people
问题描述
这是一个有关查询优化的问题。对不起，如果它有点长，
但感谢任何有耐心帮助的人 - 这是我的第一篇帖子
这里...
如果我有两个表：''tblContact''和''tblCategory''类别是
喜欢：
代码名称
010101短暂
010102脂肪
010103愚蠢
联结表''tblConCat''有字段CctConID，CctCatCode告诉我
哪些联系人有哪些类别代码。这些是nchar（6）字段，如果它是
产生任何差异。
如果我需要找到所有短，胖和愚蠢的人我可以看两个
方式：
解决方案一：
SELECT tblContact。* FROM tblContact WHERE
ConID IN（SELECT CctConID FROM tblConCat WHERE CctCatCode =''010101''）
ConID IN（选择CctConID FROM tblConCat WHERE CctCatCode =''010102''）和
ConID IN（选择CctConID FROM tblConCat WHERE CctCatCode =''010103''）
解决方案二：
构建一个帮助表，其中包含我正在寻找的代码
SELECT tblContact。* FROM tblContact
WHERE ConID IN
（SELECT CctConID
FROM tblConCat INNER加入tblHelper
ON tblConCat.CctCatCode = tblHelper.HlpCatCode
GROUP BY CctConID HAVING Count（*）= 3）
我试过两个虽然我查看了查询分析器，但它提供了
比我知道如何处理的更多信息。在实践中，他们
都提供类似的工作时间（我等了大约一秒钟），但我认为
首先看起来效率很低，并认为我的助手表可能会有所帮助。
大约有30,000个联系人记录，180个类别记录和120,000个
联结表记录。
所有我正在寻找任何专业人士的意见这两种方法的缺点 -
看起来那么糟糕吗？数据库是从Access迁移的，其中
助手表确实有帮助，但使用SQL Server我可能不需要它。
再次感谢，如果你有到目前为止！
>我已经尝试了它们，虽然我查看了查询分析器，但是提供的信息比我知道的更多。在实践中，它们都提供类似的工作时间
您可以通过同时运行查询来比较估算的成本
在查询分析器中显示执行计划选项。但是，我会
仍然选择具有最低实际执行时间的查询。所有东西
相等，选择最简单的方法。
BTW，确保你在tblConCat上有一个独特的索引（CctConID和
CctCatCode）。主键，唯一约束或唯一索引将提供
这个。
未经测试的解决方案三（还有更多）：
SELECT tblContact。*
FROM tblContact c
JOIN tblConCat cc1 ON
c.ConID = cc1.CctConID AND
CctCatCode =''010101''
JOIN tblConCat cc2 ON
c.ConID = cc2.CctConID AND
CctCatCode =''010102''
加入tblConCat cc3 ON
c.ConID = cc3.CctConID AND
CctCatCode =''010103''
-
希望这会有所帮助。
Dan Guzman
SQL Server MVP
" Justin Hoffman" < j@b.com>在消息中写道
新闻：d4 ********** @ nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com ...
这是一个有关查询优化的问题。对不起，如果它有点长，但感谢任何有耐心帮助的人 - 这是我的第一篇
帖子......
如果我有两张桌子：''tblContact''和''tblCategory''类别如下：
代码名称
010101短
010102胖子
010103愚蠢
联结表''tblConCat''有字段CctConID，CctCatCode告诉我
哪些联系人有哪些类别代码。这些是nchar（6）字段，如果它有任何区别。
如果我需要找到所有短，胖和愚蠢的人，我可以看到两种方式：
解决方案一：
SELECT tblContact。* FROM tblContact WHERE
ConID IN（选择CctConID FROM tblConCat WHERE CctCatCode =''010101''）
ConID IN（ SELECT CctConID FROM tblConCat WHERE CctCatCode =''010102''）和
ConID IN（选择CctConID FROM tblConCat WHERE CctCatCode =''010103''）
解决方案二：
构建一个帮助表，其中包含我正在寻找的代码
SELECT tblContact。* FROM tblContact
在哪里ConID IN
（SELECT CctConID
FROM tblConCat INNER JOIN tblHelper
ON tblConCat.CctCatCode = tblHelper.HlpCatCode
GROUP BY CctConID HAVING Count（*）= 3）
我已经尝试了两种方法，虽然我查看了查询分析器
提供的信息比我知道的更多。在实践中，他们都提供类似的工作时间（我等待一秒钟），但我认为第一次看起来效率很低，并认为我的助手表可能会有所帮助。大约有30,000个联系人记录，180个类别记录和120,000个联系表记录。
所有我正在寻找关于这两种方法的利弊的评论 - 一个看起来那么糟糕？数据库是从
Access迁移到帮助表确实有帮助的地方，但是使用SQL Server我可能不需要它。
再次感谢，如果你有这么远的话！ br />
" Dan Guzman" <顾****** @ nospam-online.sbcglobal.net>在消息中写道
news：vb **************** @ newssvr11.news.prodigy.com ...< blockquote class =post_quotes>我已经尝试了它们，虽然我查看了查询分析器，它提供的信息比我知道的更多。在实践中，它们都提供类似的工作时间
您可以通过在查询分析器中以相同的时间运行查询与显示执行计划来比较估计的成本选项。但是，我仍然会选择具有最低实际执行时间的查询。所有事情都是平等的，选择最简单的方法。
顺便说一下，确保你在tblConCat（CctConID和CctCatCode）上有一个独特的索引。主键，唯一约束或唯一索引将提供此功能。
未经测试的解决方案三（还有更多）：
SELECT tblContact。*
FROM tblContact c
加入tblConCat cc1 ON
c.ConID = cc1.CctConID和
CctCatCode =''010101''
加入tblConCat cc2 ON
c.ConID = cc2.CctConID AND
CctCatCode =''010102''
加入tblConCat cc3 ON
c.ConID = cc3.CctConID AND
CctCatCode =''010103''
- 希望这会有所帮助。
Dan Guzman
SQL Server MVP
Justin Hoffman < j@b.com>在消息中写道
新闻：d4 ********** @ nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com ...
这是一个有关查询优化的问题。对不起，如果它有点长，但感谢有耐心帮助的人 - 这是我的第一篇帖子......
如果我有两张桌子：''tblContact''和''tblCategory''类别如下：
代码名称
010101短
010102胖子
010103愚蠢
联结表''tblConCat''有字段CctConID，CctCatCode告诉我
哪些联系人有哪些类别代码。这些是nchar（6）字段，如果它有任何区别。
如果我需要找到所有短，胖和愚蠢的人，我可以看到两种方式：
解决方案一：
SELECT tblContact。* FROM tblContact WHERE
ConID IN（选择CctConID FROM tblConCat WHERE CctCatCode =''010101''）
ConID IN（ SELECT CctConID FROM tblConCat WHERE CctCatCode =''010102''）和
ConID IN（选择CctConID FROM tblConCat WHERE CctCatCode =''010103''）
解决方案二：
构建一个帮助表，其中包含我正在寻找的代码
SELECT tblContact。* FROM tblContact
在哪里ConID IN
（SELECT CctConID
FROM tblConCat INNER JOIN tblHelper
ON tblConCat.CctCatCode = tblHelper.HlpCatCode
GROUP BY CctConID HAVING Count（*）= 3）
我已经尝试了两种方法，虽然我查看了查询分析器
提供的信息比我知道的更多。在实践中，他们都提供类似的工作时间（我等待一秒钟），但我认为第一次看起来效率很低，并认为我的助手表可能会有所帮助。大约有30,000个联系人记录，180个类别记录和120,000个联系表记录。
所有我正在寻找关于这两种方法的利弊的评论 - 一个看起来那么糟糕？数据库是从
Access迁移到帮助表确实有帮助的地方，但是使用SQL Server我可能不需要它。
再次感谢，如果你有这么远的话！ / blockquote>
嗨Dan
感谢您的评论。 SQL需要进行一些调整：
CctCatCode字段需要一个表前缀才能使它们明确无误以及
（可能的拼写错误）我需要（选择c。*）而不是比（选择tblContact。*）所以
最终版本是：
SELECT c。*
FROM tblContact c
JOIN tblConCat cc1 ON
c.ConID = cc1.CctConID AND
cc1.CctCatCode =''010101''
JOIN tblConCat cc2 ON
c.ConID = cc2.CctConID AND
cc2.CctCatCode =''010102''
JOIN tblConCat cc3 ON
c.ConID = cc3.CctConID AND
cc3.CctCatCode =''010103''
无论如何，结果再次是非常快，我想我浪费我的时间
建立一个帮助表，如果它没有显着提高速度但是确实增加了复杂性（辅助表）需要在
a多用户环境中动态创建。
我查看了执行情况计划，但它提供了如此多的信息（成本，
子树成本等），它让一个像我这样的简单的人有点迷失。
我想知道的是''哪个更好：A还是B？''即使我跑了他们的b $ b，我也不确定如何比较实际的表现。是否有一把钥匙
号码可供查看？也许''累积客户端处理时间''？也许我应该尝试阅读这个。
（PS我确实有你提到的那个索引）
>我想知道的是''哪个更好：A还是B？''即使我运行他们，我也不确定如何比较实际的性能。是否有一个
键号可供查看？
我通常在受控测试环境中比较实际查询持续时间
，没有其他活动和清理缓存。
CHECKPOINT
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
GO
SELECT GETDATE（）AS StartTime
GO
- 执行第一个查询
GO
SELECT GETDATE（）AS EndTime
GO
CHECKPOINT
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
GO
SELECT GETDATE（）AS StartTime
GO
- 执行第二个查询
GO
SELECT GETDATE（）AS EndTime
GO
-
希望这会有所帮助。
Dan Guzman < br $>
SQL Server MVP
" Justin Hoffman" < j@b.com>在消息中写道
news：d4 ********** @ nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com ...
丹Guzman的" <顾****** @ nospam-online.sbcglobal.net>在消息中写道
新闻：vb **************** @ newssvr11.news.prodigy.com ...我已经尝试了它们，虽然我查看了查询分析器
它提供的信息比我知道的更多。在实践中
它们都提供类似的工作时间
您可以通过在查询分析器中以相同的时间运行查询与显示执行计划来比较估计的成本选项。但是，
我仍然会选择实际执行时间最短的查询。
所有条件相同，选择最简单的方法。
BTW，确保你有一个独特的索引在tblConCat上（CctConID和
CctCatCode）。主键，唯一约束或唯一索引将提供此功能。
未经测试的解决方案三（还有更多）：
SELECT tblContact。*
FROM tblContact c
加入tblConCat cc1 ON
c.ConID = cc1.CctConID和
CctCatCode =''010101''
加入tblConCat cc2 ON
c.ConID = cc2.CctConID AND
CctCatCode =''010102''
加入tblConCat cc3 ON
c.ConID = cc3.CctConID AND
CctCatCode =''010103''
- 希望这会有所帮助。
Dan Guzman
SQL Server MVP
Justin Hoffman < j@b.com>在消息中写道
新闻：d4 ********** @ nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com ...
这是一个有关查询优化的问题。对不起，如果它有点长，但感谢有耐心帮助的人 - 这是我的第一篇帖子......
如果我有两张桌子：''tblContact''和''tblCategory''类别
类似：
代码名称
010101短
010102胖子
010103愚蠢
联结表''tblConCat''有字段CctConID，CctCatCode告诉我哪些联系人有哪些类别代码。这些是nchar（6）字段，
如果它有任何区别。
如果我需要找到所有短，胖和愚蠢的人我可以看到两种方式：
解决方案一：
SELECT tblContact。* FROM tblContact WHERE
ConID IN（选择CctConID FROM tblConCat WHERE CctCatCode =''010101''）
ConID IN（ SELECT CctConID FROM tblConCat WHERE CctCatCode =''010102''）和
ConID IN（选择CctConID FROM tblConCat WHERE CctCatCode =''010103''）
解决方案二：
构建一个帮助表，其中包含我正在寻找的代码
SELECT tblContact。* FROM tblContact
在哪里ConID IN
（SELECT CctConID
FROM tblConCat INNER JOIN tblHelper
ON tblConCat.CctCatCode = tblHelper.HlpCatCode
GROUP BY CctConID HAVING Count（*）= 3）
虽然我查看了查询分析器，但我已经尝试过它们了它提供的信息比我知道的更多。在实践中，他们都提供类似的工作时间（我等了一秒钟），但我认为第一次看起来效率很低，并认为我的助手表可能会有所帮助。大约有30,000个联系人记录，180个类别记录和120,000个联系表记录。
所有我正在寻找关于这两种方法的利弊的评论 - 做一个看起来那么糟糕？数据库是从
Access迁移到帮助表确实有帮助的地方，但是使用SQL Server我可能不需要它。
再次感谢，如果你有这么远的话！ / blockquote>
您好Dan
感谢您的评论。 SQL需要进行一些调整：
CctCatCode字段需要一个表前缀才能使它们明确无误以及
（可能的拼写错误）我需要（选择c。*）而不是（select tblContact。*）所以
最终版本是：
SELECT c。*
FROM tblContact c
加入tblConCat cc1 ON
c.ConID = cc1.CctConID AND
cc1.CctCatCode =''010101''
加入tblConCat cc2 ON
c.ConID = cc2.CctConID AND
cc2.CctCatCode =''010102''
加入tblConCat cc3 ON
c.ConID = cc3.CctConID AND
cc3.CctCatCode =''010103''
无论如何，结果再次非常快，我想我在浪费我的时间
构建一个帮助表，如果它没有显着提高速度，但确实增加了复杂性（辅助表需要在多用户环境中动态创建）。我查看了执行计划，但是它提供了很多信息（成本，
子树成本等），它留下了像我这样的简单的人有点失落。
我想知道的是''哪个更好：A还是B？''即使我跑了
他们，我也不确定如何比较实际表现。是否有一个
键号可供查看？也许''累积客户端处理时间''？
也许我应该尝试阅读这个。
（PS我确实有你提到的那个索引）
This is a question concerning query optimisation. Sorry if it''s a bit long,
but thanks to anyone who has the patience to help - This is my first post
here...
If I have two tables: ''tblContact'' and ''tblCategory'' where categories are
like:
Code Name
010101 Short
010102 Fat
010103 Stupid
The junction table ''tblConCat'' has fields CctConID, CctCatCode to tell me
which contacts have which category codes. These are nchar(6) fields, if it
makes any difference.
If I need to find all people who are short, fat and stupid I can see two
ways:
Solution One:
SELECT tblContact.* FROM tblContact WHERE
ConID IN (SELECT CctConID FROM tblConCat WHERE CctCatCode=''010101'') AND
ConID IN (SELECT CctConID FROM tblConCat WHERE CctCatCode=''010102'') AND
ConID IN (SELECT CctConID FROM tblConCat WHERE CctCatCode=''010103'')
Solution Two:
Build a helper table which contains the codes I''m looking for
SELECT tblContact.* FROM tblContact
WHERE ConID IN
(SELECT CctConID
FROM tblConCat INNER JOIN tblHelper
ON tblConCat.CctCatCode = tblHelper.HlpCatCode
GROUP BY CctConID HAVING Count(*)=3)
I have tried them both out and although I looked at the query analyzer it
provided more information than I knew what to do with. In practise they
both provide similar working times (I wait about a second) but I thought the
first looked rather inefficient and thought my helper table might help.
There are about 30,000 contact records, 180 category records and 120,000
junction table records.
All I am looking for comments on any pros and cons of these two approaches -
does one look that bad? The database was migrated from Access where the
helper table really did help, but using SQL Server I might not need it.
Thanks again, if you got this far!解决方案> I have tried them both out and although I looked at the query analyzer itprovided more information than I knew what to do with. In practise they
both provide similar working times
You can compare the estimated costs by running your queries at the same time
in Query Analyzer with the show execution plan option on. However, I would
still choose the query with the lowest actual execution time. All things
being equal, select the simplest approach.
BTW, make sure you have a unique index on tblConCat (CctConID and
CctCatCode). A primary key, unique constraint or unique index will provide
this.
Untested Solution Three (there are many more):
SELECT tblContact.*
FROM tblContact c
JOIN tblConCat cc1 ON
c.ConID = cc1.CctConID AND
CctCatCode=''010101''
JOIN tblConCat cc2 ON
c.ConID = cc2.CctConID AND
CctCatCode=''010102''
JOIN tblConCat cc3 ON
c.ConID = cc3.CctConID AND
CctCatCode=''010103''
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Justin Hoffman" <j@b.com> wrote in message
news:d4**********@nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...
This is a question concerning query optimisation. Sorry if it''s a bit
long, but thanks to anyone who has the patience to help - This is my first
post here...
If I have two tables: ''tblContact'' and ''tblCategory'' where categories are
like:
Code Name
010101 Short
010102 Fat
010103 Stupid
The junction table ''tblConCat'' has fields CctConID, CctCatCode to tell me
which contacts have which category codes. These are nchar(6) fields, if
it makes any difference.
If I need to find all people who are short, fat and stupid I can see two
ways:
Solution One:
SELECT tblContact.* FROM tblContact WHERE
ConID IN (SELECT CctConID FROM tblConCat WHERE CctCatCode=''010101'') AND
ConID IN (SELECT CctConID FROM tblConCat WHERE CctCatCode=''010102'') AND
ConID IN (SELECT CctConID FROM tblConCat WHERE CctCatCode=''010103'')
Solution Two:
Build a helper table which contains the codes I''m looking for
SELECT tblContact.* FROM tblContact
WHERE ConID IN
(SELECT CctConID
FROM tblConCat INNER JOIN tblHelper
ON tblConCat.CctCatCode = tblHelper.HlpCatCode
GROUP BY CctConID HAVING Count(*)=3)
I have tried them both out and although I looked at the query analyzer it
provided more information than I knew what to do with. In practise they
both provide similar working times (I wait about a second) but I thought
the first looked rather inefficient and thought my helper table might
help. There are about 30,000 contact records, 180 category records and
120,000 junction table records.
All I am looking for comments on any pros and cons of these two
approaches - does one look that bad? The database was migrated from
Access where the helper table really did help, but using SQL Server I
might not need it.
Thanks again, if you got this far!
"Dan Guzman" <gu******@nospam-online.sbcglobal.net> wrote in message
news:vb****************@newssvr11.news.prodigy.com ...I have tried them both out and although I looked at the query analyzer it
provided more information than I knew what to do with. In practise they
both provide similar working times
You can compare the estimated costs by running your queries at the same
time in Query Analyzer with the show execution plan option on. However, I
would still choose the query with the lowest actual execution time. All
things being equal, select the simplest approach.
BTW, make sure you have a unique index on tblConCat (CctConID and
CctCatCode). A primary key, unique constraint or unique index will
provide this.
Untested Solution Three (there are many more):
SELECT tblContact.*
FROM tblContact c
JOIN tblConCat cc1 ON
c.ConID = cc1.CctConID AND
CctCatCode=''010101''
JOIN tblConCat cc2 ON
c.ConID = cc2.CctConID AND
CctCatCode=''010102''
JOIN tblConCat cc3 ON
c.ConID = cc3.CctConID AND
CctCatCode=''010103''
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Justin Hoffman" <j@b.com> wrote in message
news:d4**********@nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...
This is a question concerning query optimisation. Sorry if it''s a bit
long, but thanks to anyone who has the patience to help - This is my
first post here...
If I have two tables: ''tblContact'' and ''tblCategory'' where categories are
like:
Code Name
010101 Short
010102 Fat
010103 Stupid
The junction table ''tblConCat'' has fields CctConID, CctCatCode to tell me
which contacts have which category codes. These are nchar(6) fields, if
it makes any difference.
If I need to find all people who are short, fat and stupid I can see two
ways:
Solution One:
SELECT tblContact.* FROM tblContact WHERE
ConID IN (SELECT CctConID FROM tblConCat WHERE CctCatCode=''010101'') AND
ConID IN (SELECT CctConID FROM tblConCat WHERE CctCatCode=''010102'') AND
ConID IN (SELECT CctConID FROM tblConCat WHERE CctCatCode=''010103'')
Solution Two:
Build a helper table which contains the codes I''m looking for
SELECT tblContact.* FROM tblContact
WHERE ConID IN
(SELECT CctConID
FROM tblConCat INNER JOIN tblHelper
ON tblConCat.CctCatCode = tblHelper.HlpCatCode
GROUP BY CctConID HAVING Count(*)=3)
I have tried them both out and although I looked at the query analyzer it
provided more information than I knew what to do with. In practise they
both provide similar working times (I wait about a second) but I thought
the first looked rather inefficient and thought my helper table might
help. There are about 30,000 contact records, 180 category records and
120,000 junction table records.
All I am looking for comments on any pros and cons of these two
approaches - does one look that bad? The database was migrated from
Access where the helper table really did help, but using SQL Server I
might not need it.
Thanks again, if you got this far!
Hi Dan
Thank you for your comments. The SQL needed a couple of adjustments: the
CctCatCode fields needed a table prefix to make them unambiguous and also
(probable typo) I need (select c.*) rather than (select tblContact.*) So
the final version is:
SELECT c.*
FROM tblContact c
JOIN tblConCat cc1 ON
c.ConID = cc1.CctConID AND
cc1.CctCatCode=''010101''
JOIN tblConCat cc2 ON
c.ConID = cc2.CctConID AND
cc2.CctCatCode=''010102''
JOIN tblConCat cc3 ON
c.ConID = cc3.CctConID AND
cc3.CctCatCode=''010103''
Anyway, the result again is pretty fast and I guess I am wasting my time
building a helper table if it doesn''t noticeably increase the speed but does
increase the complexity (the helper tables need to be dynamically created in
a multi-user environment).
I looked at the execution plans, but it gives so much information (cost,
subtree cost, etc) that it leaves a simple person like me somewhat lost.
What I would like to know is ''which is better: A or B?'' Even when I run
them, I am not sure how to compare the actual performance. Is there one key
number to look at? Perhaps ''Cumulative client processing time''? Perhaps I
should try to read up on this.
(PS I do have that index you mentioned)
> What I would like to know is ''which is better: A or B?'' Even when I runthem, I am not sure how to compare the actual performance. Is there one
key number to look at?
I usually compare actual query duration in a controlled test environment
with no other activity and clean cache.
CHECKPOINT
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
GO
SELECT GETDATE() AS StartTime
GO
--execute first query
GO
SELECT GETDATE() AS EndTime
GO
CHECKPOINT
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
GO
SELECT GETDATE() AS StartTime
GO
--execute second query
GO
SELECT GETDATE() AS EndTime
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Justin Hoffman" <j@b.com> wrote in message
news:d4**********@nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com...
"Dan Guzman" <gu******@nospam-online.sbcglobal.net> wrote in message
news:vb****************@newssvr11.news.prodigy.com ...I have tried them both out and although I looked at the query analyzer
it provided more information than I knew what to do with. In practise
they both provide similar working times
You can compare the estimated costs by running your queries at the same
time in Query Analyzer with the show execution plan option on. However,
I would still choose the query with the lowest actual execution time.
All things being equal, select the simplest approach.
BTW, make sure you have a unique index on tblConCat (CctConID and
CctCatCode). A primary key, unique constraint or unique index will
provide this.
Untested Solution Three (there are many more):
SELECT tblContact.*
FROM tblContact c
JOIN tblConCat cc1 ON
c.ConID = cc1.CctConID AND
CctCatCode=''010101''
JOIN tblConCat cc2 ON
c.ConID = cc2.CctConID AND
CctCatCode=''010102''
JOIN tblConCat cc3 ON
c.ConID = cc3.CctConID AND
CctCatCode=''010103''
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Justin Hoffman" <j@b.com> wrote in message
news:d4**********@nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...
This is a question concerning query optimisation. Sorry if it''s a bit
long, but thanks to anyone who has the patience to help - This is my
first post here...
If I have two tables: ''tblContact'' and ''tblCategory'' where categories
are like:
Code Name
010101 Short
010102 Fat
010103 Stupid
The junction table ''tblConCat'' has fields CctConID, CctCatCode to tell
me which contacts have which category codes. These are nchar(6) fields,
if it makes any difference.
If I need to find all people who are short, fat and stupid I can see two
ways:
Solution One:
SELECT tblContact.* FROM tblContact WHERE
ConID IN (SELECT CctConID FROM tblConCat WHERE CctCatCode=''010101'') AND
ConID IN (SELECT CctConID FROM tblConCat WHERE CctCatCode=''010102'') AND
ConID IN (SELECT CctConID FROM tblConCat WHERE CctCatCode=''010103'')
Solution Two:
Build a helper table which contains the codes I''m looking for
SELECT tblContact.* FROM tblContact
WHERE ConID IN
(SELECT CctConID
FROM tblConCat INNER JOIN tblHelper
ON tblConCat.CctCatCode = tblHelper.HlpCatCode
GROUP BY CctConID HAVING Count(*)=3)
I have tried them both out and although I looked at the query analyzer
it provided more information than I knew what to do with. In practise
they both provide similar working times (I wait about a second) but I
thought the first looked rather inefficient and thought my helper table
might help. There are about 30,000 contact records, 180 category records
and 120,000 junction table records.
All I am looking for comments on any pros and cons of these two
approaches - does one look that bad? The database was migrated from
Access where the helper table really did help, but using SQL Server I
might not need it.
Thanks again, if you got this far!
Hi Dan
Thank you for your comments. The SQL needed a couple of adjustments: the
CctCatCode fields needed a table prefix to make them unambiguous and also
(probable typo) I need (select c.*) rather than (select tblContact.*) So
the final version is:
SELECT c.*
FROM tblContact c
JOIN tblConCat cc1 ON
c.ConID = cc1.CctConID AND
cc1.CctCatCode=''010101''
JOIN tblConCat cc2 ON
c.ConID = cc2.CctConID AND
cc2.CctCatCode=''010102''
JOIN tblConCat cc3 ON
c.ConID = cc3.CctConID AND
cc3.CctCatCode=''010103''
Anyway, the result again is pretty fast and I guess I am wasting my time
building a helper table if it doesn''t noticeably increase the speed but
does increase the complexity (the helper tables need to be dynamically
created in a multi-user environment).
I looked at the execution plans, but it gives so much information (cost,
subtree cost, etc) that it leaves a simple person like me somewhat lost.
What I would like to know is ''which is better: A or B?'' Even when I run
them, I am not sure how to compare the actual performance. Is there one
key number to look at? Perhaps ''Cumulative client processing time''?
Perhaps I should try to read up on this.
(PS I do have that index you mentioned)
这篇关于SQL找到短，胖，愚蠢的人的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
