2011年10月26日 星期三

Xcode 4.2 中的Automatic Reference Counting (ARC)

Automatic Reference Counting (ARC), 自动引用计数,是开发Cocoa程序时的一个编译级别的特性,用于自动内存管理。


在XCode 4.2中,使用模板新建一个工程,该工程将使用ARC特性。


如果你的iOS SDK是iOS5 seed release 2,需要做如下修改,才能避免编译时的错误:

● 在System/Library/Frameworks/CoreFoundation.framework/Headers/CFBase.h中, 将:

CFTypeRef CFMakeCollectable(CFTypeRef cf) CF_AUTOMATED_REFCOUNT_UNAVAILABLE;

修改成:

CFTypeRef CFMakeCollectable(CFTypeRef cf);

●在System/Library/Frameworks/Foundation.framework/Headers/NSObject.h中, 将:

return (__bridge_retain CFTypeRef)X;

修改成:

return (__bridge_retained CFTypeRef)X;


使用ARC将让你远离烦人且容易遗漏的retain,release和autorelease等操作。


ARC的工作原理是,在你编译程序时,将内存操作的代码(retain,release或autorelease)自动添加到需要的位置。即底层上使用和Manual Reference Counting(手工引用计数)一样的内存管理机制,但由于XCode自动帮你在编译时添加内存操作的代码,从而简化了编程的工作。


启用ARC,编译选项中需加上-fobjc-arc,不过这个由XCode在创建工程模板时帮你完成。


4.2以前版本的XCode都不支持ARC。

对操作系统也有要求:Mac OS X v10.6 或 v10.7 (64-bit applications), iOS4或iOS5。注意:其中Mac OS X v10.6和iOS4不支持weak references(弱引用,后面会说明什么是weak references)。


Xcode 4.2提供了一个名为“Convert to Objective-C Automatic Reference Counting”的工具,在Edit->Convert menu下,可以帮你自动将使用Manual Reference Counting的老代码转换成使用ARC的新代码(例如去掉对retain和release的调用)。


一个使用ARC的代码例子:


@interface Person : NSObject

@property (nonatomic, strong) NSString *firstName;

@property (nonatomic, strong) NSString *lastName;

@property (nonatomic, strong) NSNumber *yearOfBirth;

@property (nonatomic, strong) Person *spouse;

@end


@implementation Person

@synthesize firstName, lastName, yearOfBirth, spouse; @end


注意:不再需要重载dealloc函数,因为没有release操作。(strong的语义后面会介绍。)


例子2:

  • (void)contrived {

    Person *aPerson = [[Person alloc] init];

    [aPerson setFirstName:@"William"];

    [aPerson setLastName:@"Dudney"];

    [aPerson:setYearOfBirth:[[NSNumber alloc] initWithInteger:2011]];

    NSLog(@"aPerson: %@", aPerson);

}


注意:没有了release操作。


例子3:

  • (void)takeLastNameFrom:(Person *)person {

    NSString *oldLastname = [self lastName];

    [self setLastName:[person lastName]];

    NSLog(@"Lastname changed from %@ to %@", oldLastname, [self lastName]);

}


注意:ARC会保证 oldLastname引用的对象在NSLog执行结束之前,不会被释放掉。


使用ARC的一些规则:

  • 不能直接调用dealloc方法,不能重载或直接调用retain, release, retainCount, 或 autorelease等方法。

    但可以通过@selector(retain), @selector(release)这样的形式调用。

    用户自定义的dealloc方法,不能调用[super dealloc] ,编译器会自动帮你添加这个代码。

    对Core Foundation-style 的对象,仍可以使用CFRetain, CFRelease等方法。

  • 不能使用NSAllocateObject或NSDeallocateObject去创建对象,请使用alloc方法。

  • 在c语言中的结构体中,不能再使用对象指针。请放弃C结构体,使用Objective-C的类。

  • id和void*之间没有隐式的类型转换,请使用显式类型转换。

  • 不能使用NSAutoreleasePool,ARC提供了@autoreleasepool语句块。

    例如:

    @autoreleasepool {

    // Code, such as a loop that creates a large number of temporary objects.

}

  • 不能使用NSZone。

  • 方法和变量的命名不能以“new”开头。


