Re: SQL Server taking ages to compile a Stored Procedure

From: Darren (ozy_osborne_at_hotmail.com)
Date: 10/13/04 

Date: 13 Oct 2004 01:35:28 -0700

"Brian Moran" <brian@solidqualitylearning.com> wrote in message news:<Ovr4oCJsEHA.332@TK2MSFTNGP14.phx.gbl>...
> just wondering... what happens if you run the 'procedure' as a batch from
> Query Analyzer? Do you still see the excessive compile time?
>
> --
>
> Brian Moran
> Principal Mentor
> Solid Quality Learning
> SQL Server MVP
> http://www.solidqualitylearning.com
>
>
> "Darren" <ozy_osborne@hotmail.com> wrote in message
> news:877b0de.0410120951.5d781fef@posting.google.com...
> > We have over 2000 stored procedures. Only one is causing a problem.
> > Its probably one of the more complicated ones we have (BUT NOT THE
> > MOST COMPLICATED!).
> >
> > The problem is that it takes 2 minutes to compile. (At least that is
> > what I think it is doing!). When we execute it for the first time, it
> > alway takes this extra 2 minutes to execute. If we create it using the
> > WITH RECOMPILE statement, then every time it gets executed it takes an
> > extra two minutes!
> >
> > Once 'compilied' the sproc executes in around 8 seconds.
> >
> > I'm no stranger to complex queries, but I've never experienced a delay
> > of this magnitude on first execution before. Has anyone else ever seen
> > it??
> >
> > Thanks in advance

Query Analyser or front end - results are the same.

Relevant Pages