what's faster?
DELETE * FROM table_name;
or
DELETE * FROM table_name where 1=1;
why?
does truncate table work in access?
views:
177answers:
3+1 for truncate
Adnan
2010-06-08 20:14:59
Bingo. The answer was C) none of the above.
ceejayoz
2010-06-08 20:15:02
It sounded like a homework question, so I thought I'd go a different direction... :)
Jaymz
2010-06-08 20:15:50
does truncate table work in access?
I__
2010-06-08 20:19:06
No, would have helped if you mentioned Access in your original question. In which case, DELETE FROM table_name will do the job quickest as answered below.
Jaymz
2010-06-08 20:22:08
TRUNCATE seems to be the fastest way but only since 5.x!
Pekka
2010-06-08 20:23:11
Bear in mind that `TRUNCATE` will reset all `AUTO_INCREMENT` counts on MySQL tables (not sure if MS Access has such a thing).
BoltClock
2010-06-08 20:24:42
Access does not maintain a transaction log, which is why there's no need for the TRUNCATE statement, and why the DELETE FROM table_name will do the job nicely.
Jaymz
2010-06-08 20:26:20
+4
A:
This should be faster:
DELETE * FROM table_name;
because RDBMS don't have to look where is what.
You should be fine with truncate though:
truncate table table_name
Sarfraz
2010-06-08 20:15:25
Jet treats DELETE * FROM Table as a truncate, instead of deleting the records one by one. I don't think it resets the Autonumber seed value, though. That has to be done in code or with a compact (not even sure it will reset with a compact in recent iterations of Jet/ACE).
David-W-Fenton
2010-06-24 21:09:30
+2
A:
There is a mySQL bug report from 2004 that still seems to have some validity. It seems that in 4.x, this was fastest:
DROP table_name
CREATE TABLE table_name
TRUNCATE table_name was DELETE FROM internally back then, providing no performance gain.
This seems to have changed, but only in 5.0.3 and younger. From the bug report:
[11 Jan 2005 16:10] Marko Mäkelä
I've now implemented fast TRUNCATE TABLE, which will hopefully be included in MySQL 5.0.3.
Pekka
2010-06-08 20:15:34