query runs extremely slow

[shopwise]

the following delete query runs extremely slow that it does not progress past one bar on the progress bar. any ideas on how to correct this issue? perhaps changing the data type or compression of the underlying table? if yes, in what manner should this be done

DELETE *
FROM table_query
WHERE table_query.FDataSource <> 'Q' AND table_query.SoldToFax IN (SELECT table_query.SoldToFax
FROM table_query
WHERE (((table_query.SoldToFax) In (SELECT [SoldToFax] FROM [table_query] As Tmp GROUP BY [SoldToFax] HAVING Count(*)>1 )))
GROUP BY table_query.SoldToFax
ORDER BY table_query.SoldToFax);

 

[jrbarnett]

First step - take the ORDER BY off the bottom of the query, there is no need to sort the result of a delete query. Rerun and check.

Next: How many different values are there in the FDataSource field?
(ie if you can rewrite:

WHERE table_query.FDataSource <> 'Q'

to
WHERE (table_query.FDataSource IN ('Z', 'P')

it will almost certainly be faster, especially if you index the FDataSource field.

Third: Is the SoldToFax field indexed?
this should speed up your subquery.

Finally - is this an Access database backend and frontend, or are the data in a backend client/server database such as SQL Server or MySQL? if so there are further optimisations such as pass through queries that can help.

John

 

[PHV]

Why TWO subqueries ?
DELETE *
FROM table_query
WHERE FDataSource <> 'Q' AND SoldToFax IN (SELECT SoldToFax FROM table_query GROUP BY SoldToFax HAVING Count(*)>1)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886