|
|
Posted by Inna on 11/20/07 14:54
On Nov 18, 6:00 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> Inna (med...@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, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx- Hide quoted text -
>
> - Show quoted text -
The stored procedure in all databases optimised well, all fields are
indexed. So I was thinking since this is development server , we don't
rebuild indexes and if we go on production maybe it will be better...
There is another trick I was thinking about, which I am not sure is
good and possible to do, maybe I should create a job,
which checks if the execution plan is there (this I am not sure I can
do) and if not, execute this SP in background (I mean P_Main). So I
have a question: First: Is it possible? Second: if it is possible,
would it be a good idea? Third: If First+Second = "YES" , how can I do
it?
Thank you
Navigation:
[Reply to this message]
|