What is difference between these 2 SP

Question

What is difference between these 2 SP

MotivateMan1394

Hall of Fame

Points: 3876
More actions
December 19, 2015 at 11:01 pm

#399490

Hi
I have this 2 SP . There is some difference between them. I need a better Analyse about them.
For example : One of them cant be profile in (PRC:Complete) more over about their exec plan. and ... .
Create PROCEDURE [dbo].[USP_1] @Par1 NVARCHAR(10)
AS
BEGIN
SELECT TOP 20 *
FROM Tab1 with(ReadUncommitted)
WHERE (F2 LIKE '%' + @Par1 + '%' )
END
Go
And this :
Create PROCEDURE [dbo].[USP_2] @Par1 NVARCHAR(10)
AS
BEGIN
Declare @sql nVarchar(2000)
Set @sql=Null
Set @sql =
'SELECT TOP 20 * '+
'FROM Tab1 with(ReadUncommitted) '+
'WHERE (F2 LIKE '+''''+'%' + @Par1 + '%'+''''+' )'
Exec (@sql)
END

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jason A. Long SSC-Insane Points: 23786 More actions · Answer 1

MotivateMan1394 (12/19/2015)
Hi
I have this 2 SP . There is some difference between them. I need a better Analyse about them.
For example : One of them cant be profile in (PRC:Complete) more over about their exec plan. and ... .
Create PROCEDURE [dbo].[USP_1] @Par1 NVARCHAR(10)
AS
BEGIN
SELECT TOP 20 *
FROM Tab1 with(ReadUncommitted)
WHERE (F2 LIKE '%' + @Par1 + '%' )
END
Go
And this :
Create PROCEDURE [dbo].[USP_2] @Par1 NVARCHAR(10)
AS
BEGIN
Declare @sql nVarchar(2000)
Set @sql=Null
Set @sql =
'SELECT TOP 20 * '+
'FROM Tab1 with(ReadUncommitted) '+
'WHERE (F2 LIKE '+''''+'%' + @Par1 + '%'+''''+' )'
Exec (@sql)
END

Assuming that there is an index on Tab1 with F2 in the first key position... The first proc is going to trick the optimizer into thinking that it can do an index seek when it really needs to be doing an index scan.

Basically, the optimizer doesn't know the the '%' is a leading wild card until after it's created the plan...

The second proc will allow the optimizer to correctly choose to do a scan.

That may sound like a good thing but the reality is that if there is a leading wild card in the predicate, a scan is FAR more efficient than a seek.

Add OPTION(RECOMPILE) to the first piece of code and it'll correct itself and produce the correct plan.

MotivateMan1394 Hall of Fame Points: 3876 More actions · Answer 2

Thank you Jason

And what about 'Parameter sniffing', some one say me about that ? what is it ?

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 3

MotivateMan1394 (12/20/2015)
Thank you Jason
And what about 'Parameter sniffing', some one say me about that ? what is it ?

Worse than parameter sniffing and much more importantly, the second method is subject to SQL Injection. Admittedly, you can't do much in an NVARCHAR(10) but you should still delouse the contents of the variable if you're going to use that method.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 4

Here's one way that will help with both parameter sniffing and a way to get SQL Server to usually pick the correct execution plan.

CREATE PROCEDURE dbo.USP_1 @Par1 NVARCHAR(10)

AS

DECLARE @pPar1 NVARCHAR(10);

SELECT @pPar1 = '%'+@Par1+'%'; --Written for 2005 and up. Can combine with DECLARE in 2008 and up.

SELECT TOP 20 *

FROM dbo.Tab1

WHERE F2 LIKE @pPar1

;

As a bit of a sidebar...

1. I avoid Hungarian Notation of things like "usp" and "tbl". I find that they're unnecessary and if you ever do something like change a view to a table or vice versa (and it DOES happen), then you're left with either an incorrectly named object or you have to find and rename the object in all code in which it appears.

2. I don't use table hints like NOLOCK or READ UNCOMMITTED unless it's absolutely necessary (normally not the case) and safe (normally not the case).

3. You should really get into the habit of always using 2 part naming for SQL objects. It saves some run time (important for heavy hit procs/functions, etc) and will keep your butt out of the sling if an object of the same name appears in a different schema even if by accident.

4. You really must get into the habit of using semi-colons. As much as I hate them, not using them everywhere has been deprecated for a decade now. You should always use them in all new code and I strongly recommend that you add them whenever you need to modify some old code.

5. Although sometimes useful, I find brackets to be annoying to read past, especially in complicated code. That's really a personal preference, though.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

MotivateMan1394 Hall of Fame Points: 3876 More actions · Answer 5

Thank you Jeff Moden

Both for my obstacle and for your sidebar.

😛

MadAdmin SSChampion Points: 11261 More actions · Answer 6

USP1 should create 1 plan.

USP2 creates millions cos you hard coding the String value into the query. Since it is not simple because of the like %%. If it were simple, then simple aparemterization could result in it making only one plan.

USP2 looks like a place where you could do SQL injection cos you not using sp_ExecuteSQL and passing parameters into it.

Catch-all queries done right [/url]
Gail Shaw's Performance Blog[/url]