This topic has been archived. It cannot be replied.
-
工作学习 / IT技术讨论 / 来来来，抄家伙，练练手艺。我这有一个SQL语句，运行特慢。一万多记录竟然１０分钟没给结果。更恶劣的是今天竟然不给我５０，而是全部！出什么问题了？如何改进？SELECT TOP 50 * FROM Table1
WHERE Table1.ID Not In (SELECT ID FROM Table2) ORDER BY RandomNumber
ID在表１里是unique在表２里是multiple。
-23456789(大白呼);
2004-9-10
{139}
(#1879837@0)
-
SELECT TOP 50 * FROM Table1
WHERE Table1.ID Not In (SELECT unique ID FROM Table2) ORDER BY RandomNumber -- ??
-sarskiller(萍沙秋月);
2004-9-10
(#1879838@0)
-
试了。不中。速度一样地慢。再来。
-23456789(大白呼);
2004-9-10
(#1879849@0)
-
你有INDEX吗？
-647i(盲人协会);
2004-9-10
(#1879841@0)
-
ID
-23456789(大白呼);
2004-9-10
(#1879846@0)
-
if it is too slow, try "exist", "in" is slow, and your "not in" even slower
-ningxin0809(雁影行洲);
2004-9-10
(#1879850@0)
-
我知道就是因为是outer join所以才死喽。这不是在慢的里选快的吗。
-23456789(大白呼);
2004-9-10
(#1879860@0)
-
Try this one:Select Top 50 Table1.* from Table1 left join Table2 on Table1.id=Table2.id where Table2.id is null order by randomnumber
-goodbaby(小宝);
2004-9-10
{120}
(#1879857@0)
-
Maybe better one: SELECT table1.* FROM table1 left join (Select distinct table2.id from table2) T2 on table1.id=t2.id where t2.id is null order by randomnumber
-goodbaby(小宝);
2004-9-10
(#1879888@0)
-
茅厕顿开阿。前段时间花了一天的时间解决不了的access not in 条件慢的问题，小宝手到病除了。当时是6百万条数据，半小时不出结果，同样语句SQL Server20秒不到，所以当时断定NOT IN是ACCESS死穴。
-wonton_soup(Wonton Soup);
2004-9-10
(#1879920@0)
-
用的是Access吧?
-wonton_soup(Wonton Soup);
2004-9-10
(#1879859@0)
-
然
-23456789(大白呼);
2004-9-10
(#1879864@0)
-
not in是Access的死穴，升级Access会好一点。或者表里加个flag分两步走。
-wonton_soup(Wonton Soup);
2004-9-10
(#1879877@0)
-
我还没明白，慢就慢吧，为什么不给５０？小宝的语句很快，不到一秒。看来要避免用nested query，改用outer join query。
-23456789(大白呼);
2004-9-10
(#1879902@0)
-
how many records in your table?
-ningxin0809(雁影行洲);
2004-9-10
(#1879906@0)
-
about 15k
-23456789(大白呼);
2004-9-10
(#1879913@0)