Follow Up to Maintaining Security and Performance Using Stored Procedures Part I – Using Execute As

This is a follow up to Maintaining Security and Performance using Stored Procedures Part I – Using EXECUTE AS because of a comment on that post on the SQLServerCentral syndicated feed by Ken Lee.  He commented:

Like the technique, not the example. ISNULL() is a function, it forces the where clause to look at every record in the table.
SQL is smart enough to identify constants and know the variable name is true or not and will or will not evaluate the second OR statement used below.
First SET @LastName=@LastName+N'%', if it's null it remains null. To make sure it always works declare a nvarchar(51) field and assign it instead. If the field in the table is max 50 characters then this isn't needed.
Replace the function in the first example with "(@LastName IS NULL OR LastName Like @LastName) And" logic and you should get the performance without the dynamic SQL.
Kind of curious about the stats.

Since the purpose of that post was to show how to get better performance using Dynamic SQL without compromising security, I decided I needed to do some testing on his method.  Here are the queries I ran (FREEPROCCACHE was run because the Dynamic SQL query plans were not removed from the cache when the stored procedure was altered):

DBCC FREEPROCCACHE();
GO

Exec dbo.FindPhoneByName @FirstName ='J',@LastName ='A';

GO

Exec dbo.FindPhoneByName @FirstName ='J';

GO

Exec dbo.FindPhoneByName @LastName ='A';

Go

I don’t know why, but his method DOES generate a different execution plan than my method using ISNULL() when you leave the Person.Contact in, what I believe is, the original state.  In the original state the optimizer chooses to do a scan of the index, IX_Contact_MiddleName, and bookmark lookup on the clustered index.  Here’s the index definition:

CREATE NONCLUSTERED INDEX[IX_Contact_MiddleName] ON[Person].[Contact]
(
[MiddleName] ASC
)
INCLUDE ([FirstName],
[LastName])

While the optimizer chose a clustered index scan for Ken’s query.  This access path required more reads when both parameters were provided, but fewer when only one was provided. 

The Dynamic SQL, however, provided the best of both, using the index when both parameters were provided and using the clustered index when only one parameter was provided.

Intrigued I decided to see what happened if a covering index was made available, so I created this index:

CREATE NONCLUSTERED INDEX[IX_Contact_LastName_FirstName] ON[Person].[Contact]
(
[LastName] ASC,
[FirstName] ASC,
[MiddleName] ASC
)
INCLUDE ([Title],
[Suffix],
[Phone])

With a covering index in place both non-dynamic solutions produced the same execution plan, scans of the newly created covering index.  The Dynamic SQL though had seeks on the covering index for queries 1 and 3, and a scan for query 2.

Lastly I decided to see what happened when there was a non-covering index available, here’s that index:

CREATE NONCLUSTERED INDEX [IX_Contact_LastName_FirstName] ON [Person].[Contact] 
(
[LastName] ASC,
[FirstName] ASC,
[MiddleName] ASC
)

This case was similar to the first test, my solution did a scan on the index with a bookmark lookup, and Ken’s solution did a clustered index scan.  The dynamic SQL did an index seek/bookmark lookup when both parameters were provided and a clustered index scan when only one parameter was provided.





Here’s the results from statistics IO:

		IsNull() Solution		Ken’s Solution		Dynamic
		Scans	Reads	Scans	Reads	Scans	Reads
Query 1	Original	1	593	1	1116	1	593
	Covered	1	201	1	201	1	13
	Uncovered	1	457	1	1116	1	359
Query 2	Original	1	7783	1	1116	1	1116
	Covered	1	201	1	201	1	201
	Uncovered	1	7647	1	1116	1	1116
Query 3	Original	1	3031	1	1116	1	1116
	Covered	1	201	1	201	1	13
	Uncovered	1	2895	1	1116	1	1116

Conclusion

 

As you can see from the results above Ken’s solution does provide more consistent results than my original solution, while the Dynamic SQL still provides the best performance.  I should note that there is a tradeoff with the dynamic SQL solution, you get better plans because you get a plan for each option that is run, so depending on the number of parameter combinations you could get procedure cache bloat.  In most 64-bit implementations this shouldn’t be an issue, but you may see more compiles with a dynamic solution. As always test your options, monitor, and change as needed to keep your systems running as well possible.

 

All the files (except the AdventureWorks database, on CodePlex) can be found here. Included are the queries, the results from statistics IO, and a trace file showing the activity and query plans.

 

Part II