取数据下面哪句sql语句效率高(100分)

[xu770]

有100w条数据
去中间10w-11w条数据
下面哪个方法效率高?
1.
SELECT top 10000 [ID]
,[GUID]
,[Name]
FROM
(
SELECT top 110000 [ID]
,[GUID]
,[Name]
FROM [Test]
order by id
) as t
order by id desc

2.
SELECT top 10000 [ID]
,[GUID]
,[Name]
FROM [Test]
where id not in
(
SELECT top 100000 [ID]
FROM [Test]
order by id
)
order by id

[dawugui]

相比较而言,第一个效率高.
第二个不出意外,其运行时间将在一个小时以上.

[wxiaol]

第一个

[wgzaaa]

个人认为第一个高，其实这都不是效率高的，应该先查前100000的最大id再top 10000 应该效率会高些

[LYDF4151]

第一个

[dobear_0922]

第一个效率要高一些

[you_tube]

第一个高,第二个用了
not in

[ojuju10]

在查询分析器里面测试下，必要的时候也要设置下索引，用索引去查询速度快很多

[想飞的狼]

1

[fengming2222]

第一个
在查询分析器执行
然后看执行计划就知道了.

[hhlis1]

【高薪猎头职位】上海外企急聘.NET TeamLeader！

简历到Email: [e-mail]
MSN: [e-mail]

地点：上海
职位：TeamLeader(.Net)
年限：4年以上经验。

Title: TeamLeader(.Net)

Responsibilities:
·Lead developers to deliver high quality software source code on time
·Track the developers' schedule and always beat the deadline
·Improve the team's tech standard and increase developers' productivity
·Write design documents and implement the core modules.
·Interface with developers and related teams (BA, Tester, QA).
·Handle multiple problems and priorities.
·Mentor other developers

Requirements:
·Team leadership and management
·Bachelor or above in computer science or equivalent
·More than 4 years .Net application experience, at least 2 years experience
as a team leader. Experience in multi-national corporations is a plus
·Familiar with standard software development process (e.g. RUP, CMM, etc.)
·Knowledge of .NET Framework, OO Methodology, UML and Design Patterns
·Strong experience in the latest .Net technologies (.Net 2.0, c#, web
service, etc.), experience in java or C++ is a plus.
·Good communication & problem solving skills.
·Good English both in oral and written
·Good project planning and time management skills
·Ability to work under a fast paced delivery oriented environment

[顽固己死_有事烧纸]

exec master..xp_cmdshell 'bcp 数据库.dbo.表A out 数据库.dbo.表B -c -F"100000" -L"110000" -q -U"sa" -P"" '

感觉用BCP会快一点~~~

[顽固己死_有事烧纸]

我从一个36万数据的表中取10-15万的数据 用了5秒多点~

[lost_queen]

学习～ 真没有用过BCP

[顽固己死_有事烧纸]

貌似 BCP 不能从表导到表~~~我再看看~~大不了先导到文本 再导回到表里
exec master..xp_cmdshell 'bcp 数据库.dbo.表A out B.TXT -c -F"100000" -L"110000" -q -U"sa" -P"" '
exec master..xp_cmdshell 'bcp 数据库.dbo.表A in B.TXT -c -q -U"sa" -P"" '

[顽固己死_有事烧纸]

上面第二个 表A改成表B

[lt1129]

第一个高, 第二个用了not in,效率就不高了

[lllcui]

第一个高，但是ＩＤ是倒着了，使用不太方便．

[sp4]

很明显第一个好，测试一下就知道了。

因为第一个执行了一次表扫描
第2个执行了2次扫描 还进行了一次Merge join

[sp4]

至于TOP排序都是2次

[penglewen]

为什么in的效率会很慢呢....