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
Showing posts with label legacy. Show all posts
Showing posts with label legacy. Show all posts
Wednesday, March 28, 2012
Friday, March 9, 2012
INSERT rows while SELECT returning large volumes of data against same table
I'm taking over some legacy coding and am checking to see if there may be a better approach to some sql code I've inherited.
Any input would be appreciated.
Here was the problem that the implemented code was to address:
Given a high volume (10 to 50 thousand inserts daily) transaction table, it was observed that when executing a stored procedure that executed a
SELECT against the table while inserts were being attempted, there was considerable delay before the inserts were committed. It turns out the SELECT query was taking 100% CPU. Because of the business needs of the environment, the inserts were required to be committed in a very timely fashion.
Present Solution:
The currently implemented workaround implements a cursor that after so may reads will execute
the WAITFOR command in order to yield the CPU. This is all done within a stored procedure where there are actually seven
sections that parse through data tables in order to populate the temp table.
The below is just a example of the currently implemented code that invokes the waitfor command.
DECLARE @.a1 [datetime]
DECLARE @.b1 [int]
DECLARE @.c1 [int]
DECLARE @.rowCount [int]
DECLARE @.sleepRowCount [int]
DECLARE @.waitForDelay varchar(20)
DECLARE @.parameterDate1 smalldatetime
DECLARE @.parameterDate2 smallDateTime
SET @.parameterDate1 = '1/1/2006'
SET @.parameterDate2 = '1/31/2006'
SET @.sleepRowCount = 10000
SET @.waitForDelay = '0:0:0.500'
CREATE TABLE [#tempTable] (
a smalldatetime,
b int,
c int
)
DECLARE tableCursor CURSOR FOR
SELECT my.somedate, my.someId, count(*)
FROM myTable my with (nolock, index=IX_somedate)
WHERE my.somedate BETWEEN @.parameterDate1 AND @.parameterDate2
GROUP BY my.somedate, my.someId
OPEN tableCursor
FETCH NEXT FROM tableCursor INTO @.a1, @.b1, @.c1
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.rowCount = @.rowCount + 1
INSERT INTO #tempTable VALUES( @.a1, @.b1, @.c1 )
FETCH next FROM tableCursor INTO @.a1, @.b1, @.c1
-- Yield CPU
IF (@.rowCount > @.sleepRowCount)
BEGIN
WAITFOR delay @.waitForDelay
SET @.rowCount=0
END
END
CLOSE tableCursor
DEALLOCATE tableCursor
Zymore:
What is the composition of the "IX_somedate" index? Does this index contain both (1) someDate and (2) someId?
|||Dave
If you are directly inserting into the table without any modifications to the values retrieved you could directly dump the data into the temp table.
INSERT INTO #temptable
SELECT my.somedate, my.someId, count(*)
FROM myTable my with (nolock, index=IX_somedate)
WHERE my.somedate BETWEEN @.parameterDate1 AND @.parameterDate2
GROUP BY my.somedate, my.someId
Mugambo
The sql code listed is just a rough example to give the reader an idea of the methodology that has been implemented to overcome CPU hogging. In the actual code, the date and id would be columns in a composite index that also contains additional columns.
Could high CPU use point to inefficient index usage from a query?
|||Zymore:
Really, I think I would also implement pretty much the way Dinakar has laid it out. What I was puzzling over was the index hint -- wandering why the code needed to resort to an index hint. If you have a cover index -- and from your response it seems that you do -- then the index hint should not be necessary. My worry when I saw your example was that you were going to get bombarded with hundreds of thousands of bookmark lookups; and these could grieve you.
However, if you are getting your data out of a cover index and are not incurring any bookmark lookups then your insert and select should be very efficient. It would be a good idea to get an execution plan of the query you outlined and present it. Also, try Dinakar's query and get an execution for that particular query. Getting such execution plans would greatly help discussion. I will also put together a mock-up so that we can try to compare notes.
|||
Dave
Zymore:
I mocked up your table with about 98K records ranging in date from 1/1/2006 - 1/3/2006 and sprayed these records over 32K unique "someID" keys. I then ran your query and also ran Dinakar's query. I used two different nonclustered indexes for testing purposes; one version had only the "someDate" field and the other had both the "someDate" and the "someId" field. I ran the mock-up on my 4-way SQL Server 2000 machine. The information that I recorded included:
The particular index arrangement used|||
Mugambo wrote:
I left out something: After running the mockup I feel that Dinakar's query held up well. I would try Dinakar's query WITHOUT the INDEX HINT. And please make sure you thank Dinakar for his help.
Well, Mugambo has done all the (dirty) work. So credit goes to Mugambo.
Subscribe to:
Posts (Atom)