Posted by teddysnips on 02/20/07 10:56

On 19 Feb, 22:35, Gert-Jan Strik <s...@toomuchspamalready.nl> wrote:
> teddysn...@hotmail.com wrote:
>
> > In a system I'm maintaining there is a Stored Procedure called
> > dbo.MyStoredProcedure. I didn't create this - it was created by a
> > developer who has now left. I don't know how the object came by its
> > "dbo." prefix, but I think he created it in QA.
>
> > Anyway, there were some performance issues (it was taking between 4
> > and 10 seconds to complete) so I copied the SQL into a QA window and
> > it consistently ran in under 1 second. So I created a new SPROC with
> > SQL exactly identical to the old one, but without the "dbo." prefix,
> > and that too runs in <1 second.
>
> > Any thoughts?
>
> > Edward
>
> Maybe all the stored procedure needed was a recompilation. Maybe it had
> nothing to do with the dbo prefix.
>
> Please post back if the behavior is consistent (IOW, if performance
> degrades if you change it back to the dbo prefix).

10/10! I dropped the SPROC, then recreated it identically and it ran
like a greyhound. So all it needed was recompilation. I guess
there's an art to knowing how often/in what circumstances to recompile
SPROCs but I'm a developer, not a DBA so I don't know!

Thanks

Edward

[Back to original message]