Strange behaviour when changing SP name

Question

Strange behaviour when changing SP name

marty.seed

SSCrazy

Points: 2661
More actions
February 16, 2023 at 7:43 pm

#4150738

Hey all,
I have never seen anything like this before. I wrote a report that uses a stored procedure named martySPImportExport as a test. When I was confident that the report was performing well I decided to change the name of the SP to something more company friendly, SP_REV_IMPORTEXPORT_DEPARTMENT.
When I changed the sp name and re-uploaded the report it went from taking around 30 secs to run on the server to close to 10 minutes.
Has anyone seen this before?! The stored procedures are the EXACT same aside from the name.
CONUNDRUM!!!!

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

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

Gerckens.3 SSC Rookie Points: 42 More actions · Answer 1

Is it possible that your 30 second time was based on a cached execution plan on the database server or cached rdl data in SSDT? Depending on whether you changed the name of the SP or dropped and recreated it could have influenced the former. That's what comes to mind off the top of my head. My bet is that its not the change in name spelling, but the change in environment or something else related. That's all I got...

"When it comes to report design and development, I have a list of pet peeves a mile wide. You might think the list would be a mile long, but I like to turn it on its side, sharpen the end, and poke people with it." - Me

Ed B Hall of Fame Points: 3669 More actions · Answer 2

I doubt this is the cause, but is is advised not to name procedures with the sp_ prefix. This is reserved for system stored procedures. I have read there is a measurable performance hit, but it's a small percentage, it doesn't make things run ten times as slowly. I would still ditch the sp_ prefix.

Did you rename the existing procedure or drop it and compile it with the new name? I've experienced some odd things when deploying by renaming objects, so I don't do it anymore. This is probably not relevant, but possibly interesting.

Once the procedure is recompiled wth a new name I would test it from SQL before trying again from the report. Does the report pass in parameters? You may have got a really bad execution plan when you changed the name, but it's worth testing in sql to see if you can recreate the slow execution.

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

It's a fairly common occurrence. Please see the following article.

https://www.sommarskog.se/query-plan-mysteries.html

And I totally agree with Ed B's recommendation about the use of sp_ and I'll extend that to the general use of Hungarian Notation in SQL Server.

--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)

marty.seed SSCrazy Points: 2661 More actions · Answer 4

Thanks all for the input, all very interesting and will definitely stay away from naming with the sp_prefix. I did recompile the new sp with a drop/create. Both Sp's run in about the exact amount of time in SSMS, it is only the report that slows when using the SP_ version.