关于对象的生命周期:

  • weak引用:设置成weak的属性,不会影响对象的生命周期,如果引用的对象已经被释放,引用会指向nil。

  • strong引用:设置成strong的属性,会影响对象的生命周期。

例如:

@property(strong) MyClass *myObject; 和 @property(retain) MyClass *myObject; 是等效的。


又例如:

@property(weak) MyClass *myObject; 和 @property(assign) MyClass *myObject; 在多数情况下等效,但当instance被释放时,设置为weak的引用会指向nil。


可用的限定词:

  • __strong, 默认的

  • __weak

  • __unsafe_unretained, 和weak的区别是当对象被释放时,引用不指向nil。

  • __autoreleasing,当方法的参数是id*,且希望方法返回时对象被autoreleased,可以添加__autoreleasing限定词。


使用__weak时要特别小心,例如

NSString __weak *string = [[NSString alloc] initWithFormat:@"First Name: %@", [self firstName]];

NSLog(@"string: %@", string); //此时 string为空,因为weak类型不影响对象的生命周期,对象刚创建就释放了。


其他特性:

  • 使用strong, weak, autoreleasing限定的变量会被隐式初始化为nil。

    例如:

    - (void)myMethod { NSString *name;

NSLog(@"name: %@", name); //会输出null

}


source: http://blog.sina.com.cn/s/blog_4c4c79950100t3uy.html

Error Handling Best Practice in IOS Dev

Exception Handling

The Objective-C language has an exception-handling syntax similar to that of Java and C++. By using this syntax with the NSException, NSError, or custom classes, you can add robust error-handling to your programs. This chapter provides a summary of exception syntax and handling; for more details, see Exception Programming Topics.

Enabling Exception-Handling

Using GNU Compiler Collection (GCC) version 3.3 and later, Objective-C provides language-level support for exception handling. To turn on support for these features, use the -fobjc-exceptions switch of the GNU Compiler Collection (GCC) version 3.3 and later. (Note that this switch renders the application runnable only in Mac OS X v10.3 and later because runtime support for exception handling and synchronization is not present in earlier versions of the software.)

Exception Handling

An exception is a special condition that interrupts the normal flow of program execution. There are a variety of reasons why an exception may be generated (exceptions are typically said to beraised or thrown), by hardware as well as software. Examples include arithmetical errors such as division by zero, underflow or overflow, calling undefined instructions (such as attempting to invoke an unimplemented method), and attempting to access a collection element out of bounds.

Objective-C exception support involves four compiler directives: @try, @catch, @throw, and @finally:

  • Code that can potentially throw an exception is enclosed in a @try{} block.

  • A @catch{} block contains exception-handling logic for exceptions thrown in a @try{} block. You can have multiple @catch{} blocks to catch different types of exception. (For a code example, see “Catching Different Types of Exception.”)

  • You use the @throw directive to throw an exception, which is essentially an Objective-C object. You typically use an NSException object, but you are not required to.

  • A @finally{} block contains code that must be executed whether an exception is thrown or not.

This example depicts a simple exception-handling algorithm:

Cup *cup = [[Cup alloc] init];
 
@try {
    [cup fill];
}
@catch (NSException *exception) {
    NSLog(@"main: Caught %@: %@", [exception name], [exception reason]);
}
@finally {
    [cup release];
}

Catching Different Types of Exception

To catch an exception thrown in a @try{} block, use one or more @catch{}blocks following the @try{} block. The @catch{} blocks should be ordered from most-specific to least-specific. That way you can tailor the processing of exceptions as groups, as shown in Listing 10-1.

