Nested cursors

I have something kind of bizarre going on and I'm not sure how to
troubleshoot it, I've put print statements all over in my code to try an fin
d
out where it's breaking but it doesn't seem to be breaking at any specific
point, there is no error message, processing just stops about the time the
machine runs out of memery, I suspect it's something with the number of
cursors I have open at one time. I can remove some of the nested cursors and
the problem goes away so I know it's memory related... Anybody have some
design advice.? I'm just moving/transforming data from a legacy database to
a
new system.. I tried to post my code but it's too big, I know that should be
a clue right there but I'm trying to bail a buddy out on a project that is
way overdue so opting for quick and dirty.. I can email the script, shoot me
an email..
Thanks!!
Danhi,
Let me know all that stuff at vtam13@.terra.es
--
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''s hard to provide information
without seeing the code. location: Alicante (ES)
"Alien2_51" wrote:

> I have something kind of bizarre going on and I'm not sure how to
> troubleshoot it, I've put print statements all over in my code to try an f
ind
> out where it's breaking but it doesn't seem to be breaking at any specific
> point, there is no error message, processing just stops about the time the
> machine runs out of memery, I suspect it's something with the number of
> cursors I have open at one time. I can remove some of the nested cursors a
nd
> the problem goes away so I know it's memory related... Anybody have some
> design advice.? I'm just moving/transforming data from a legacy database t
o a
> new system.. I tried to post my code but it's too big, I know that should
be
> a clue right there but I'm trying to bail a buddy out on a project that is
> way overdue so opting for quick and dirty.. I can email the script, shoot
me
> an email..
> Thanks!!
> Dan
>|||It went away with FAST_FORWARD.. hmm.. Maybe someone can tell why the
READ_ONLY is so much more costly on resources...?
"Enric" wrote:
> hi,
> Let me know all that stuff at vtam13@.terra.es
> --
> Please post DDL, DCL and DML statements as well as any error message in
> order to understand better your request. It''s hard to provide information
> without seeing the code. location: Alicante (ES)
>
> "Alien2_51" wrote:
>|||Fast_forward is a forward Only read only cursor. If you can move forward an
d
backward in a cursor SQL needs to cache the results... If you use
fast_forward, sql only needs to have in memory ( or in tempdb depending on
the other options you choose), the current row you are looking at... Once yo
u
move off of that row, sql can purge it from memory because you are not
allowed to move backward...
Forward-only, read only cursors are called firehose cursors, and are the
fastest of any cursor...
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Alien2_51" wrote:
> It went away with FAST_FORWARD.. hmm.. Maybe someone can tell why the
> READ_ONLY is so much more costly on resources...?
> "Enric" wrote:
>|||Thanks Wayne!!
Dan
"Wayne Snyder" wrote:
> Fast_forward is a forward Only read only cursor. If you can move forward
and
> backward in a cursor SQL needs to cache the results... If you use
> fast_forward, sql only needs to have in memory ( or in tempdb depending on
> the other options you choose), the current row you are looking at... Once
you
> move off of that row, sql can purge it from memory because you are not
> allowed to move backward...
> Forward-only, read only cursors are called firehose cursors, and are the
> fastest of any cursor...
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "Alien2_51" wrote:
>|||On Tue, 28 Mar 2006 02:26:02 -0800, Alien2_51 wrote:

>I have something kind of bizarre going on and I'm not sure how to
>troubleshoot it, I've put print statements all over in my code to try an fi
nd
>out where it's breaking but it doesn't seem to be breaking at any specific
>point, there is no error message, processing just stops about the time the
>machine runs out of memery, I suspect it's something with the number of
>cursors I have open at one time. I can remove some of the nested cursors an
d
>the problem goes away so I know it's memory related... Anybody have some
>design advice.?
Hi Dan,
Triggers whould be used VERY sparingly in SQL Server. Nested triggers
are a big red waving flag screaming "bad design!! bad design!!"
You should consider rewriting your stored procedure to use set-based
logic only - or at least to reduce the number of triggers.
If you need help with that, you'll really have to post your existing
code. And not only that - we need table structure (as CREATE TABLE
statements), sample data (as INSERT statements) and expected results as
well!

