Re: query performance help needed

On Nov 18, 6:00 pm, Erland Sommarskog <esq...@xxxxxxxxxxxxx> wrote:
Inna (med...@xxxxxxxxxxx) 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...@xxxxxxxxxxxxx

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


.

Relevant Pages

  • Re: Order of execution in logical expressions
    ... expressions. ... But this has nothing to do with execution order. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (microsoft.public.sqlserver.tools)
  • Re: TRANSACTIONS in a WHILE LOOP. Flow Question
    ... I obviously do not understand error handling as I should. ... loop below where does the point of execution move to after an error? ... Error handling in SQL Server 2000 is a fairly messy story, ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: query performance help needed
    ... why I guessed that the execution plan is gone. ... executes this sp from 30 databases. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: differnt execution plans for same query
    ... one of these symbol has very thick lines in execution plan. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (microsoft.public.sqlserver.tools)
  • Re: Slow UPDATE and DELETE on SQL Server 2000
    ... I've increased the size of the TempDb database and noticed ... When I ran the update query I got an error message about not being ... I did read the execution plans incorrectly. ... > SQL Server process, memory, as well as the SQL Server specific counters. ...
    (microsoft.public.sqlserver.server)
Loading