Removal of function calls can have better performance

Original: Removal of function calls can have better performance

Original from:

Http://www.mssqltips.com/sqlservertip/2727/removing-function-calls-for-better-performance-in-sql-server/?utm_ source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=2012726

Problem:

Most people know not to call a function in the WHERE clause, which can affect your performance. But what if I use it in select? This article will try to remove function calls in select to dramatically enhance performance, especially when returning large amounts of data.

Solution: Sample Tables and Functions:

In this example, we will create two sample tables and two functions to access the tables. As for the fill table , you will have to use a tool , as Visual Studio to populate them to provide some reasonable real data. In this example, the data is populated for each table . One thing to note is that these sample functions only return a single record for each buyer. When there are multiple buyers.

Here's the code:

--Table Creationlogic

CREATE Table[dbo]. [Carsale] (

[Carsaleid] [INT] IDENTITY (*) is not NULL,

[Purchasedate] [smalldatetime] Not NULL,

CONSTRAINT [Pk_carsale] PRIMARY keyclustered ([Carsaleid] ASC)

);

CREATE Table[dbo]. [Buyer] (

[Buyerid] [INT] IDENTITY (*) is not NULL,

[Carsaleid] [INT] Not NULL,

[LastName] [varchar] () NULL,

[FirstName] [varchar] (+) NULL,

[CompanyName] [varchar] (+) NULL,

CONSTRAINT [Pk_buyer] PRIMARY KEY nonclustered ([Buyerid] ASC)

);

ALTER Table[dbo].  [Buyer] With CHECK ADD Constraint[fk_buyer_carsale] FOREIGN KEY ([Carsaleid])

REFERENCES[DBO]. [Carsale] ([Carsaleid]) On UPDATE CASCADE on DELETE CASCADE;

CREATE Clusteredindex [ix_buyer_carsalelid] on [dbo]. [Buyer] ([Carsaleid] ASC);

--Function Creationlogic

CREATE Function[dbo]. [Fngetbuyerfirstname]

(@CarSaleID INT)

RETURNS VARCHAR (500)

As

BEGIN

RETURN (SELECT Top 1FirstName

From Buyer

WHERE [email protected]

ORDER by Buyerid)

END

GO

CREATE Function[dbo]. [Fngetbuyerlastname]

(@CarSaleID INT)

RETURNS VARCHAR (500)

As

BEGIN

RETURN (SELECT Top 1coalesce (lastname,companyname)

From Buyer

WHERE [email protected]

ORDER by Buyerid)

END

GO

Original query:

SELECT cs. Purchasedate,

Dbo.fngetbuyerfirstname (CS. Carsaleid),

Dbo.fngetbuyerlastname (CS. Carsaleid)

From Carsale CS

ORDER by Carsaleid;

As you can see from the above code, each record calls a function. And the buyer table was queried two times. This approach is not efficient when the Carsale table has a large amount of data. The implementation plan is as follows:

Even though we use the WHERE clause to limit the query and query only one piece of data, by looking at the execution plan, as you can see, you still have to do two searches on the buyer table.

Modified query:

SELECT cs. Purchasedate,

Dbo.fngetbuyerfirstname (CS. Carsaleid),

Dbo.fngetbuyerlastname (CS. Carsaleid)

From Carsale CS

WHERE carsaleid=5

ORDER by Carsaleid;

It is worth noting that in this example, only one record is returned. A query with a wider where condition to return more data is becoming slower.

Examples of removal functions:

Now remove the function call in select and use the table Association to achieve the same result, one of which is the use of the WHERE clause, and the other with no restrictions:

SELECT cs. Purchasedate,firstname,lastname

On CS. Carsaleid=m2. Carsaleid

INNER JOIN Buyer m on M2. Carsaleid=cs. Carsaleid and M2. Singlebuyerid=m.buyerid

ORDER by CS. Carsaleid;

SELECT cs. Purchasedate,firstname,lastname

On CS. Carsaleid=m2. Carsaleid

INNER JOIN Buyer m on M2. Carsaleid=cs. Carsaleid and M2. Singlebuyerid=m.buyerid

WHERE cs. Carsaleid=5

ORDER by CS. Carsaleid;

By looking at the execution plan, you can conclude that no functions are needed, and that each record is no longer required to be re-searched. This is handled by the merge join.

To confirm this, let's take a look at the query that just removed the function, and how much performance improvement can be achieved by tracking the SQL Profiler:

Query	WHERE Clause	CPU (MS)	Reads	Writes	Duration
Original	NO	10734	1239655	0	25879
	YES	0	9	0	0
No Function Call	NO	578	16337	0	2457
	YES	0	11	0	0

From the above results, it can be seen that when the result is very large, it can get considerable benefits, including CPU, logic reading, duration and so on. Performance is better when only one result is returned.

Final version, using CTE:

Because in this example, a function is used to return a separate buyer, a CTE can be used to achieve further performance:

FirstName,

LastName,

On S.carsaleid=cs. Carsaleid WHERE s.rk = 1;

FirstName,

LastName,

On S.carsaleid=cs. Carsaleid WHERE S.RK = 1 and cs. carsaleid=5;

Through the execution plan and the SQLProfiler comparison get:

266

Query	WHERE Clause	CPU (ms)	Reads	writes	Duration
no Function call Add with statement	no	15796	0	0	0

Summarize:

I agree that the first approach is easy to implement and easy to read, but performance gains are more important than code volume for performance improvement.

Removal of function calls can have better performance