五种提高SQL性能的方法(二)

d) and interprets it as representing a Recordset. So the true data is pushed back into a second Recordset. While you can get to this second Recordset using the NextRecordset method in ADO, it is much easier (and more efficient) if you can always count on the Recordset being the first and only one returned.
 While this technique gets the job done, it does require extra code in the SQL statement. Another way of getting the same result is to use the SET NOCOUNT ON statement preceding the INSERT and to put the SELECT @@IDENTITY statement in a FOR INSERT trigger on the table, as shown in the following code snippet. This way, any INSERT statement into that table will automatically return the IDENTITY value.
 CREATE TRIGGER trProducts_Insert ON Products FOR INSERT AS
     SELECT @@IDENTITY
 GO
 The trigger only fires when an INSERT occurs on the Products table, so it always will return an IDENTITY after a successful INSERT. Using this technique, you can consistently retrieve IDENTITY values in the same manner across your application.
 Inline Views Versus Temp Tables
 Queries sometimes need to join data to other data that may only be gathered by performing a GROUP BY and then a standard query. For example, if you want to return the information about the five most recently placed orders, you would first need to know which orders they are. This can be retrieved by using a SQL query that returns the orders' IDs. This data could be stored in a temporary table, a common technique, and then joined to the Product table to return the quantity of products sold on those orders:
 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
 This batch of SQL creates a temporary table, inserts the data into it, joins other data to it, and drops the temporary table. This is a lot of I/O for this query, which could be rewritten to use an inline view instead of a temporary table. An inline view is simply a query that can be joined to in the FROM clause. So instead of spending a lot of I/O and disk access in tempdb on a temporary table, you could instead use an inline view to get the same result:
 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
   &

首页 上一页 1 2 3 4 5 6 7 下一页 尾页 2/7/7

五种提高SQL性能的方法(二)相关范文