Shulou(Shulou.com)05/31 Report--

Xiaobian to share with you how to improve the performance of SQL statements, I believe most people do not know how, so share this article for everyone's reference, I hope you have a lot of gains after reading this article, let's go to understand it together!

1, Returns IDENTITY from INSERT

SELECT @@IDENTITY

2. Embedded views and temporary tables

Temporary tables-Temporary tables in tempdb cause queries to do a lot of I/O and disk accesses, and temporary tables consume a lot of resources.

Inline Views-Use inline views instead of temporary tables. An inline view is just one query that can be joined to a FROM clause. If you only need to join data to other queries, try using inline views to save resources.

3. Avoid LEFT JOIN and NULL

LEFT JOINs consume a lot of resources because they contain data that matches NULL data. In some cases, this is inevitable, but the cost can be very high. LEFT JOINs consume more resources than INNER JOINs, so if you can rewrite the query so that it doesn't use any LEFT JOINs, you get a very good payoff.

One technique to speed up queries using LEFT JOIN involves creating a TABLE data type, inserting all rows from the first table (the table to the left of LEFT JOIN), and then updating the TABLE data type with values from the second table. This technique is a two-step process, but can save a lot of time compared to standard LEFT JOIN. A good rule is to try a variety of different techniques and keep track of how long each technique takes until you get the query that performs best for your application.

DECLARE @tblMonths TABLE (sMonth VARCHAR(7))

4. Flexible use of Cartesian products

I'll go into great detail about this technique and advocate using Cartesian products in some cases. For some reason CROSS JOIN gets a lot of blame, and developers are usually warned not to use them at all. In many cases, they consume too many resources to be used efficiently. But like any tool in SQL, they can be valuable if used correctly.

Here's an example code worth emulating:

--Cartesian product returns all customers for all months. Cartesian multiplication basically multiplies a first table by a second table to produce a set of rows containing the number of rows in the first table multiplied by the number of rows in the second table. Therefore, Cartesian multiplication returns 12(all months)*81(all customers)=972 rows to table @tblFinal. The final step is to update the @tblFinal table with the monthly sales totals for each customer in this date range and select the final rowset.

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 INTEGER SET @dtEndDate = '5/5/1997' SET @dtEndDate = DATEADD(DD, -1, CAST(CAST((MONTH(@dtEndDate) + 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 table SET @i = 0 WHILE (@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 THEN '0' + CAST(MONTH(@dtDate) AS VARCHAR(2)) ELSE CAST(MONTH(@dtDate) AS VARCHAR(2)) END AS sMonth SET @i = @i + 1 END -- Get all clients who had sales during that period into the "y" table INSERT INTO @tblCustomers SELECT DISTINCT c.CustomerID, c.CompanyName, c.ContactName FROM Customers c INNER JOIN Orders o ON c.CustomerID = o.CustomerID WHERE o.OrderDate BETWEEN @dtStartDate AND @dtEndDate INSERT INTO @tblFinal SELECT m.sMonth, c.CustomerID, c.CompanyName, c.ContactName, 0 FROM @tblMonths m CROSS JOIN @tblCustomers c UPDATE @tblFinal SET mSales = mydata.mSales FROM @tblFinal f INNER JOIN ( SELECT c.CustomerID, CAST(YEAR(o.OrderDate) AS VARCHAR(4)) + '-' + CASE WHEN MONTH(o.OrderDate) < 10 THEN '0' + CAST(MONTH(o.OrderDate) AS VARCHAR(2)) ELSE CAST(MONTH(o.OrderDate) AS VARCHAR(2)) END AS sMonth, SUM(od.Quantity * od.UnitPrice) AS mSales FROM Customers c INNER JOIN Orders o ON c.CustomerID = o.CustomerID INNER JOIN [Order Details] od ON 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 THEN '0' + CAST(MONTH(o.OrderDate) AS VARCHAR(2)) ELSE CAST(MONTH(o.OrderDate) AS VARCHAR(2)) END ) mydata on f.CustomerID = mydata.CustomerID AND f.sMonth = mydata.sMonth SELECT f.sMonth, f.CustomerID, f.CompanyName, f.ContactName, f.mSales FROM @tblFinal f ORDER BY f.CompanyName, f.sMonth

5. Fill in the blanks

Here are some other common techniques that can help improve SQL query efficiency. Suppose you want to group all salespeople by region and subtotal their sales, but you want only those salespeople marked active in the database. You can group salespeople by region and eliminate those who are not active using the HAVING clause, or you can do so in the WHERE clause. Performing this operation in a WHERE clause reduces the number of rows that need to be grouped, so it is more efficient than performing it in a HAVING clause. Row-based filtering in the HAVING clause forces the query to group data that would otherwise be excluded in the WHERE clause.

Another efficiency trick is to use the DISTINCT keyword to find individual reports of data rows instead of using the GROUP BY clause. In this case, SQL using the DISTINCT keyword is more efficient. Use GROUP BY only when you need to compute aggregate functions (SUM, COUNT, MAX, etc.). Also, don't use the DISTINCT keyword if your query always returns a unique row by itself. In this case, the DISTINCT keyword only adds overhead.

That's all for "How to improve SQL statement performance". Thanks for reading! I believe that everyone has a certain understanding, hope to share the content to help everyone, if you still want to learn more knowledge, welcome to pay attention to the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.