Performance prblm with SP

Hi all,
My one SP takes around 30 second to execute. When I
execute the same SP from another SP and put the output
into a #table it takes more than 10 minutes. I m doing
something like this
create proc sp2
begin
create table #t ( ... )
insert into #t execute sp1
.
.
.
end
Tempdb shows numerous locks on sysobjects, syscolumns,
sysindexes and OAM pages.
Can anybody please suggest the cause and remedy for this?
I am running SQL 2000 on Win2K.
Thanks in advance
Best regards
HimanshuHi
Have you checked output from Profiler?
recomplile ,cache missing ?
"Himanshu" <himanshu@.ocwen.co.in> wrote in message
news:252a001c38d90$6998bb50$a601280a@.phx.gbl...
> Hi all,
> My one SP takes around 30 second to execute. When I
> execute the same SP from another SP and put the output
> into a #table it takes more than 10 minutes. I m doing
> something like this
> create proc sp2
> begin
> create table #t ( ... )
> insert into #t execute sp1
> .
> .
> .
> end
> Tempdb shows numerous locks on sysobjects, syscolumns,
> sysindexes and OAM pages.
> Can anybody please suggest the cause and remedy for this?
> I am running SQL 2000 on Win2K.
> Thanks in advance
> Best regards
> Himanshu|||Hiya,
Yes I checked profiler output by running SP many times. It
showed numerous locks acquired on
tempdb..sysobjects/sysccolumns/sysindexes table. And
frustratingly out of 10 run 3 times it runs very fast,
less than 15 seconds.
Any more places to peep?
thanks v much.
>--Original Message--
>Hi
>Have you checked output from Profiler?
>recomplile ,cache missing ?
>
>"Himanshu" <himanshu@.ocwen.co.in> wrote in message
>news:252a001c38d90$6998bb50$a601280a@.phx.gbl...
>> Hi all,
>> My one SP takes around 30 second to execute. When I
>> execute the same SP from another SP and put the output
>> into a #table it takes more than 10 minutes. I m doing
>> something like this
>> create proc sp2
>> begin
>> create table #t ( ... )
>> insert into #t execute sp1
>> .
>> .
>> .
>> end
>> Tempdb shows numerous locks on sysobjects, syscolumns,
>> sysindexes and OAM pages.
>> Can anybody please suggest the cause and remedy for
this?
>> I am running SQL 2000 on Win2K.
>> Thanks in advance
>> Best regards
>> Himanshu
>
>.
>