Listing 10-1 An exception handler

@try {
    ...
}
@catch (CustomException *ce) {   // 1
    ...
}
@catch (NSException *ne) {       // 2
    // Perform processing necessary at this level.
    ...
 
}
@catch (id ue) {
    ...
}
@finally {                       // 3
    // Perform processing necessary whether an exception occurred or not.
    ...
}

The following list describes the numbered code lines:

  1. Catches the most specific exception type.

  2. Catches a more general exception type.

  3. Performs any clean-up processing that must always be performed, whether exceptions were thrown or not.

Throwing Exceptions

To throw an exception, you must instantiate an object with the appropriate information, such as the exception name and the reason it was thrown.

NSException *exception = [NSException exceptionWithName: @"HotTeaException"
                                                 reason: @"The tea is too hot"
                                               userInfo: nil];
@throw exception;

Inside a @catch{} block, you can rethrow the caught exception using the @throw directive without providing an argument. Leaving out the argument in this case can help make your code more readable.

You are not limited to throwing NSException objects. You can throw any Objective-C object as an exception object. The NSException class provides methods that help in exception processing, but you can implement your own if you so desire. You can also subclass NSException to implement specialized types of exceptions, such as file-system exceptions or communications exceptions.


source: link

2011年10月19日 星期三

Oracle Hint

在SQL中增加/*ordered */
會使查詢效能顯著提升
原來這叫做Oracle Hint

常見Oracle HINT的用法:
1. /*+ALL_ROWS*/
表明對語句塊選擇基於開銷的優化方法,並獲得最佳吞吐量,使資源消耗最小化.
例如:
SELECT /*+ALL+_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO=’SCOTT’;

2. /*+FIRST_ROWS*/
表明對語句塊選擇基於開銷的優化方法,並獲得最佳響應時間,使資源消耗最小化.
例如:
SELECT /*+FIRST_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO=’SCOTT’;

3. /*+CHOOSE*/
表明如果數據字典中有訪問表的統計資料,將基於開銷的優化方法,並獲得最佳的吞吐量;
表明如果數據字典中沒有訪問表的統計資料,將基於規則開銷的優化方法;
例如:
SELECT /*+CHOOSE*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO=’SCOTT’;

4. /*+RULE*/
表明對語句塊選擇基於規則的優化方法.
例如:
SELECT /*+ RULE */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO=’SCOTT’;

5. /*+FULL(TABLE)*/
表明對表選擇全局掃描的方法.
例如:
SELECT /*+FULL(A)*/ EMP_NO,EMP_NAM FROM BSEMPMS A WHERE EMP_NO=’SCOTT’;

6. /*+ROWID(TABLE)*/
提示明確表明對指定表根據ROWID進行訪問.
例如:
SELECT /*+ROWID(BSEMPMS)*/ * FROM BSEMPMS WHERE ROWID>=’AAAAAAAAAAAAAA’
AND EMP_NO=’SCOTT’;

7. /*+CLUSTER(TABLE)*/
提示明確表明對指定表選擇簇掃描的訪問方法,它只對簇對像有效.
例如:
SELECT /*+CLUSTER */ BSEMPMS.EMP_NO,DPT_NO FROM BSEMPMS,BSDPTMS
WHERE DPT_NO=’TEC304′ AND BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

8. /*+INDEX(TABLE INDEX_NAME)*/
表明對錶選擇索引的掃描方法.
例如:
SELECT /*+INDEX(BSEMPMS SEX_INDEX) USE SEX_INDEX BECAUSE THERE ARE FEWMALE BSEMPMS */ FROM BSEMPMS WHERE SEX=’M';

