Posted by Inna on 11/18/07 21:01

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

>
> Pinning tables is very rarely if ever a good idea, and this feature
> has been nullified in SQL 2005.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
Hello,
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.

Thank you very much.

[Back to original message]