> I tried to post my code but it's too big
Eh? I've posted some prettly lengthy posts here and I've never gotten
any errors on this. Just make sure NOT to post it as a file atachment
(they get filtered out by namy usenet providers); just paste the
complete CREATE PROCEDURE statement in your post.
Of course - the longer the code you post, the harder it is to comment;
we are after all all doing this in our spare time. But if you do post, I
will definitely look at it - that much I promise.
Hugo Kornelis, SQL Server MVP|||Thanks Hugo... I'm not using the cursors in a trigger.. I have a huge
procedural script that transforms data from one data schema to another.. I
have to nest my cursors to preserve all of my data relationships.. Some of
the relationships I'm trying to preserve are four and five levels deep..
~Dan
"Hugo Kornelis" wrote:

> On Tue, 28 Mar 2006 02:26:02 -0800, Alien2_51 wrote:
>
> Hi Dan,
> Triggers whould be used VERY sparingly in SQL Server. Nested triggers
> are a big red waving flag screaming "bad design!! bad design!!"
> You should consider rewriting your stored procedure to use set-based
> logic only - or at least to reduce the number of triggers.
> If you need help with that, you'll really have to post your existing
> code. And not only that - we need table structure (as CREATE TABLE
> statements), sample data (as INSERT statements) and expected results as
> well!
>
> Eh? I've posted some prettly lengthy posts here and I've never gotten
> any errors on this. Just make sure NOT to post it as a file atachment
> (they get filtered out by namy usenet providers); just paste the
> complete CREATE PROCEDURE statement in your post.
> Of course - the longer the code you post, the harder it is to comment;
> we are after all all doing this in our spare time. But if you do post, I
> will definitely look at it - that much I promise.
> --
> Hugo Kornelis, SQL Server MVP
>|||On Tue, 28 Mar 2006 12:12:02 -0800, Alien2_51 wrote:

>Thanks Hugo... I'm not using the cursors in a trigger.. I have a huge
>procedural script that transforms data from one data schema to another.. I
>have to nest my cursors to preserve all of my data relationships.. Some of
>the relationships I'm trying to preserve are four and five levels deep..
Hi Dan,
I made a stupid typo. Where I wrote
I actually intended to write
*Cursors* whould be used VERY sparingly in SQL Server. Nested *cursors*
are a big red waving flag screaming "bad design!! bad design!!"
In most cases you don't need cursors at all. Neither for transforming
data, nor for preserving relationships.
That being said - if this is a one-time conversion job and it works now,
leave it as is!
Hugo Kornelis, SQL Server MVP|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
Yep! My rule of thumb is that any SQL statement over 10 lines is
suspect. But the real problem is that you used cursors at all! Nested
cursrors scream total incompedency, and old mag tape processing
algorithms.
No good deed goes unpunished! But if it is sooooo bad that you cannot
post to a Newsgroup, then he needs to pay someone to do his job for
him.
Based on 20+ years of fixing this stuff, I would start over with the
spec and do the job from scratch. What I am afraid of is that the
schema is also designed as if it were a mag tape file and thsu cannot
be saved, and probably has already destroyed data integrity.|||>> I have to nest my cursors to preserve all of my data relationships.. Some
of
the relationships I'm trying to preserve are four and five levels
deep.. <<
First, have you looked at ETL tools to do this job?
Again without DDL or code, we can only guess at what you are doing.
But when I have moved data from a "scrub table" to a normalized schema,
I never use cursors. I have found that something this is easier to
write:
BEGIN
..
INSERT INTO HighestReferencedLevel (...)
SELECT ..
FROM ScrubTable
WHERE ..;
INSERT INTO NextHighestReferencedLevel (...)
SELECT ..
FROM ScrubTable
WHERE ..;
etc.
END;
The real problem is what to do about the dirty data that cannot be
moved, like an order for an item that is not in Inventory.
I might have to hit the ScrubTable a few times in the transaction, but
it is still faster than cursors. You might also look at the MERGE
statement in SQL:2003 that other SQL products have.