9. /*+INDEX_ASC(TABLE INDEX_NAME)*/
表明對錶選擇索引升序的掃描方法.
例如:
SELECT /*+INDEX_ASC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS WHERE DPT_NO=’SCOTT’;

10. /*+INDEX_COMBINE*/
為指定表選擇位圖訪問路經,如果INDEX_COMBINE中沒有提供作為參數的索引,將選擇出位圖索引的布爾組合方式.
例如:
SELECT /*+INDEX_COMBINE(BSEMPMS SAL_BMI HIREDATE_BMI)*/ * FROM BSEMPMS
WHERE SAL<5000000 emp_no="SCOTT" sex="M" dpt_no="V.DPT_NO">V.AVG_SAL;

20. /*+NO_MERGE(TABLE)*/
對於有可合併的視圖不再合併.
例如:
SELECT /*+NO_MERGE(V) */ A.EMP_NO,A.EMP_NAM,B.DPT_NO FROM BSEMPMS A (SELECT DPT_NO,AVG(SAL) AS AVG_SAL FROM BSEMPMS B GROUP BY DPT_NO) V WHERE A.DPT_NO=V.DPT_NO AND A.SAL>V.AVG_SAL;

21. /*+ORDERED*/
根據表出現在FROM中的順序,ORDERED使ORACLE依此順序對其連接.
例如:
SELECT /*+ORDERED*/ A.COL1,B.COL2,C.COL3 FROM TABLE1 A,TABLE2 B,TABLE3 C WHERE A.COL1=B.COL1 AND B.COL1=C.COL1;

22. /*+USE_NL(TABLE)*/
將指定表與嵌套的連接的行源進行連接,並把指定表作為內部表.
例如:
SELECT /*+ORDERED USE_NL(BSEMPMS)*/ BSDPTMS.DPT_NO,BSEMPMS.EMP_NO,BSEMPMS.EMP_NAM FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

23. /*+USE_MERGE(TABLE)*/
將指定的表與其他行源通過合併排序連接方式連接起來.
例如:
SELECT /*+USE_MERGE(BSEMPMS,BSDPTMS)*/ * FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

24. /*+USE_HASH(TABLE)*/
將指定的表與其他行源通過hash連接方式連接起來.
例如:
SELECT /*+USE_HASH(BSEMPMS,BSDPTMS)*/ * FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

25. /*+DRIVING_SITE(TABLE)*/
強制與ORACLE所選擇的位置不同的表進行查詢執行.
例如:
SELECT /*+DRIVING_SITE(DEPT)*/ * FROM BSEMPMS,DEPT@BSDPTMS WHERE BSEMPMS.DPT_NO=DEPT.DPT_NO;

26. /*+LEADING(TABLE)*/
將指定的表作為連接次序中的首表.

27. /*+CACHE(TABLE)*/
當進行 full scan 時,CACHE提示能夠將表的檢索塊放置在緩衝區緩存中最近最少列表LRU的最近使用端
例如:
SELECT /*+FULL(BSEMPMS) CAHE(BSEMPMS) */ EMP_NAM FROM BSEMPMS;

28. /*+NOCACHE(TABLE)*/
當進行 full scan 時,CACHE提示能夠將表的檢索塊放置在緩衝區緩存中最近最少列表LRU的最近使用端
例如:
SELECT /*+FULL(BSEMPMS) NOCAHE(BSEMPMS) */ EMP_NAM FROM BSEMPMS;

29. /*+APPEND*/
直接插入到表的最後,可以提高速度.
insert /*+append*/ into test1 select * from test4 ;

30. /*+NOAPPEND*/
通過在插入語句生存期內停止並行模式來啟動常規插入.
insert /*+noappend*/ into test1 select * from test4 ;

source: http://iqloss.blogspot.com/2011/04/oracle-hint.html

2011年10月18日 星期二

五种提高 SQL 性能的方法

五种提高 SQL 性能的方法

发布日期: 4/1/2004 | 更新日期: 4/1/2004

Johnny Papa

Data Points Archive

