SP vs isolated code.

Question

SP vs isolated code.

Barry G Freeman

SSC Eights!

Points: 942
More actions
May 18, 2010 at 2:56 am

#221591

Ok, I have this stored procedure
ALTER PROCEDURE [dbo].[TSL_CheckEmailAlreadyRegistered]
-- The email address which will be checked
@Email nvarchar(256),
@SubscriberId int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @Registered bit
SET @Registered = 0
IF @SubscriberId IS NULL
IF
NOT EXISTS(
SELECT Email AS Email
FROM TSL_User_Profile
WHERE Email = @Email
UNION ALL
SELECT m.Email AS Email
FROM aspnet_Membership m
WHERE m.Email = @Email
)
SET @Registered = 0
ELSE
SET @Registered = 1
ELSE
IF
NOT EXISTS(
SELECT Email AS Email
FROM TSL_User_Profile
WHERE Email = @Email AND SubscriberId != @SubscriberId
UNION ALL
SELECT m.Email AS Email
FROM aspnet_Membership m
INNER JOIN TSL_User_Profile up
ON up.UserId = m.UserId
WHERE m.Email = @Email AND up.SubscriberId != @SubscriberId
)
SET @Registered = 0
ELSE
SET @Registered = 1
SELECT Registered = @Registered
END
If I supply BOTH parameters, the SP takes 27 seconds to run (!).
If I take ALl the code out of the SP and run it manually, again, supplying both parameters, it executes in less than 1 second.
I cannot explain to the dev, why the SP runs so slowly while running the in ad-hoc mode runs very quickly.
Help?
DBA (Dogsbody with Bad Attitude)

Viewing 4 posts - 1 through 3 (of 3 total)

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

Stefan_G SSCertifiable Points: 6609 More actions · Answer 1

Most likely because of parameter sniffing.

When compiling a stored procedure the SQL server creates an execution plan that is optimal for the parameters specified when the procedure was first run.

It then reuses this plan every time the procedure is called.

Try adding WITH RECOMPILE to the procedure, or a RECOMPILE hint to the problematic query.

/SG

Arjun Sivadasan SSCertifiable Points: 5902 More actions · Answer 2

A couple of good resources on parameter sniffing -

http://elegantcode.com/2008/05/17/sql-parameter-sniffing-and-what-to-do-about-it/

http://blogs.msdn.com/queryoptteam/archive/2006/03/31/565991.aspx

- arjun

https://sqlroadie.com/

Barry G Freeman SSC Eights! Points: 942 More actions · Answer 3

Thanks, all..

Solved the problem. I guess this is because of the conditional logic.. with anything more complex, I'd split it into caller and two separate procs.

Cheers, again.. got a happy dev now.

DBA (Dogsbody with Bad Attitude)