why is this proc so slow?

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.
0
soni2926
9/8/2010 6:34:17 PM
sqlserver.programming 1873 articles. 0 followers. Follow

4 Replies
999 Views

Similar Articles

[PageSpeed] 13

soni2926 (soni2926@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, esquel@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

0
Erland
9/8/2010 10:14:36 PM
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.
0
CELKO
9/9/2010 8:09:22 PM
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!
0
soni2926
9/9/2010 8:18:58 PM
>> it comes in via a feed unformatted, client wants to see it both ways, no=
t my choice :) =A0I intially did think of just putting the formatting on th=
e 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 .. :(
0
CELKO
9/9/2010 8:30:25 PM
Reply:

Similar Artilces:

Slow performance on closing Appointment
When a user closes an Appointment that has several User Attendees, the initiating user gets a time-out after about 5 minutes, while at the same time the system becomes quite slow. However, the Appointment is closed and after a while the performance returns to it's prevoius performance level. Performance is OK for other CRM and non-CRM activities. Any idea what causes the temporary performance degradation and how to improve it? System Specs single 3 MHz P4 server with 2GB RAM and raid-1. MS SBS 2003 Premium (latest SP's installed except SQL SP4) 7 users are running Sales for Outlo...

error message while opening the stored proc.
Can anybody view the stored procedure code - installed by eConnect on GPS database (Stored procs created after you install eConnect e.g. taSopHdrIvcInsert) I get error message while opening the stored proc. Error 20585: [SQL-DMO]/*******Encrypted object is not transferable, and script can not be generaqted. ******/ any comments ? ...

XML From Stored Proc
In VB.NET (Studio 2005) I need to loop thru a result set returned from a stored proc and generate XML for each row. E.g. exex procWhatever returns: custno, lname, fname, address 2, Doe, John, 123 4th St. 3, Smith, Bill, 567 8th St. 4, Grant, Terry, 333 Wherever St. For each on of those rows I need to generate (string): <Customer> <custno>2</custno> <lname>Doe</lname> <fname>John</fname> <address>123 4th st.</address> </customer> .... Any advice, tutorials, etc. would be greatly appreciated. Thank you very much. What dat...

Recievable transaction inquiry slow
Hi All, When I click inquiry(receivables transaction inquiry), it runs too slowly. Only this inquiry, others work fine. It happened from last week. My Sql server had crashed before this happened. I thought something wrong with the table rm00401, i reindex this table. But can not fix it. Who knows how to solve this problem? Thanks Nancy Are you running SQL 2000 or SQL 2005? When you re indexed did you drop and re-create or just use the dbreindex command? Drop and re-create is the best method for rebuilding an index completely but it's best to do this when users are log out and it...

Excel 2003 open slow only with domain accounts
If i logon locally on my PC all works fine. If i logon in my pc with user a domain account excel (only it!, word, access and others work fine)takes 1-2 min (splash screen) before open (i set it to open no documents on startup but is the same). I try to delete temporary files and to set up a default printer...nothing. Domain is 2003 (only 1 domain,1 tree, 1 forest) client is an XP (sp1 installed) Any idea? ...

Execute SQL Stored Proc from Access
I am working with an Access 2003 mdb which is connected via an ODBC DSN to a SQL 2005 database. I need to run a SQL stored proc from Access. This stored proc is not any kind of query; it's a stored proc that runs several other SPs in the SQL database. I have tried various methods, but keep getting different errors. One of the methods I tried is: sSQL = "EXEC sp_SprocName @SPvar = " & fvVar() DoCmd.RunSQL sSQL This method works fine in an Access Project, but this is not a project, but an mdb. I get the error 3129 Invalid SQL statement; e...

a real stumper
I have an issue with an excel worksheet I've created. WorkbookA has "Names" which are used to define drop-down menus in WorksheetA (so my users don't accidentally mistype a response). These "Names" are defined to reference cells in WorksheetB (also in WorkbookA) which itself is populated by a database query, pulling from another Excel WorkbookB. The logic is that WorkbookB should not be touched by the User, only an Admin, and Workbook A is used by User. The Names in WorkbookA are to prevent incorrect entries. Now that I've set it up, WorkbookA is moving rea...

