|
Home > Archive > Sybase Database > March 2006 > Update withou transaction log?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Update withou transaction log?
|
|
| jaroslav.povolny@gmail.com 2006-03-14, 9:23 am |
| Hello All,
is there a possibility to make Sybase update WITHOUT writing to
transaction log?
I have to update very large table (where I know there is only one
user).
I am filling the log always, but I do not want to increase the size of
the log,
as it is huge already.
Is there any solution to "turn off" transaction log for some updates,
or even databases?
Thank you
Jaroslav
| |
| Rob Verschoor 2006-03-14, 9:23 am |
| No, this is not possible. Have you tried to split up the update in smaller
transaction, for example by setting 'set rowcount 10000'? (assuming the
update somehow knows how to continue where it left off)? Otherwise you could
use a cursor and commit every X rows.
It may also be useful to check the rules for index usage for direct/deferred
updates, since this can make a big difference for the amount of log space
used (see the ASE Performance & Tuning Guide for details).
HTH,
Rob
-------------------------------------------------------------
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
-------------------------------------------------------------
<jaroslav.povolny@gmail.com> wrote in message
news:1142342719.478635.316360@e56g2000cwe.googlegroups.com...
> Hello All,
>
> is there a possibility to make Sybase update WITHOUT writing to
> transaction log?
> I have to update very large table (where I know there is only one
> user).
> I am filling the log always, but I do not want to increase the size of
> the log,
> as it is huge already.
>
> Is there any solution to "turn off" transaction log for some updates,
> or even databases?
>
> Thank you
>
> Jaroslav
>
| |
| jaroslav.povolny@gmail.com 2006-03-14, 9:23 am |
| Thank you for answer...
Now I have to find the best way how to do the update.
Do you have any ideas for this - this is very common task...
I have two tables
mydb..mytab ( id int, name varchar)
proddb..names (id int, name varchar, row_stat int)
I have ~2.5mil. of selected ids in mytab, table names is bigger (7 mil.
of ids)
what I do is:
update mytab
set name=n.name
from mytab m, names n
where m.id=n.id
Can you recommend the best way to do it - I can change the structure of
mytab in any way (i.e. adding columns, indexes etc) but I can not
change names
table.
I am thinking about extending my table of "false" column updated,
and do (pseudo code):
set rowcount 100000
go
declare @rc int
select @rc=100000
while (@rc=100000)
begin
update mytab
set name=n.name, updated=1
from mytab m, names n
where m.id=n.id
and n.updated is null
select @rc=@@rowcount
dump transaction mydb with no_log
end
What do you think about this idea? Is there any better, more effective
and clean solution
for this very common problem?
Thanks a lot
| |
| Jochen Schug 2006-03-14, 1:23 pm |
| On Tue, 14 Mar 2006 15:05:49 +0100, jaroslav.povolny@gmail.com
<jaroslav.povolny@gmail.com> wrote:
> Now I have to find the best way how to do the update.
> Do you have any ideas for this - this is very common task...
> I have two tables
>
> mydb..mytab ( id int, name varchar)
> proddb..names (id int, name varchar, row_stat int)
>
> I have ~2.5mil. of selected ids in mytab, table names is bigger (7 mil.
> of ids)
>
> what I do is:
>
> update mytab
> set name=n.name
> from mytab m, names n
> where m.id=n.id
How about select...into?
select n.id,n.name
into mytab2
from mytab m, names n
where m.id = n.id
Under the assumption that you can use the new table mytab2 instead of
mytab, and that you can restore any permissions, indexes etc. after you've
created it?
> Is there any better, more effective and clean solution for thisvery
> common problem?
Add more log space.
Jochen
| |
| ZeldorBlat 2006-03-14, 8:23 pm |
|
jaroslav.povolny@gmail.com wrote:
> Thank you for answer...
>
> Now I have to find the best way how to do the update.
> Do you have any ideas for this - this is very common task...
> I have two tables
>
> mydb..mytab ( id int, name varchar)
> proddb..names (id int, name varchar, row_stat int)
>
> I have ~2.5mil. of selected ids in mytab, table names is bigger (7 mil.
> of ids)
>
> what I do is:
>
> update mytab
> set name=n.name
> from mytab m, names n
> where m.id=n.id
> and n.updated is null
> select @rc=@@rowcount
> dump transaction mydb with no_log
> end
>
> What do you think about this idea? Is there any better, more effective
> and clean solution
> for this very common problem?
>
> Thanks a lot
Close. I didn't test it, but unless you have an exact multiple of
100000 rows in the table you'll miss some. What you've got is
basically how I do it. Here's mine:
set rowcount 100000
while (1 = 1)
begin
begin tran
update mytab
set name=n.name, updated=1
from mytab m, names n
where m.id=n.id
and n.updated is null
if @@rowcount = 0 begin
commit tran
break
end
commit tran
end
begin/commit tran.
| |
| ZeldorBlat 2006-03-14, 8:23 pm |
|
jaroslav.povolny@gmail.com wrote:
> Thank you for answer...
>
> Now I have to find the best way how to do the update.
> Do you have any ideas for this - this is very common task...
> I have two tables
>
> mydb..mytab ( id int, name varchar)
> proddb..names (id int, name varchar, row_stat int)
>
> I have ~2.5mil. of selected ids in mytab, table names is bigger (7 mil.
> of ids)
>
> what I do is:
>
> update mytab
> set name=n.name
> from mytab m, names n
> where m.id=n.id
> and n.updated is null
> select @rc=@@rowcount
> dump transaction mydb with no_log
> end
>
> What do you think about this idea? Is there any better, more effective
> and clean solution
> for this very common problem?
>
> Thanks a lot
Close. I didn't test it, but unless you have an exact multiple of
100000 rows in the table you'll miss some. What you've got is
basically how I do it. Here's mine:
set rowcount 100000
while (1 = 1)
begin
begin tran
update mytab
set name=n.name, updated=1
from mytab m, names n
where m.id=n.id
and n.updated is null
if @@rowcount = 0 begin
commit tran
break
end
commit tran
end
Additionally, I don't use the dump tran that way. Instead, I wrap the
update statement with begin/commit tran.
| |
| ZeldorBlat 2006-03-14, 8:23 pm |
|
jaroslav.povolny@gmail.com wrote:
> Thank you for answer...
>
> Now I have to find the best way how to do the update.
> Do you have any ideas for this - this is very common task...
> I have two tables
>
> mydb..mytab ( id int, name varchar)
> proddb..names (id int, name varchar, row_stat int)
>
> I have ~2.5mil. of selected ids in mytab, table names is bigger (7 mil.
> of ids)
>
> what I do is:
>
> update mytab
> set name=n.name
> from mytab m, names n
> where m.id=n.id
> and n.updated is null
> select @rc=@@rowcount
> dump transaction mydb with no_log
> end
>
> What do you think about this idea? Is there any better, more effective
> and clean solution
> for this very common problem?
>
> Thanks a lot
Close. I didn't test it, but unless you have an exact multiple of
100000 rows in the table you'll miss some. What you've got is
basically how I do it. Here's mine:
set rowcount 100000
while (1 = 1)
begin
begin tran
update mytab
set name=n.name, updated=1
from mytab m, names n
where m.id=n.id
and n.updated is null
if @@rowcount = 0 begin
commit tran
break
end
commit tran
end
| |
| Mark A. Parsons 2006-03-14, 8:23 pm |
| declare @count int
select @count = 100000 -- whatever won't
-- blow out your log
set rowcount @counter
while 1=1
begin
update mytab
set m.name = n.name
from mytab m,
names n
where m.id = n.id
and m.name != n.name
if @@rowcount < @count
break
else
dump transaction <dbname> ....
end
set rowcount 0
---------------------------------
I'm assuming you have, at a minimum, an index on names.name.
The update will only update those records where mytab.name != names.name.
If @counter records are updated then you probably have more records that
need to be updated, so loop through again.
If fewer than @counter records are updated then you're done (ie, there are
no more records to be updated).
jaroslav.povolny@gmail.com wrote:
> Thank you for answer...
>
> Now I have to find the best way how to do the update.
> Do you have any ideas for this - this is very common task...
> I have two tables
>
> mydb..mytab ( id int, name varchar)
> proddb..names (id int, name varchar, row_stat int)
>
> I have ~2.5mil. of selected ids in mytab, table names is bigger (7 mil.
> of ids)
>
> what I do is:
>
> update mytab
> set name=n.name
> from mytab m, names n
> where m.id=n.id
>
> Can you recommend the best way to do it - I can change the structure of
> mytab in any way (i.e. adding columns, indexes etc) but I can not
> change names
> table.
>
> I am thinking about extending my table of "false" column updated,
> and do (pseudo code):
>
> set rowcount 100000
> go
> declare @rc int
> select @rc=100000
> while (@rc=100000)
> begin
> update mytab
> set name=n.name, updated=1
> from mytab m, names n
> where m.id=n.id
> and n.updated is null
> select @rc=@@rowcount
> dump transaction mydb with no_log
> end
>
> What do you think about this idea? Is there any better, more effective
> and clean solution
> for this very common problem?
>
> Thanks a lot
>
| |
| Mark A. Parsons 2006-03-15, 3:23 am |
| Fixed a couple typo's ...
Mark A. Parsons wrote:
[color=darkred]
> declare @count int
>
> select @count = 100000 -- whatever won't
> -- blow out your log
>
> set rowcount @count
>
> while 1=1
> begin
> update mytab
> set m.name = n.name
> from mytab m,
> names n
> where m.id = n.id
> and m.name != n.name
>
> if @@rowcount < @count
> break
> else
> dump transaction <dbname> ....
> end
>
> set rowcount 0
>
> ---------------------------------
>
> I'm assuming you have, at a minimum, an index on names.name.
>
> The update will only update those records where mytab.name != names.name.
>
> If @counter records are updated then you probably have more records that
> need to be updated, so loop through again.
>
> If fewer than @count records are updated then you're done (ie, there
> are no more records to be updated).
>
>
>
> jaroslav.povolny@gmail.com wrote:
>
| |
| jaroslav.povolny@gmail.com 2006-03-15, 3:23 am |
| Thank you all for your tips, I will choose one of them...
|
|
|
|
|