Optimizing Linq 2 – Linq vs. Stored Procedures

This is a follow on to my previous post comparing various methods of structuring LINQ queries and comparing their performance. In this post, I’m comparing the LINQ results from that post with the performance of a stored procedure call. To make the stored procedure call I’m using the ‘Code First Stored Procedures’ code developed in my earlier posts.

The stored procedure I’m calling will perform the exact same search as the LINQ calls compared earlier and uses the SQL structure introduced in the first LINQ query to handle ‘null or match’ options for each parameter in one SQL query.

CREATE PROCEDURE dbo.Search
	@person varchar(35),
	@permit varchar(14),
	@addrno varchar(6),
	@prefix varchar(2),
	@street varchar(35),
	@unit varchar(20),
	@comm varchar(6),
	@apn varchar(30)
AS
	-- surround inputs with '%' so that we get a 'fuzzy' match 
	-- using like for our comparisons
	select @person = '%'+@person+'%',
		@apn = '%'+@apn+'%',
		@permit = '%'+@permit+'%',
		@addrno = '%'+@addrno+'%',
		@prefix = '%'+@prefix+'%',
		@street = '%'+@street+'%',
		@unit = '%'+@unit+'%',
		@comm = '%'+@comm+'%'


	-- search for any data matching the inputs
	select * from dbo.vw_PermitLookupSearch_2
	where ((@permit is null) or (permit like @permit)) and
		((@person is null) or (person like @person)) and
		((@apn is null) or (apn like @apn)) and
		((@addrno is null) or (addrno like @addrno)) and
		((@prefix is null) or (prefix like @prefix)) and
		((@street is null) or (street like @street)) and
		((@comm is null) or (comm like @comm))
GO

I also repeated the same methodology to determine the performance: Capturing the generated SQL calls in SQL Profiler, and then repeating the call and capturing Client Statistics in SQL Server Management Studio with the cache cleared of any earlier results.

Here we can see the results of calling our search as a stored proc:

Client processing time 2
Total execution time 307

What’s more interesting is when you repeat the query and eliminate the overhead of compiling stored procedure and saving the query plan the results then become:

Client processing time 2
Total execution time 86

Compare this to the results from our previous post and you have the following:

Structured LINQ: 1141
‘Fractured’ LINQ: 862
Stored Proc: 307
Stored Proc w/ cached query plan: 86

The final result is that, on the database, the stored procedure call runs 10 times as fast as the better of the LINQ queries in this example. This is why your DBAs want you to use Stored Procedures.

Read Full Post »