有时, 为了让应用程序运行得更快,所做的全部工作就是在这里或那里做一些很小调整。啊,但关键在于确定如何进行调整!迟早您会遇到这种情况:应用程序中的 SQL 查询不能按照您想要的方式进行响应。它要么不返回数据,要么耗费的时间长得出奇。如果它降低了报告或您的企业应用程序的速度,用户必须等待的时间过长,他们就会很不满意。就像您的父母不想听您解释为什么在深更半夜才回来一样,用户也不会听你解释为什么查询耗费这么长时间。(“对不起,妈妈,我使用了太多的 LEFT JOIN。”)用户希望应用程序响应迅速,他们的报告能够在瞬间之内返回分析数据。就我自己而言,如果在 Web 上冲浪时某个页面要耗费十多秒才能加载(好吧,五秒更实际一些),我也会很不耐烦。

为了解决这些问题,重要的是找到问题的根源。那么,从哪里开始呢?根本原因通常在于数据库设计和访问它的查询。在本月的专栏中,我将讲述四项技术,这些技术可用于提高基于 SQL Server? 的应用程序的性能或改善其可伸缩性。我将仔细说明 LEFT JOIN、CROSS JOIN 的使用以及 IDENTITY 值的检索。请记住,根本没有神奇的解决方案。调整您的数据库及其查询需要占用时间、进行分析,还需要大量的测试。这些技术都已被证明行之有效,但对您的应用程序而言,可能其中一些技术比另一些技术更适用。

*
本页内容
     从 INSERT 返回 IDENTITY    从 INSERT 返回 IDENTITY
     内嵌视图与临时表    内嵌视图与临时表
     避免 LEFT JOIN 和 NULL    避免 LEFT JOIN 和 NULL
     灵活使用笛卡尔乘积    灵活使用笛卡尔乘积
     拾遗补零    拾遗补零

从 INSERT 返回 IDENTITY

我决定从遇到许多问题的内容入手:如何在执行 SQL INSERT 后检索 IDENTITY 值。通常,问题不在于如何编写检索值的查询,而在于在哪里以及何时进行检索。在 SQL Server 中,下面的语句可用于检索由最新在活动数据库连接上运行的 SQL 语句所创建的 IDENTITY 值:

SELECT @@IDENTITY

这个 SQL 语句并不复杂,但需要记住的一点是:如果这个最新的 SQL 语句不是 INSERT,或者您针对非 INSERT SQL 的其他连接运行了此 SQL,则不会获得期望的值。您必须运行下列代码才能检索紧跟在 INSERT SQL 之后且位于同一连接上的 IDENTITY,如下所示:

INSERT INTO Products (ProductName) VALUES ('Chalk')  SELECT @@IDENTITY

在一个连接上针对 Northwind 数据库运行这些查询将返回一个名称为 Chalk 的新产品的 IDENTITY 值。所以,在使用 ADO 的 Visual Basic? 应用程序中,可以运行以下语句:

Set oRs = oCn.Execute("SET NOCOUNT ON;INSERT INTO Products _ (ProductName) VALUES ('Chalk');SELECT @@IDENTITY")  lProductID = oRs(0)

此代码告诉 SQL Server 不要返回查询的行计数,然后执行 INSERT 语句,并返回刚刚为这个新行创建的 IDENTITY 值。SET NOCOUNT ON 语句表示返回的记录集有一行和一列,其中包含了这个新的 IDENTITY 值。如果没有此语句,则会首先返回一个空的记录集(因为 INSERT 语句不返回任何数据),然后会返回第二个记录集,第二个记录集中包含 IDENTITY 值。这可能有些令人困惑,尤其是因为您从来就没有希望过 INSERT 会返回记录集。之所以会发生此情况,是因为 SQL Server 看到了这个行计数(即一行受到影响)并将其解释为表示一个记录集。因此,真正的数据被推回到了第二个记录集。当然您可以使用 ADO 中的 NextRecordset 方法获取此第二个记录集,但如果总能够首先返回该记录集且只返回该记录集,则会更方便,也更有效率。