slow emails
Hi there, I've got a strange problem. We are running Exchange 2000 on a Win 2000 server and ISA 2004 on a Win 2003 server. I can send emails out ok but when I try and receive them they seem to take around a day to come to my Inbox. Can someone give me some pointers on when I could look for a solution? Thanks, Steve. This happens from multiple external domains? Check Message Tracking, STMP logs and message header. -- Bharat Suneja MCSE, MCT www.zenprise.com blog: www.suneja.com/blog ----------------------------------------- "Steve" <Steve@discussions.microsoft.com>...

Slow Performing Outlook 2007 and BCM
Hi all, I just recently upgraded a number of PCs from OL2003 (connecting to Exchange - not cached mode) to Outlook 2007 w/ Business Contact Manager (oviously cached mode as this is required). The BCM database is on a shared PC using the BCM database tool. I had experimented with one PC taking it off the cached mode and it had made things speedy again, but I can't keep it like that due to the need for BCM. Any clue what is going on here? Is it cached mode or BCM causing the issue? The speed issues range from starting an email reply and having to wait for what you type to catch up,...

Stored Proc to Call Same stored proc
We are working with an application, where we can implement our custom logic using "Pre" stored procedures available thru the application. In other words, if for instance, when system creates a customer calling proc CreateCustomers, and before creating a customer, it would execute CreateCustomerPre sproc - and if you want it to check for something and based on the result, create a customer - this can be addressed using these "Pre" sproc. We are using a stored proc that enters a line item on an order. We would like to see if the line item is a bill of material item ...

Visio is extremely slow to respond (mouse seems to stick)
This only happens in Visio 2003 Pro; while authoring a document, objects placed on the page do not respond to click commands (such as double-click to enter text) and the objects get stuck in drag-mode after a single click. Overall Visio performance is extremely poor... Sometimes it can take 10 to 20 seconds for the application to execute the actions I'm trying to perform. It's certainly not the hardware platform, my system is far-beyond minimum requirements - no other applications stall like this. I'm not certain why this is happening. I've reinstalled Visio but it doesn&...

Best method to call a long stored proc with WCF
Say have a stored procedure that takes about 10min to 1 hour to run and I need to call it with WCF (silverlight). I'd basically want to fire off the query, have the wcf service fire off the query in a thread or something. Then have the wcf return to the silverlight program and say 'Ok the SP is running' The silverlight app would then make some wcf calls that would count the rows in table x, to see what the progress is. Or the SP could update another table with the status. Could the WCF service just call a thread return 'OK running', and still have the SP runn...

extremely slow message download
What might cause an extremely slow message download in Outlook 2003 using a POP account? If I look at send/receive details, it seems to be hung on "processing". The message that's causing the problem is about 300kb, but it's not the total time to download that's the issue, it's the RATE of download which is almost zero. As far as I can tell there is no issue with the person's internet bandwidth. Any help would be appreciated. ...

Reprinting Posting Journals is Very Slow
GP9: I am reprinting a posting journal. The receivables batch only has three transactions in it, but the reprint is now in excess of 8 minutes and still no report. I keep checking in Process Monitor and the Status only once in while blips to Active before going blank again. Any thoughts on why this is so slow and how to fix it? -- Elisabeth ...

xl2007
BACKGROUND: I developed an interactive Excel-based program for the engineering and construction industries using xl2003. It is essentially a picture management system. Developement time was roughly 100 hours. My latest project involves exactly 840 pictures. I have been avoiding upgrading to xl2007 like the plague. PROBLEM: The client has xl2007 and there were some compatibility issues. So I installed the xl2007 trial version and fixed these issues. However, what I can't fix is the extremely slow macro execution time. For example, I remapped the arrow keys to move the...

