Posted by Erland Sommarskog on 11/18/07 23:00

Inna (mednyk@hotmail.com) writes:
>> All and all, there is no reason why P_main should be slow on its own.
>> It reads some fairly small system tables, and runs a cursor over 30 rows.
>>
>> You say that the various P_ALL_DB runs quickly when they run on their
>> own, but there is one difference when you run it from P_Main: the
>> procedure will run in the context of a transaction defined by the
>> INSERT statement. I'm not really sure how that could matter, but then
>> again, I don't know what is in those P_ALL_DB. But there could be
>> blocking issues.
>>
>> One way to test this is to remove the INSERT, and run P_main and see
>> how that affects the execution time.
>
> I removed the INSERT into temp table and it does runs faster and
> actually by a lot. But I need a record set to be returned in one set,
> how can I achieve without using temp table.

How about posting the code of the procedure you call? Not that I'm
sure that it helps, but right now I am completely in the dark of
what is going on.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]