此方法虽然有效,但需要在 SQL 语句中额外添加一些代码。获得相同结果的另一方法是在 INSERT 之前使用 SET NOCOUNT ON 语句,并将 SELECT @@IDENTITY 语句放在表中的 FOR INSERT 触发器中,如下面的代码片段所示。这样,任何进入该表的 INSERT 语句都将自动返回 IDENTITY 值。

CREATE TRIGGER trProducts_Insert ON Products FOR INSERT AS      SELECT @@IDENTITY  GO 

触发器只在 Products 表上发生 INSERT 时启动,所以它总是会在成功 INSERT 之后返回一个 IDENTITY。使用此技术,您可以始终以相同的方式在应用程序中检索 IDENTITY 值。

内嵌视图与临时表

某些时候,查询需要将数据与其他一些可能只能通过执行 GROUP BY 然后执行标准查询才能收集的数据进行联接。例如,如果要查询最新五个定单的有关信息,您首先需要知道是哪些定单。这可以使用返回定单 ID 的 SQL 查询来检索。此数据就会存储在临时表(这是一个常用技术)中,然后与 Products 表进行联接,以返回这些定单售出的产品数量:

CREATE TABLE #Temp1 (OrderID INT NOT NULL, _                      OrderDate DATETIME NOT NULL)  INSERT INTO #Temp1 (OrderID, OrderDate) SELECT     TOP 5 o.OrderID, o.OrderDate FROM Orders o ORDER BY o.OrderDate DESC  SELECT     p.ProductName, SUM(od.Quantity) AS ProductQuantity FROM     #Temp1 t      INNER JOIN [Order Details] od ON t.OrderID = od.OrderID     INNER JOIN Products p ON od.ProductID = p.ProductID  GROUP BY p.ProductName ORDER BY p.ProductName  DROP TABLE #Temp1

这些 SQL 语句会创建一个临时表,将数据插入该表中,将其他数据与该表进行联接,然后除去该临时表。这会导致此查询进行大量 I/O 操作,因此,可以重新编写查询,使用内嵌视图取代临时表。内嵌视图只是一个可以联接到 FROM 子句中的查询。所以,您不用在 tempdb 中的临时表上耗费大量 I/O 和磁盘访问,而可以使用内嵌视图得到同样的结果:

SELECT p.ProductName,      SUM(od.Quantity) AS ProductQuantity FROM     (     SELECT TOP 5 o.OrderID, o.OrderDate     FROM     Orders o      ORDER BY o.OrderDate DESC     ) t      INNER JOIN [Order Details] od ON t.OrderID = od.OrderID     INNER JOIN Products p ON od.ProductID = p.ProductID  GROUP BY     p.ProductName ORDER BY     p.ProductName

此查询不仅比前面的查询效率更高,而且长度更短。临时表会消耗大量资源。如果只需要将数据联接到其他查询,则可以试试使用内嵌视图,以节省资源。

避免 LEFT JOIN 和 NULL

当然,有很多时候您需要执行 LEFT JOIN 和使用 NULL 值。但是,它们并不适用于所有情况。改变 SQL 查询的构建方式可能会产生将一个花几分钟运行的报告缩短到只花几秒钟这样的天壤之别的效果。有时,必须在查询中调整数据的形态,使之适应应用程序所要求的显示方式。虽然 TABLE 数据类型会减少大量占用资源的情况,但在查询中还有许多区域可以进行优化。SQL 的一个有价值的常用功能是 LEFT JOIN。它可以用于检索第一个表中的所有行、第二个表中所有匹配的行、以及第二个表中与第一个表不匹配的所有行。例如,如果希望返回每个客户及其定单,使用 LEFT JOIN 则可以显示有定单和没有定单的客户。

此工具可能会被过度使用。LEFT JOIN 消耗的资源非常之多,因为它们包含与 NULL(不存在)数据匹配的数据。在某些情况下,这是不可避免的,但是代价可能非常高。LEFT JOIN 比 INNER JOIN 消耗资源更多,所以如果您可以重新编写查询以使得该查询不使用任何 LEFT JOIN,则会得到非常可观的回报(请参阅图 1 中的图)。


