When working with a SQL Server 2000 Backend / Access 2000 Frontend, Is it
faster to Delete an enormous table and create a new one (via Stored
Procedure), rather than to Delete all of the data in that table and reuse it?
I have not read anything on the subject. However, I was instructed by the
gentleman that introduced me to SQL Server 2000 that Delete Table is faster
than Delete Data. Which brings me to here:
While reading through somebody's post / response string on here I came
across the following that contradicts what I had been previously told:

(BEGIN ~ Quote copied from another posting for somebody else...)

If you are adding and deleting tables, your design has some
very serious mistakes. I suspect that you have gotten a
fair way down a flawed design path and are now running into
the first of the many problems caused by that design.
I suggest that you back up and look at the bigger picture to
come up with a relational design.
-- **** MVP [MS Access]

(END~ Quote copied from another posting for somebody else...)

And the second question would be is the answer to the first question the
same when applied to local tables directly in Access itself? Thanks for any
information provided!

Take Care & God Bless ~ SPARKER ~

"=?Utf-8?B?c3Bhcmtlcg==?="
<SpamFreePlease@(E-Mail Removed)@SpamFreePlease> wrote in
news:60DC1E1D-4008-43C5-9A16-(E-Mail Removed):

> When working with a SQL Server 2000 Backend / Access 2000 Frontend, Is
> it faster to Delete an enormous table and create a new one (via Stored
> Procedure), rather than to Delete all of the data in that table and
> reuse it?

You are probably better off asking this is a SQL server question: or just
asking your db administrator. Note that dropping a table means dropping
all the relationships and then recreating them afterwards; you don't have
to do this with a DELETE FROM.

> If you are adding and deleting tables, your design has some
> very serious mistakes. I suspect that you have gotten a
> fair way down a flawed design path and are now running into
> the first of the many problems caused by that design.
> I suggest that you back up and look at the bigger picture to
> come up with a relational design.

I think this is broadly true. There is obviously a case for temp tables
to buffer imported data, but such would normally be created in the TEMP
database rather than the active one. I can't think of many situations,
though, where there is a legitimate reason to scrap a working table in an
active database.

> And the second question would be is the answer to the first question
> the same when applied to local tables directly in Access itself?

Either way there is likely to be a lot of fragmentation, with the risk of
corruption. Use a temp .mdb file for temp tables; correct the design for
everything else.

Hope that helps


Tim F

As far as I know (and that ain't very far), dropping (truncating?) a table in
SQL server and repopulating it is a lot faster then deleting all the records (in
large tables). Small tables (few records) may be faster to delete the old
records and add in the new ones.

The reason is that the log file does not write the delete of each record if you
drop (truncate) the table. IF i recall correctly it writes only one record to
the activity log.

Best to ask that question in the SQL forums for a good explanation.

I would say that the quote you posted applies to Access + Jet.

My two cents (and that isn't a large amount of money).

sparker wrote:
>
> When working with a SQL Server 2000 Backend / Access 2000 Frontend, Is it
> faster to Delete an enormous table and create a new one (via Stored
> Procedure), rather than to Delete all of the data in that table and reuse it?
> I have not read anything on the subject. However, I was instructed by the
> gentleman that introduced me to SQL Server 2000 that Delete Table is faster
> than Delete Data. Which brings me to here:
> While reading through somebody's post / response string on here I came
> across the following that contradicts what I had been previously told:
>
> (BEGIN ~ Quote copied from another posting for somebody else...)
>
> If you are adding and deleting tables, your design has some
> very serious mistakes. I suspect that you have gotten a
> fair way down a flawed design path and are now running into
> the first of the many problems caused by that design.
> I suggest that you back up and look at the bigger picture to
> come up with a relational design.
> -- **** MVP [MS Access]
>
> (END~ Quote copied from another posting for somebody else...)
>
> And the second question would be is the answer to the first question the
> same when applied to local tables directly in Access itself? Thanks for any
> information provided!
>
> Take Care & God Bless ~ SPARKER ~

Ok Thanks.
--
~ SPARKER ~


"Tim Ferguson" wrote:

> "=?Utf-8?B?c3Bhcmtlcg==?="
> <SpamFreePlease@(E-Mail Removed)@SpamFreePlease> wrote in
> news:60DC1E1D-4008-43C5-9A16-(E-Mail Removed):
>
> > When working with a SQL Server 2000 Backend / Access 2000 Frontend, Is
> > it faster to Delete an enormous table and create a new one (via Stored
> > Procedure), rather than to Delete all of the data in that table and
> > reuse it?
>
> You are probably better off asking this is a SQL server question: or just
> asking your db administrator. Note that dropping a table means dropping
> all the relationships and then recreating them afterwards; you don't have
> to do this with a DELETE FROM.
>
> > If you are adding and deleting tables, your design has some
> > very serious mistakes. I suspect that you have gotten a
> > fair way down a flawed design path and are now running into
> > the first of the many problems caused by that design.
> > I suggest that you back up and look at the bigger picture to
> > come up with a relational design.
>
> I think this is broadly true. There is obviously a case for temp tables
> to buffer imported data, but such would normally be created in the TEMP
> database rather than the active one. I can't think of many situations,
> though, where there is a legitimate reason to scrap a working table in an
> active database.
>
> > And the second question would be is the answer to the first question
> > the same when applied to local tables directly in Access itself?
>
> Either way there is likely to be a lot of fragmentation, with the risk of
> corruption. Use a temp .mdb file for temp tables; correct the design for
> everything else.
>
> Hope that helps
>
>
> Tim F
>
>

Ok Thanks.
--
~ SPARKER ~


"John Spencer (MVP)" wrote:

> As far as I know (and that ain't very far), dropping (truncating?) a table in
> SQL server and repopulating it is a lot faster then deleting all the records (in
> large tables). Small tables (few records) may be faster to delete the old
> records and add in the new ones.
>
> The reason is that the log file does not write the delete of each record if you
> drop (truncate) the table. IF i recall correctly it writes only one record to
> the activity log.
>
> Best to ask that question in the SQL forums for a good explanation.
>
> I would say that the quote you posted applies to Access + Jet.
>
> My two cents (and that isn't a large amount of money).
>
> sparker wrote:
> >
> > When working with a SQL Server 2000 Backend / Access 2000 Frontend, Is it
> > faster to Delete an enormous table and create a new one (via Stored
> > Procedure), rather than to Delete all of the data in that table and reuse it?
> > I have not read anything on the subject. However, I was instructed by the
> > gentleman that introduced me to SQL Server 2000 that Delete Table is faster
> > than Delete Data. Which brings me to here:
> > While reading through somebody's post / response string on here I came
> > across the following that contradicts what I had been previously told:
> >
> > (BEGIN ~ Quote copied from another posting for somebody else...)
> >
> > If you are adding and deleting tables, your design has some
> > very serious mistakes. I suspect that you have gotten a
> > fair way down a flawed design path and are now running into
> > the first of the many problems caused by that design.
> > I suggest that you back up and look at the bigger picture to
> > come up with a relational design.
> > -- **** MVP [MS Access]
> >
> > (END~ Quote copied from another posting for somebody else...)
> >
> > And the second question would be is the answer to the first question the
> > same when applied to local tables directly in Access itself? Thanks for any
> > information provided!
> >
> > Take Care & God Bless ~ SPARKER ~
>