INSERT...EXEC prevents parallelism?

Hello all. I have a, shall we say, "legacy" stored proc whose result
set I must manipulate in another stored proc. The legacy stored proc
is extremely complex, and the final select for the result set is about
2 pages of UNION statements. When this legacy SP runs in a standalone
fashion, it executes in less than 20 seconds, with the query plan
showing a high degree of parallelism.
The driver calls the legacy proc using INSERT ...EXEC, placing the data
into a pre-defined temp table and doing some simple manipulations to
it. Not complex. But it takes several minutes to run, and the query
plan shows basically the same execution plan for the legacy SP, but
with no parallelism.
What can be done to make the driver perform without the
single-processor bottleneck? I'm happy to provide the SP code, query
plans, etc if desired, but thought there might be a "Doh!" answer. :-)
TIA
CCinnabar (heynele@.cotse.net) writes:
> Hello all. I have a, shall we say, "legacy" stored proc whose result
> set I must manipulate in another stored proc. The legacy stored proc
> is extremely complex, and the final select for the result set is about
> 2 pages of UNION statements. When this legacy SP runs in a standalone
> fashion, it executes in less than 20 seconds, with the query plan
> showing a high degree of parallelism.
> The driver calls the legacy proc using INSERT ...EXEC, placing the data
> into a pre-defined temp table and doing some simple manipulations to
> it. Not complex. But it takes several minutes to run, and the query
> plan shows basically the same execution plan for the legacy SP, but
> with no parallelism.
> What can be done to make the driver perform without the
> single-processor bottleneck? I'm happy to provide the SP code, query
> plans, etc if desired, but thought there might be a "Doh!" answer. :-)
Hm, I trying to recall that whether I have heard of this issue before,
but I am not sure. In any case, when you are insertning data into a table
that can affect the query plan and the choice of parallelism.
I have an article on web site, that discusses INSERT-EXEC and a number
of alternatives to it. Maybe you should try any of the alternatives?
http://www.sommarskog.se/share_data.html.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||INSERT...EXEC causes the stored procedure to run within the context of a
transaction. It may be that parallelism isn't the problem, but rather
running it in a transaction.
Try executing the procedure from within a transaction, i.e.,
BEGIN TRAN
EXEC procName...
COMMIT
and see if things slow down. If they do, then don't use INSERT...EXEC,
instead rewrite the procedure or a copy of the procedure so that the insert
happens within the proc. If you create the temp table before executing the
procedure, it can be populated by the proc and will exist after the proc
returns.
"Cinnabar" <heynele@.cotse.net> wrote in message
news:1136847107.546724.48670@.o13g2000cwo.googlegroups.com...
> Hello all. I have a, shall we say, "legacy" stored proc whose result
> set I must manipulate in another stored proc. The legacy stored proc
> is extremely complex, and the final select for the result set is about
> 2 pages of UNION statements. When this legacy SP runs in a standalone
> fashion, it executes in less than 20 seconds, with the query plan
> showing a high degree of parallelism.
> The driver calls the legacy proc using INSERT ...EXEC, placing the data
> into a pre-defined temp table and doing some simple manipulations to
> it. Not complex. But it takes several minutes to run, and the query
> plan shows basically the same execution plan for the legacy SP, but
> with no parallelism.
> What can be done to make the driver perform without the
> single-processor bottleneck? I'm happy to provide the SP code, query
> plans, etc if desired, but thought there might be a "Doh!" answer. :-)
> TIA
> C
>|||Thanks Erland. Unfortunately, none of the alternatives is really a
possibility.
I knew that insert...exec would cause the called SP to execute within a
transaction (as Brian points out) but that in and of itself shouldn't
prevent parallelism, should it? I will plan to try executing the
legacy SP within a transaction tomorrow, to get proof that is the
issue.
This is a real drag, as my only recourse is to copy the guts of the
legacy SP into mine...which obviates the whole idea of reuse. This
legacy SP contains complex data business rules that are essentially
undocumented (and uncommented in the SP), and are maintained by another
group of developers.
thanks for your and Brian's replies.
C
Erland Sommarskog wrote:
> Cinnabar (heynele@.cotse.net) writes:
> Hm, I trying to recall that whether I have heard of this issue before,
> but I am not sure. In any case, when you are insertning data into a table
> that can affect the query plan and the choice of parallelism.
> I have an article on web site, that discusses INSERT-EXEC and a number
> of alternatives to it. Maybe you should try any of the alternatives?
> http://www.sommarskog.se/share_data.html.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Brian, I did the test you suggested and that's exactly it.
Do you know why the explicit transaction could prevent parallelism?
And a related question: I might also have to use this legacy SP as the
source on a data pump DTS task that inserts the result set into a
staging table on another server. Am I likely to run into the same
issue, i.e. poor performance due to lack of parallelism, which in turn
is due to an implicit or explicit transaction?
Thanks!
C
Brian Selzer wrote:
> INSERT...EXEC causes the stored procedure to run within the context of a
> transaction. It may be that parallelism isn't the problem, but rather
> running it in a transaction.
> Try executing the procedure from within a transaction, i.e.,
> BEGIN TRAN
> EXEC procName...
> COMMIT
> and see if things slow down. If they do, then don't use INSERT...EXEC,
> instead rewrite the procedure or a copy of the procedure so that the inser
t
> happens within the proc. If you create the temp table before executing th
e
> procedure, it can be populated by the proc and will exist after the proc
> returns.
> "Cinnabar" <heynele@.cotse.net> wrote in message
> news:1136847107.546724.48670@.o13g2000cwo.googlegroups.com...|||Cinnabar (heynele@.cotse.net) writes:
> Brian, I did the test you suggested and that's exactly it.
> Do you know why the explicit transaction could prevent parallelism?
Very interesting.
But there is something which smells fishy to me. I find it bit difficult
to believe that a transaction scope in general would prevent parallelism.
I mean, in such case I should have heard of it before. Or at least I think
so.
It could of course be something particular particular with this procedure,
although I have no idea of what that could be.
Out of the blue, you are not running with a transactiun isolation
level of REPEATABLE READ or SERIALIZABLE?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I didn't think that the explicit transaction would prevent parallelism. I
suggested on the other hand that the problem may not be parallelism at all,
but the increased lock duration and overhead caused by executing the entire
procedure within the context of a transaction.
It sounds like the problem is the procedure itself. Consider that it may
cost less in time, resources, and performance to fix it. If this is a
procedure that must remain in production, then that should carry more weight
in your decision process. I'm usually hold the opinion: if it ain't broke,
don't fix it! But if the performance is unmanagable, then in this case it's
clearly broken.
"Cinnabar" <heynele@.cotse.net> wrote in message
news:1136924932.520579.29290@.f14g2000cwb.googlegroups.com...
> Brian, I did the test you suggested and that's exactly it.
> Do you know why the explicit transaction could prevent parallelism?
> And a related question: I might also have to use this legacy SP as the
> source on a data pump DTS task that inserts the result set into a
> staging table on another server. Am I likely to run into the same
> issue, i.e. poor performance due to lack of parallelism, which in turn
> is due to an implicit or explicit transaction?
> Thanks!
> C
> Brian Selzer wrote:
>|||Brian, I wish this SP were in my purview, I would certainly rewrite it.
But in a sense it isn't "broken", since it is working fine in its
standalone execution. So those who maintain it will not be eager to
rewrite it to help the performance of my driver proc.
(Erland, neither the driver nor the legacy SP is running with the
isolation levels you asked about.)
Some background I did not mention earlier: my driver SP that filters
the legacy SP's result set is part of a DTS package that moves data
between two servers. (However, this problem is repeatable outside the
context of the DTS package.) One of the alternatives I'm considering
is to modify the package to dump the legacy SP's result set directly
into a staging table on the destination server. Then I'd do the
filtering and transformations at the destination. But I'm not sure
that this design won't result in the same problem; seems like it would
not, since the destination table is on a different server and I can
control the transaction context. I'm banging out a prototype to
determine if this will work.
again, thanks for all your comments!
C