Multipurpose Stored Procedures.

Still under impression of Kimberly L. Tripp session "Dealing with Multipurpose Procs and PSP the RIGHT Way!" on PASS Summit 2014 in Seattle.

It was wonderful session with a simple idea of how to make Multipurpose Stored Procedures work faster. I did a research couple of years ago on similar topic, but I did not come up with such a wonderful idea.

At first, will explain what it is a Multipurpose Stored Procedure.

Look from a developer's perspective: There is a business need to have a search for a client and it has to be universal search by any client's attribute. There can be entered anything: Part of first or last name, part of email address, phone number, account number, part of a street address. There even more intelligent requirement to search not just by one, but also by two, three or more parameters.

How would you implement that?

It would be nice to have a stored procedure which would be called in a way like in example below:

There are several different ways to implement that logic. The most straight way looks like this:

That approach is the easiest one and it is the least optimal.
That might easily happen that generated plan for that query will be horrible for the most of passing to that stored procedure values.

In order to prevent generation of the bad plan you can build the stored procedure with RECOMPILE option, but you also can build a query for an execution within the stored procedure dynamically. That will recompile only that single query, not the whole procedure:

That code with recompilation will work better because there will be no "bad" query generated.
However, that solution still can hit the scalability ceiling. Each run of that stored procedure will still cause plan recompilation and high CPU usage.

Kimberly's idea was that we do not have to recompile ALL plans. For queries where we expect return of single row and which will use an index we can leave the plan in cache.

I think that is a brilliant idea. It will save recompilation CPU time for all requests, which will do index seek and return one or few items.

In my example, these kind of plans will be produced in case we supply @Account, @Order or @Phone variables.

So, here is the changed second part of my stored procedure to demonstrate that idea:

In case of @Account, @Order or @Phone variables "OPTION (RECOMPILE)" won't be added to a query and that query will stay in cache for future usage.

Thanks Kimberly for great idea.