Delete table in single shot!

jemmy · 2005-01-11

Hi folks,

Anyone knows how to delete table if there's existing data in its fields.
The manual we have to delete each field's data.
I require a fast way to accomplish this... please advice.

Thanks in advance as always.

Jemmy

jm · 2005-01-11

Hi,

what about:

DELETEALL
This function is used to delete all records that are selected by the filter settings – if no filters are set, all records in the table will be deleted.

(Page 268 in Application Designer’s Guide, w1w1adg.pdf)

br
Josef Metz

Warfox · 2005-01-11

Yes thats right but this will be betten in performance:

repeat
  Table.delete;
until Table.next = 0;

Luc_VanDyck · 2005-01-11

A DELETEALL instruction will be executed on the server, while a REPEAT ... DELETE ... UNTIL NEXT construction is executed on the client and every record needs to be retrieved from the server and send to the client.

Therefore, DELETEALL is the way the go!

jemmy · 2005-01-11

Josef,
Thanks buddy... that's exactly what I think but I forget where I know it earlier. Yeah, The Bible of Navision comes again.

Warfox,
Thanks!!! you are a great by the experiences. It's good that you know the better way to do these. Any reasons?

Just edited "any reason"
I think Luc has the answers...
Thanks Luc for correcting us to the correct thinkings!!!

Cheers,

Jemmy

jm · 2005-01-11

Hi Luc,

i agree completely with you.

@Warfox:
Why do you think that REPEAT .. UNTIL will betten in performance?

br
Josef Metz

pdj · 2005-01-11

If you wish to delete the entire content of a table, and even DELETEALL is to slow, then there is a dirty alternative... Use at own risk - I have only seen it used with the Native DB - however I think it works in SQL as well...

Object.SETRANGE(Type,Object.Type::TableData);
Object.SETRANGE("Company Name",COMPANYNAME);
Object.SETRANGE(ID,DATABASE::"Customer Price Group"); // This is the table to flush
Object.DELETEALL;

But test it GOOD and use extreme caution before using this in production enviroment. (I have only seen and used it in internal tools) I don't know which permissions it requires...

jemmy · 2005-01-12

A DELETEALL instruction will be executed on the server, while a REPEAT ... DELETE ... UNTIL NEXT construction is executed on the client and every record needs to be retrieved from the server and send to the client.

All,

I have a question based on quoted statement, how about if we do it in the server machine or at single user installation, which one is faster?
Is still DELETEALL command? :-s

Let me know your opinions...

Timo_Lässer · 2005-01-12

I've heared that the REPEAT..UNTIL variant should be faster on a SQL server. For native servers the DELETEALL is the fastest way.

Myself has no experience with this performance.

eromein · 2005-01-13

I don't think we will see difference in performance, even if there is a difference.

Maybe if you have millions and millions of records you could see a little difference. Maybe... (we need to test this).

But still, would this be a reason to make a loop? Type the words REPEAT, UNTIL and record.NEXT = 0 to many just for that bit of performance?

And I think a deleteall is MUCH easier to read that a loop.

So.... let's go for that DELETEALL, let's go fot that Navision standard.

jemmy · 2005-01-14

You are right!!!

In my test:
DELETEALL is faster than DELETE
Navision 4.0
SQL Option
SQL 2000 SP#3
Windows 2000 Adv.Server SP#4
DB Server and client are located in single machine!
Table has 100,000 records, 10 columns, I created a fake table haha..

I haven't tried in the natives, are you curious? Hmm, anyways research is good... let's build the navision knowledge base...

Regards,

Jemmy

IngEolRuin · 2006-09-13

Only problem with the DELETALL is it locks the client.
I personally dislike "long" Application doesnt respond windows.

The idea deleting the tabledata is also nice, but I dont dare to think what happens if THAT transaction crashes ...

themave · 2006-09-13

I bet this is just as fast,

open object designer, click on table, select run

I click on the top corner, select all, press delete

it is faster because I don't have to write a program and code it, (which I don't have the license for anyways)

just kidding with you. I'm sure with a big table it would take a while to select all the records.

ara3n · 2006-09-14

Actually running a table and selecting all and click on delete will run slower if there is code on the triger ondelete. Navision will then delete one record at a time and run the underlying code. Deleteall will not run the underlying code. Deleteall(true) will.

philippegirod · 2006-09-14

And if you use many filters, don't forget to use the right key, it is incredibly faster with the right key...

girish.joshi · 2006-09-16

Warfox's deleteall comment was the first instance of flame bait I've seen on mibuso.

Let me be the first to take it for what it was and say

WTF???????

DenSter · 2006-09-16

flamebait? what are you talking about?

uto · 2006-10-03

pdj wrote:
If you wish to delete the entire content of a table, and even DELETEALL is to slow, then there is a dirty alternative... Use at own risk - I have only seen it used with the Native DB - however I think it works in SQL as well...
Object.SETRANGE(Type,Object.Type::TableData);
Object.SETRANGE("Company Name",COMPANYNAME);
Object.SETRANGE(ID,DATABASE::"Customer Price Group"); // This is the table to flush
Object.DELETEALL;
But test it GOOD and use extreme caution before using this in production enviroment. (I have only seen and used it in internal tools) I don't know which permissions it requires...

For SQL option, it's works properly. But you must have the authorization to delete coz permission property in report cannt work. And now I'm looking the way to delete data by unauthorized user.

Delete table in single shot!

Comments

Categories