图 1:查询

加快使用 LEFT JOIN 的查询速度的一项技术涉及创建一个 TABLE 数据类型,插入第一个表(LEFT JOIN 左侧的表)中的所有行,然后使用第二个表中的值更新 TABLE 数据类型。此技术是一个两步的过程,但与标准的 LEFT JOIN 相比,可以节省大量时间。一个很好的规则是尝试各种不同的技术并记录每种技术所需的时间,直到获得用于您的应用程序的执行性能最佳的查询。

测试查询的速度时,有必要多次运行此查询,然后取一个平均值。因为查询(或存储过程)可能会存储在 SQL Server 内存中的过程缓存中,因此第一次尝试耗费的时间好像稍长一些,而所有后续尝试耗费的时间都较短。另外,运行您的查询时,可能正在针对相同的表运行其他查询。当其他查询锁定和解锁这些表时,可能会导致您的查询要排队等待。例如,如果您进行查询时某人正在更新此表中的数据,则在更新提交时您的查询可能需要耗费更长时间来执行。

避免使用 LEFT JOIN 时速度降低的最简单方法是尽可能多地围绕它们设计数据库。例如,假设某一产品可能具有类别也可能没有类别。如果 Products 表存储了其类别的 ID,而没有用于某个特定产品的类别,则您可以在字段中存储 NULL 值。然后您必须执行 LEFT JOIN 来获取所有产品及其类别。您可以创建一个值为“No Category”的类别,从而指定外键关系不允许 NULL 值。通过执行上述操作,现在您就可以使用 INNER JOIN 检索所有产品及其类别了。虽然这看起来好像是一个带有多余数据的变通方法,但可能是一个很有价值的技术,因为它可以消除 SQL 批处理语句中消耗资源较多的 LEFT JOIN。在数据库中全部使用此概念可以为您节省大量的处理时间。请记住,对于您的用户而言,即使几秒钟的时间也非常重要,因为当您有许多用户正在访问同一个联机数据库应用程序时,这几秒钟实际上的意义会非常重大。

灵活使用笛卡尔乘积

对于此技巧,我将进行非常详细的介绍,并提倡在某些情况下使用笛卡尔乘积。出于某些原因,笛卡尔乘积 (CROSS JOIN) 遭到了很多谴责,开发人员通常会被警告根本就不要使用它们。在许多情况下,它们消耗的资源太多,从而无法高效使用。但是像 SQL 中的任何工具一样,如果正确使用,它们也会很有价值。例如,如果您想运行一个返回每月数据(即使某一特定月份客户没有定单也要返回)的查询,您就可以很方便地使用笛卡尔乘积。 图 2 中的 SQL 就执行了上述操作。

虽然这看起来好像没什么神奇的,但是请考虑一下,如果您从客户到定单(这些定单按月份进行分组并对销售额进行小计)进行了标准的 INNER JOIN,则只会获得客户有定单的月份。因此,对于客户未订购任何产品的月份,您不会获得 0 值。如果您想为每个客户都绘制一个图,以显示每个月和该月销售额,则可能希望此图包括月销售额为 0 的月份,以便直观标识出这些月份。如果使用 图 2 中的 SQL,数据则会跳过销售额为 0 美元的月份,因为在定单表中对于零销售额不会包含任何行(假设您只存储发生的事件)。

