slow delete query

  • Thread starter Thread starter shank
  • Start date Start date
S

shank

I have the below DELETE query that connects from Access to an SQL Server
database over the internet via ODBC. With no matching records, this query
can take 3 minutes to complete. How do I track down why it's taking so long?
The online [Titles] has 130,000 records, but don't think that's the issue.
This query worked fine around 100,000 records. I've also tried DELETE
DISTINCT and it doesn't help.

thanks!
 
Dear Shank:

As a quick and easy test, change it to a pass-thru query so it runs
within the server. Is it faster that way?

DELETE Songs
FROM Songs
INNER JOIN Titles ON Songs.OrderNo = Titles.OrderNo;

If it's still slow, look to see if there are indexes on Songs.OrderNo
and Titles.OrderNo. These would be important to performance,
especially the one on Titles.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top