yerai
asked on
it's faster delete or update
i have a process that read select * from table and after delete this records every 30 seconds,
my question is beter practice to do update every 30 seconds and every 1 hour or 1 day delete all record marked in each select, because this process it's very hard in this moment in the DB.
Regards
Gerid
my question is beter practice to do update every 30 seconds and every 1 hour or 1 day delete all record marked in each select, because this process it's very hard in this moment in the DB.
Regards
Gerid
Update on a single fixed-width column is faster than delete on an indexed table.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You asked this question earlier, same suggestion applies here and with the same questions from me.
Are you processing the records when you read them? Or are you reading them only for the purposes of deleting them?
Also note, that if you leave the data in the table and merely update it, that means you have more data to sift through for all of your other processes too. Which may mean recreating all of your existing indexes (if any) to include your delete-flag as a way to filter out the data you would have deleted.
Are you processing the records when you read them? Or are you reading them only for the purposes of deleting them?
Also note, that if you leave the data in the table and merely update it, that means you have more data to sift through for all of your other processes too. Which may mean recreating all of your existing indexes (if any) to include your delete-flag as a way to filter out the data you would have deleted.
Like last time, if you're not doing any processing of the records, then update or delete as a single statement.
Something else to consider... How and why is the table getting loaded? Would it be feasible to declare the table a global temporary and load and processes the data for whatever purposes it has and then simply let the data disappear when the session/transaction ends?
Something else to consider... How and why is the table getting loaded? Would it be feasible to declare the table a global temporary and load and processes the data for whatever purposes it has and then simply let the data disappear when the session/transaction ends?
ASKER
i can't temporary table because the program that insert in this table it's not the same than read and delete records for this table.
regards
Gerid Garcia
regards
Gerid Garcia
ASKER
i can't do it truncate because if this moment than you read other process can do insert
ASKER
when i read for the select take information and lnmediatly i shoud delete the same records
gerid
gerid
"take information" you mean you do something with it?
Or you read records and then delete them?
If the latter, don't read and delete. Just delete.
If the former, what's your code look like?
Or you read records and then delete them?
If the latter, don't read and delete. Just delete.
If the former, what's your code look like?
ASKER
sdtuber:
i have a client in java where your purpose is read the records from any table take information and inmediatly delete the records read, i can't truncate because o cuold occur than in this time other user can insert in the same table
i wait than you uderstand my problem
i have a client in java where your purpose is read the records from any table take information and inmediatly delete the records read, i can't truncate because o cuold occur than in this time other user can insert in the same table
i wait than you uderstand my problem
what do you mean by "take information"
are you doing something with the data after you read it?
does your code looke something like this...
select * from table
loop through above data
delete from table where column = loop data
end loop
or does your code look like this...
select * from table
loop through above data
1 - process the loop data somehow
2 - delete from table where column = loop data
end loop
or maybe something like this...
select * from table
loop through above data
process the loop data somehow
end loop
delete from table
I don't understand what you mean when you say "take information"
please provide some code or psuedo-code that demonstrates what you are doing
are you doing something with the data after you read it?
does your code looke something like this...
select * from table
loop through above data
delete from table where column = loop data
end loop
or does your code look like this...
select * from table
loop through above data
1 - process the loop data somehow
2 - delete from table where column = loop data
end loop
or maybe something like this...
select * from table
loop through above data
process the loop data somehow
end loop
delete from table
I don't understand what you mean when you say "take information"
please provide some code or psuedo-code that demonstrates what you are doing
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Forced accept.
Computer101
Community Support Moderator
Computer101
Community Support Moderator
What exactly is being done here?
If you are sure that you dont need the data any more; then truncate the table instead of delete.
Know that:
truncate is :
-- faster
-- doesnt generate rollback
-- and you CANNOT rollback the transaction.