1. Return to Identity SELECT @@ identity from INSERT
2, inline view and temporary table temporary table - temporary table in Tempdb will cause a query to make a large number of I / O operations and disk access, and temporary tables consume a lot of resources. Inline view - use in-in-room instead of temporary table. The embedded view is just a query that can be coupled to the FROM clause. If you only need to join the data to another query, you can try the inline view to save resources. 3, avoiding the resources consumed by left Join and Null Left Join, because they contain data that matches NULL data. In some cases, this is inevitable, but the consideration may be very high. Left Join consumes more resources than Inner Join, so if you can rewrite the query so that the query does not use any Left Join, it will get a very considerable return. A technique accelerating using the Query speed of Left Join involves creating a Table data type, inserting all rows in the first table (left on the left side of the left), then update the Table data type using the value in the second table. This technology is a two-step process, but it saves a lot of time compared to standard Left Join. A good rule is to try a variety of different technologies and record the time required for each technology until you have the best performance for your application's execution performance. Declare @TBLMONTHS TABLE (SMONTH VARCHAR (7)) 4, flexible use of Cartesol's product for this skill, I will introduce a very detailed introduction and advocate in some cases using Cartesol product. For some reasons, Cartin Join has been condemned, and developers are usually not used to use them at all. In many cases, they consume too much resources, causing efficient use. But like any tool in SQL, if you use it correctly, they will also be valuable. One of the sample code is worthy of emulation: - Cartesian product can return all customers in all months. The Cartesian product is basically multiplied by the first table with the second table, generating a row collection, which contains the result of the number of rows in the first table multiplied by the number of rows in the second table. Therefore, Cartesian product returns 12 (all months) * 81 (all customers) = 972 lines to Table @TBLFINAL. The final step is to update the @TBLFinal table for each customer's monthly sales of each customer in the range of this date, and select the final row. DECLARE @tblMonths TABLE (sMonth VARCHAR (7)) DECLARE @tblCustomers TABLE (CustomerID CHAR (10), CompanyName VARCHAR (50), ContactName VARCHAR (50)) DECLARE @tblFinal TABLE (sMonth VARCHAR (7), CustomerID CHAR (10) , CompanyName Varchar (50), ContactName VARCHAR (50), MSALES MONEY
Declare @dtstartdate DateTime, @dtendDate datetime, @dtdate datetime, @i integerset @dtenddate = '5/5 / 1997'set @dtendDate = dateadd (DD, -1, Cast (Cast ((@dtendddate) 1) As varchar (2)) '/ 01 /' cast (Year (@dtenddate) as varchar (4)) '23:59:59' as datetime)) Set @dtstartdate = dateadd (mm, -1 * 12 , @dtendDate) - Get All Months Into the first tableset @i = 0WHILE (@i <12) Begin set @dtdate = dateadd (mm, -1 * @i, @dtendDate) Insert Into @tblmonths Select Cast (Year) @dtdate) AS VARCHAR (4)) '-' Case When Month (@dtdate) <10 TEN '0' CAST (Month (@dtdate) as varchar (2)) ELSE CAST (Month (@dtdate) AS VARCHAR (2)) END AS sMonth SET @i = @i 1END-- Get all clients who had sales during that period into the "y" tableINSERT INTO @tblCustomers SELECT DISTINCT c.CustomerID, c.CompanyName, c.ContactName FROM Customers C Inner Join ORDERS O on C.Customer ID = o.CustomerID WHERE o.OrderDate BETWEEN @dtStartDate AND @dtEndDateINSERT INTO @tblFinalSELECT m.sMonth, c.CustomerID, c.CompanyName, c.ContactName, 0FROM @tblMonths m CROSS JOIN @tblCustomers cUPDATE @tblFinal SET mSales = mydata. Msalesfrom @tblfinal f inner Join (Select C.Customerid, Cast (Year (O.OrDerdate) AS VARCHAR (4)) '-' Case When Month (O.Orderdate) <10 Ten '0'
CAST (Month (O.orderdate) AS VARCHAR (2)) Else Cast (Month (O.RDERDATE) AS VARCHAR (2)) End As Smont, Sum (Od.quantity * Od.Unitprice) AS MSALES from Customers C Inner Join ORDERS o on c.customerid = o.customerid inner Join [ORDER DETAILS] OD O. ORDERID = OD.Orderid WHERE O.OrDerdate Between @dtstartDate and @dtendDate Group by C.Customerid, Cast (Year (O.OrDerdate) AS VARCHAR (4)) '-' Case WHEN MONTH (O.OrDerdate) <10 TEN '0' CAST (Month (O.OrDerdate) As Varchar (2)) Else Cast (Month (O.OrDerdate) As Varchar (2))) MyData on f.customerid = mydata.customerid and f.smonth = mydata.smonth select f.smonth, f.customerid, f.companyname, f.contactname, f.msalesfrom @tblfinal forder by f.companyname , F.SMONTH5, picking the picked up zero here to introduce other common technologies that can help improve SQL query efficiency. Suppose you will group all sales personnel according to the area and make their sales small, but you only want those salesperson marked as active. You can group salesperson by region and use the Having clause to eliminate salespeople that are not active, or do this in the WHERE clause. Doing this in the WHERE clause will reduce the number of rows that require packets, so do this more efficient than in the Having clause. The screening of row-based conditions in the Having clause will force queries to group data that will be removed in the WHERE clause.
