_Extremely_ slow deletes?

[Maury Markowitz]

I have two databases that I need to keep in sync. In order to do this, I have
an MDB that links to both, and every night it deletes the "local" copy of the
table in our SQL Server, and then re-copies the data from the external source.

Currently I'm doing this only with two tables. One contains two 15-wide
varchars, and is about 10000 rows long with an index on the first col. Doing
a DELETE all on this table takes over 20 seconds! Is this expected? This
represents a significant amount of time in the overall process. TRUNCATE
doesn't work in MDB, at least I'm not sure how to do it.

Maury

 

[Maury Markowitz]

I have now tried this on a second table I wish to mirror. The table is about
22k lines of about 15 columns, mostly ints and smallints. DELETE FROM
tblPAMmirror" in Access via the linked table took over 1 minute. Executing
the same command in Query Analyzer took less than one second.

Any ideas?

Maury

 

[Dirk Goldgar]

I have two databases that I need to keep in sync. In order to do this, I
have
an MDB that links to both, and every night it deletes the "local" copy of
the
table in our SQL Server, and then re-copies the data from the external
source.

Currently I'm doing this only with two tables. One contains two 15-wide
varchars, and is about 10000 rows long with an index on the first col.
Doing
a DELETE all on this table takes over 20 seconds! Is this expected? This
represents a significant amount of time in the overall process. TRUNCATE
doesn't work in MDB, at least I'm not sure how to do it.

I don't have an answer offhand as to why the DELETE query is taking so long.
You could always use SQL Profiler to see what commands are being passed back
and forth between Access and the Server.

Or you could sidestep the whole question by using a pass-through query.
Then you could use TRUNCATE if you want.

 

[david]

Probably doing it inside a transaction. But if TRUNCATE is what you
want, TRUNCATE is what you should do.

(david)

 

[Maury Markowitz]

:

Sorry for my tardy reply...

Or you could sidestep the whole question by using a pass-through query.
Then you could use TRUNCATE if you want.

You know, I need to know a lot more about the whole pass-through query
concept. Do you have a good guide you might recommend? MS's docs are not
terribly useful in determining when to use or not use them.

Maury

 

[Douglas J. Steele]

:

Sorry for my tardy reply...


You know, I need to know a lot more about the whole pass-through query
concept. Do you have a good guide you might recommend? MS's docs are not
terribly useful in determining when to use or not use them.

Essentially, use pass-through whenever you're dealing with an external DBMS
(SQL Server, Oracle, MySQL, etc.) and you don't need the query to be
updatable.

 

[david]

You know, I need to know a lot more about the whole pass-through

Pass through queries are like linked tables, only without the table.

There are three ways to use a pass through query: either as a saved
query, which gives you a saved query with a saved connection
and a different icon (and you can dynamically change the SQL if you want),

or as dynamic sql, which you get by setting up a new DAO querydef,
with a connection string, without a query name, and without SQL.
Then you add the dynamic sql to. (It can get too confusing if you
try to set up the SQL before you set the connection string, and
in code, giving a querydef a name is how you save it).

or as an ADO querydef.

So the first decision is: ADO, DAO, or Access database window?

(david)