Newbie Q's on mass delete processing

GWood · *nix forums beginner Joined: 19 Apr 2005 Posts: 14

I am a developer with lots of Oracle experience, but currently I am tasked
with coding some extensive delete processing on a complex Sybase server. My
code will be touching several dozen databases there, performing DML based on
certain key field comparisons in tables. The server is sybase 11.9.2.

I've poked around enough in the data dictionary tables to assemble a list of
the tables and their key columns for processing, and we've set up the server
to allow for "dynamic" sql, so I can construct the necessary DML while
looping through a "driver" table.

Several of these tables will have many thousands of rows to be processed. I
am concerned about Sybase's transaction log tables filling and killing the
processing. In the Oracle world I would use loops with bulk
collection/ForAll constructs to efficiently process chunks of say, 10K rows
at a time.

Q - Is there a preferred threshold of loading the log files before doing a
commit? What's a safe limit? I can glean an approximate row size for each
table being processed, and a good guesstimate of the rowcount too. Can I
just do the math and say I can process as many rows as will fit in NN% of
the transaction log files?

Q - Is there a trick to efficient high volume processing in the Sybase
world. In 11.9.2, I don't see many tricks/tips for chunking through many
thousands of rows. Is there anything better than "Delete where Exists
(select * from set_of_candate_rows where....) " ?

Any suggestions appreciated.
Gary

Rob Verschoor · *nix forums addict Joined: 19 May 2005 Posts: 75

The thing to avoid is your transaction log getting full. How many deletes it
will take to achieve that depends mostly on the size of your log...
Now, this type of heavy delete job is indeed best done by running in a loop
and deleting individual rows (or small groups of them) at a time, so you're
doing small transactions. Then, truncate or dump the log regularly to stop
it from filling up.
This approach also has the advantage of not blocking access to the table in
the mean time.

If you're deleting many rows, it _could_ be faster to do a select..into
instead, copying only the rows you want to keep. Since select-into is
minimally logged, it can be faster than deleting the rows you don't want to
keep. Everything depends on your specific numbers though, so you'll have to
benchmark it first.

HTH,

Rob V.
-------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 12.5/12.0/11.5/11.0
and Replication Server 12.5 / TeamSybase

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks & Recipes for Sybase ASE"
"The Complete Sybase Replication Server Quick Reference Guide"
"The Complete Sybase ASE Quick Reference Guide"

mailto:rob@YOUR.SPAM.sypron.nl.NOT.FOR.ME
http://www.sypron.nl
Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
-------------------------------------------------------------

"GWood" <sorry@nothere.com> wrote in message
news:rC74g.7153$kg.3012@news02.roc.ny...

GWood · *nix forums beginner Joined: 19 Apr 2005 Posts: 14

Thanks Rob

I have set things up much as suggested, as so far I am testing using
"neutral" Update statements to preserve my test data. No glitches so far.
How coincidental you answered this posting. I am using some of your code to
implement dynamic SQL in our 11.9.2 environment !

As a matter of fact, you could save my life with another answer question. I
am using the dynamic sql approach of "faking out" a remote server from your
site (http://www.sypron.nl/dynsqlcis.html ) . What I am looking for is a
way to capture the @@rowcount value when the passed sql string actually gets
executed. I need to pass this back for logging and eventual comaprison
purposes.

The sp_exec_dynsql procedure from your site calls sp_remotesql. (For some
reason I could only see the first part of the sp_remotesql proc in our
instance, I asked a dba for the remainder). From his response, the part of
sp_remotesql that does the work is the call:
EXEC sp_procxmode 'dbo.sp_remotesql','anymode'.

I have tried adding a couple of print statements to sp_procxmode to
determine where the @@rowcount value might be available, but it doesn't seem
to recognize the version I created. In other words, if I impement the test
copy I made, I see the print statements but the SQL never executes. If I
use the original version, I see output like "NN rows affected" (showing my
sql has executed), but no print statements. I have seriously limited DBA
resources to bounce this question off.

Any suggestions you could offer would be greatly appreciated.

Gary

"Rob Verschoor" <rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY> wrote in
message news:2d9ff$4454b6bb$d55d70a1$28389@news.chello.nl...

Rob Verschoor · *nix forums addict Joined: 19 May 2005 Posts: 75

Not sure if my SQL can save lives, but it's worth a try...

There are ways to return @@rowcount through sp_remotesql, but they require
various types of hacks.
First, use you can use raiserror to return the rowcount through @@error, but
this is a bit dirty -- see below.

1> sp_remotesql loopback , 'select * from sysdatabases declare @rc int
select @rc = @@rowcount + 20000 select @rc raiserror @rc "xyz" '
2> go
1> select @@error - 20000 "rowcount
2> go

Second, you can use a proxy table mapped to a stored proc, and return the
the rowcount as the last row or something. You'll have to experiment a
bit...

HTH,

Rob V.

"GWood" <sorry@nothere.com> wrote in message
news:QZs6g.7908$kg.590@news02.roc.ny...

Google