在之前的工作中,我曾经遇到过表特别大的情况,这个时候我想到了使用with来降低查询的消耗,前文中已经有了描述:http://www.cnblogs.com/wingsless/archive/2012/01/15/2323060.html。今天我突然想到,其实用with和from (select)应该没有本质的差别。所以做了个小实验。
我的表大概7W行,使用with的SQL语句如下:
WITH A AS (SELECT T.OWNER,
T.OBJECT_NAME,
T.OBJECT_ID,
T.DATA_OBJECT_ID,
T.OBJECT_TYPE,
T.LAST_DDL_TIME,
T.EDITION_NAME,
T.NAMESPACE
FROM TEST1 T
WHERE T.OBJECT_NAME = 'I_USER1')
SELECT * FROM A;
SELECT *
FROM (SELECT T.OWNER,
T.OBJECT_NAME,
T.OBJECT_ID,
T.DATA_OBJECT_ID,
T.OBJECT_TYPE,
T.LAST_DDL_TIME,
T.EDITION_NAME,
T.NAMESPACE
FROM TEST1 T
WHERE T.OBJECT_NAME = 'I_USER1');
这个表的索引在OBJECT_NAME上。
这两个语句的执行计划:
可以看到两者的执行计划没有任何区别。也就是说,在表的数据量十分巨大的情况下,不管是使用with还是from (select ...)这种形式,可以通过选择需要的列来降低bytes,从而达到提高效率的目的,但是两者孰优孰劣,应该说是没有太大的区别的。
分享到:
相关推荐
CHAPTER 5 SELECT Statement: Common Elements . . . . . . . . . . . . 73 5.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73 5.2 Literals and Their Data ...
select count(*) from Master.dbo.SysProcesses where dbid=db_id() --前10名其他等待类型 SELECT TOP 10 * from sys.dm_os_wait_stats ORDER BY wait_time_ms DESC SELECT *FROM sys.dm_os_wait_stats ...
Table of Contents Introduction....................................................................................................13 Code Examples.........................................................
2.2.2 Configuration With VxWorks Image Projects ............................................... 8 2.2.3 Configuration With VxWorks Source Build Projects ................................... 8 2.2.4 ...
6.1.9 Select a Test Case that Exposes the Runtime Bottleneck . . 68 6.1.10 The Difference Between Algorithm and Implementation . . 70 6.2 Using Profiling Tools . . . . . . . . . . . . 72 6.2.1 Do Not ...
with... as 是公用表表达式,可以理解为创建临时表,如: WITH cte AS(SELECT * FROM TABLE1) SELECT * FROM cte VIEW语句是真正的创建了一个对象,WITH...AS 公用表表达式不创建对象,只能被后随的SELECT语句使用
Action 2: Select the Step Type ...................................................................................3-4 Step Types...........................................................................
3.9 Interaction with other languages...............................................................................43 3.9.1. Interacting with C ..........................................................
测试11:来源于SELECT的INSERTs ................................................... 29 测试12:没有索引的DELETE ......................................................... 29 测试13:有索引的DELETE ............
1)GETTING STARTED WITH VISUAL BASIC .NET An Introduction to Visual Basic .NET ................................................................................2 Install Visual Studio .NET.................
Details -Event filter with query “SELECT * FROM __InstanceModificationEvent WITHIN 60 WHERE TargetInstance ISA “Win32_Processor” AND TargetInstance.LoadPercentage > 99″ could not be reactivated in...
Dependency Injection in Controllers With Minification . . . . . . . . . 37 Overview of Two-Way Data Binding . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38 What Is Data Binding? . . . ....
Microsoft® Excel® 2013: Working with Data Ranges and Tables is a two-lesson intermediate level course that guides you through organizing, entering and validating worksheet data and then effectively ...
and all other areas with abundance of data. The interest in this field by companies, universities, governments, and research organizations has accelerated the advances in the field. This book covers ...
3.9 Interaction with other languages...............................................................................43 3.9.1. Interacting with C ..........................................................
10.2.5. Using profiles to select a data source 10.3. Using JDBC with Spring 10.3.1. Tackling runaway JDBC code 10.3.2. Working with JDBC templates 10.4. Summary Chapter 11. Persisting data with object...
MyBatis is a first class persistence framework with support for custom SQL, stored procedures and advanced mappings. MyBatis eliminates almost all of the JDBC code and manual setting of parameters and...
Select and split data sets into training, test, and validation, and explore validation strategies Discover how to improve and optimize your neural network In Detail Vast quantities of data are ...
Table of Contents Preface, Notes, Licenses . . . . . . . . ....1. Licenses for Third-Party Components ....1.1. FindGTest.cmake License ....1.2. LPeg Library License ....1.3. LuaFileSystem Library License ....
CEIL 和FLOOR ............................................................................................... 73 COS COSH SIN SINH TAN TANH........................................................ 73 ...