图 3 中的代码虽然较长,但是可以达到获取所有销售数据(甚至包括没有销售额的月份)的目标。首先,它会提取去年所有月份的列表,然后将它们放入第一个 TABLE 数据类型表 (@tblMonths) 中。下一步,此代码会获取在该时间段内有销售额的所有客户公司的名称列表,然后将它们放入另一个 TABLE 数据类型表 (@tblCus-tomers) 中。这两个表存储了创建结果集所必需的所有基本数据,但实际销售数量除外。 第一个表中列出了所有月份(12 行),第二个表中列出了这个时间段内有销售额的所有客户(对于我是 81 个)。并非每个客户在过去 12 个月中的每个月都购买了产品,所以,执行 INNER JOIN 或 LEFT JOIN 不会返回每个月的每个客户。这些操作只会返回购买产品的客户和月份。

笛卡尔乘积则可以返回所有月份的所有客户。笛卡尔乘积基本上是将第一个表与第二个表相乘,生成一个行集合,其中包含第一个表中的行数与第二个表中的行数相乘的结果。因此,笛卡尔乘积会向表 @tblFinal 返回 972 行。最后的步骤是使用此日期范围内每个客户的月销售额总计更新 @tblFinal 表,以及选择最终的行集。

如果由于笛卡尔乘积占用的资源可能会很多,而不需要真正的笛卡尔乘积,则可以谨慎地使用 CROSS JOIN。例如,如果对产品和类别执行了 CROSS JOIN,然后使用 WHERE 子句、DISTINCT 或 GROUP BY 来筛选出大多数行,那么使用 INNER JOIN 会获得同样的结果,而且效率高得多。如果需要为所有的可能性都返回数据(例如在您希望使用每月销售日期填充一个图表时),则笛卡尔乘积可能会非常有帮助。但是,您不应该将它们用于其他用途,因为在大多数方案中 INNER JOIN 的效率要高得多。

拾遗补零

这里介绍其他一些可帮助提高 SQL 查询效率的常用技术。假设您将按区域对所有销售人员进行分组并将他们的销售额进行小计,但是您只想要那些数据库中标记为处于活动状态的销售人员。您可以按区域对销售人员分组,并使用 HAVING 子句消除那些未处于活动状态的销售人员,也可以在 WHERE 子句中执行此操作。在 WHERE 子句中执行此操作会减少需要分组的行数,所以比在 HAVING 子句中执行此操作效率更高。HAVING 子句中基于行的条件的筛选会强制查询对那些在 WHERE 子句中会被去除的数据进行分组。

另一个提高效率的技巧是使用 DISTINCT 关键字查找数据行的单独报表,来代替使用 GROUP BY 子句。在这种情况下,使用 DISTINCT 关键字的 SQL 效率更高。请在需要计算聚合函数(SUM、COUNT、MAX 等)的情况下再使用 GROUP BY。另外,如果您的查询总是自己返回一个唯一的行,则不要使用 DISTINCT 关键字。在这种情况下,DISTINCT 关键字只会增加系统开销。

您已经看到了,有大量技术都可用于优化查询和实现特定的业务规则,技巧就是进行一些尝试,然后比较它们的性能。最重要的是要测试、测试、再测试。在此专栏的将来各期内容中,我将继续深入讲述 SQL Server 概念,包括数据库设计、好的索引实践以及 SQL Server 安全范例。


http://www.microsoft.com/china/MSDN/library/data/sqlserver/FiveWaystoRevupYourSQLPerformanCE.mspx?mfr=true

source:

2011年10月17日 星期一

Combine Multiple Columns And Records In MSSQL


In this example i am going to describe how to combine multiple columns and records in one column in MS SQL.

Here is the scenario

I have a table having Employees names and their respective Department names, now i want to show Employees names separated by comma into one column and respective Department name in another column.

My table schema is shown in the image below



And this is Data into table



I want output in following format

Department FirstName
IT amiT,Emp1,Emp5
Admin Shobhit, Emp3,Emp7


and so on

To get this desired result we need to write below mentioned query

SELECT DISTINCT Department, EmpNames = substring( ( SELECT ', ' + FirstName  FROM Employees e2 WHERE e2.Department = e1.Department FOR XML path(''), elements  ),2,500) FROM Employees e1 

And the output of this SQL Query would be