Ending a stored proc
I am trying to end a stored procedure and return a value of -1 to tell the application that a check number already exists. Below is the code in my sp where I am checking but it goes right past it and continues as though the result is false. When I run this as a separate query it returns the -1 fine. Can anyone see what might be wrong? Thanks. DECLARE @Return int; IF @Duplicate = 1 AND EXISTS (SELECT dbo.PayChecks.CheckNumber FROM dbo.PayChecks INNER JOIN dbo.PeopleLink ON dbo.PayChecks.PeopleLinkID = dbo.PeopleLink.PeopleLinkID WHERE (dbo.P...

Initial connection to Outlook SLOW!
When I first open Outlook and enter my username/password it takes approximately 5 minutes to connect and update my Inbox. I leave it running in the taskbar all day and subsequent updates are fine. Furthermore, if I connect from home I do not have this problem. Only when I'm at work. We recently switched ISP's from SBC DSL to T1 through a private ISP. Any thoughts? It's not a huge problem, but I'd like to fix it if it's a simple task. what type of email account do you have? is there a firewall in place? how many people are using the T1? -- Diane Poremsky [MVP - Ou...

Macro for OnKey: Can the proc name a VB6 proc?
Hi, I want to capture the user pressing "Enter" in excel. If I want to do so in VB6, like an Excel AddIn project, instead of in VBA, and want a proc in my VB6 project to be called instead of a macro sub in the VBA, what can I do? I know that the OnKey works, but I can only assign it with a VBA sub as the second parameter of OnKey("~", procName). Please help! And thanks. Regards, David Chan I think you'd have to have OnKey call a VBA procedure which would then call your VB6 proc. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC ...

rebuild reorg proc
Hello all, SQL server 2005 Std edition X64 SP3 CU6 running on Server 2003 Ent Edition SP2 32 GB RAM 2 X quad core procs. AM using a commonly available Stored Procedure to rebuild\reorg indexes based on criterion. Unsure where the original came from but I got this copy from the Web. Please consider this credit to the original writer and modifiers. I have made some slight changes to include materialized views. My problem is on a relatively busy server the procedure will exit in the middle of the execution with sometimes 10 databases done sometimes 40, sometimes 5, etc. O...

Slow Response When Clicking on " New " Button
When I go to create a new email using the " New" button located right under the " file " tab and proceed to click on it..there is a long delay before it actually opens up the window for me to compose the email...it spins for about 3 seconds..give or take.Please help. ...

Synchronizing offline folders is sometimes very slow
My company recently moved to Exchange 5.5 (all latest patches) and Outlook for all clients (98, 2000, 2002, 2003). We will be moving to Exchange 2003 within the next year but are stuck with 5.5 for the time being. We have about a dozen laptop users for whom we have set up offline folders, which generally are working fine. However, I have one client in particular (Outlook 2002 sp2, Windows 2000 sp4) who is having synchronization problems. It sync's fine sometimes but other times it will start syncing and take an hour or more(and transfer 50MB) when it appears nothing significan...

Outlook VERY SLOW when opening messages
In my company LAN there is an Exchange 2000 server withsome Windows2000 Pro and Outlook 2000 clients. Some of them have an issue with extreme slow e-mail messages opening. The message preview is perfect, really fast; when they try to open the message double clicking on it it lasts 3-5 seconds before the message window opens. Outlook is even slow when opening the program, about 2-3 seconds while in other clients it takes just 1 second. How can I solve this issue (and don't have my pals dont' complaying anymore about it )? Please help me! Mauro We have a similar problem where I wor...

Using OutputDebugString too much can cause slowness in GUI response
FYI, I have been having problems with a ListView with about 100 items, and when I try to scroll vertically or horizontally by dragging the scroll bar, it doesn't catch up immediately, but it looks highlighted and frozen, and catches up later. It turned out the reason for it was that I was using OutputDebugString too much, printing the text for each item and sumitem(There are 8 columns in this list view), and DebugView was open to view the output. When I close DebugView, the problem is gone. I was using OutputDebugString at a rate of 1000/Second. The items also appeared ...

Outlook is starting up very slow
Hello What's wrong with my Outlook ? It take salmost 2 minutes to open and to see my e-mail And i even do not automatically get check for new mail I just wait 2 mins and then hit the send/receive button That also takes maybe 2 to 3 minutes to get all my e-mails in Maybe my inbox is too big ? There are maybe 900 emails in it The oldest email in it is almost one year old. How can i make my inbox smaller and still have fast access to these e-mails ? Another issue: I have 19 e-mail accounts. Does that influence the speed ? Version of OL? The size of the pst is? (Properties) in mb ...

socket extremely slow
I've done a simple winapp program that sends a message from a client to a server and back. However, when starting the server everything seems to happen extremely slow on the server (if it happens at all). E g when starting up my server there's supposed to come up a "Waiting for connections..." message, but it doesn't and the dialog doesn't seems to respond to any actions. Even though it seems almost deadlocked, when connecting the client, the client successfully sends its message and also successfully gets a message back. Then, after a while on the server, t...