why is this proc so slow?

[soni2926]

Hi,
I have the below proc, it seems to be running very slow ever since i
setup encryption on one of the columns. running it takes almost 2
mins, but if i run the same script outside of the proc, just in query
analyzer it completes in 13 sec, i just don't get why the proc takes
longer as i thought proc would be faster since it's compiled. i'm
using sql sever 2005:

CREATE PROCEDURE [dbo].[usp_clint_GetBldgList]
@biweeklyDate char(10)
AS
BEGIN

Open SYMMETRIC KEY SymIDKey DECRYPTION BY CERTIFICATE certBlgdKey

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT a.ClientID, a.qnumber, a.qin, first_name + ' ' + a.last_name
as 'name', a.startdate, (CASE isdate(startdate) WHEN 1 THEN
CONVERT(VARCHAR(10), CONVERT(datetime, startdate, 101), 101) ELSE
startdate END) as 'formattedStartDate', a.BiweeklyPeriod,
CONVERT(VARCHAR(10), a.BiweeklyPeriod, 101) as
'formattedBiweeklyPeriod', RIGHT(CONVERT(VARCHAR(9),
(DecryptByKey(enkey))), 4) as 'key',
a.hours, a.days, b.Idnumber, b.code
FROM Client a left join History b on a.ClientId = b.ClientId
WHERE a.BiweeklyPeriod = @biweeklyDate
AND a.startdate = @biweeklyDate
ORDER BY a.BiWeeklyPeriod desc, a.last_name, a.first_name

Close SYMMETRIC KEY SymIDKey

any ideas why the same code would take so much longer while in a proc
over just running it directly? running it directly i'm still using
encryption as the query is exactly the same, just copied and pasted
it, and it runs much faster outside of the proc.

Thanks.

[Erland Sommarskog]

soni2926 (soni...@yahoo.com) writes:
> I have the below proc, it seems to be running very slow ever since i
> setup encryption on one of the columns. running it takes almost 2
> mins, but if i run the same script outside of the proc, just in query
> analyzer it completes in 13 sec, i just don't get why the proc takes
> longer as i thought proc would be faster since it's compiled. i'm
> using sql sever 2005:

When you run it outside the proc, how do you provide the parameter value?
Do you just fill in a constant? Or do you assign a variable?

Whichever you do, it will be a different situation from when you run
the procedure. If you have a constant, SQL Serves more exactly what is
going and can build the plan for that. If you have a variable, it has
less clue, and makes some standard assumption based on the density
of the columns, I believe. For a procedure, it sniffs the input values
when the plan is first compliler, which means that if you ran it with
funky parameter it have produced a plan which is not good for what you
have now.

This case is easy to test. Run the procedure in a query window and add
WITH RECOMPILE. If this gives better performance, it is a sniffing
issue.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[--CELKO--]

Parameter sniffing is the usual cause for this kind of problem. but
why are you formatting columns for display in the procedure and not
the front end? Do you want to prevent indexes from being used or do
you hate the idea of a tiered architecture? This is SQL and not COBOL
where data and front are monolithic. In the long term, I would get rid
of the vague data element names and re-think your coding approach.

[soni2926]

Thank you both for the response. I looked into it some more, and looks
like someone had deleted an index on the Client table, putting that
back got the data to be returned faster. thank you for the suggestion
on the columns, they actually want us to display data on a grid both
in an formatted and unformatted manner. it comes in via a feed
unformatted, client wants to see it both ways, not my choice :) i
intially did think of just putting the formatting on the front end,
but the same proc is also used in a crystal report that's being
developed by another person, so i just sent them the data formatted,
rather than having them also format it. but we can always change that
and get them to format it on their end as well :)

Thanks!

[--CELKO--]

>> it comes in via a feed unformatted, client wants to see it both ways, not my choice :)  I intially did think of just putting the formatting on the front end,

but the same proc is also used in a crystal report that's being

developed by another person, so I just sent them the data formatted,

rather than having them also format it. but we can always change that
and get them to format it on their end as well :) <<

Be careful. When the same data floats around in multiple formats,
strange things can happen. You would be surprised how long you can run
with a mix of UK and US traditional date formats, concatenated full
name formats, etc. from data feeds before thing fall apart. I
obviously have a story .. :(