Delete query is working very slow (1 Viewer)

Hi,

i have very simple query which is checkin if table in database has all fields from CSV (with no PK field) . If not - i am deleting them.

Query looks like:



where tblOsServers are servers in database, qry is transformed CSV. (transformed because i do not have all fields in csv and have to match with IDs with database's tables).

TblOsServers has only 6000 records.
But Autonumbers is almost 21000 (i was deleting and adding into this table).

As Delete query i am using:

The issue is that fist query (qryIm_TempOsServersSourceToDelete) is taking about 1 second to run.
And even i have this query empty (so not rows to delete) - delete query is running few hours.

I do not know why. How can i solve the issue?
Maybe better is to delete all rows from table and append new records? But this will cause database bloating...

How you Guys solving the similar issues?
Exists function is looping record by record and i suspect this is the cause of problem...but please advice.

Best Wishes,
Jacek

Hi. Not sure I completely follow but the funny thing I noticed about this is you're deleting from a table based on the result of a query involving the same table you're deleting from. Perhaps there's a way to make this simpler, somehow...

hi theDBguy,

i do not understand. This the same table is not the same - one is from CSV, one is from database.
In CSV i have to add fields with ID to create left join like in the picture above and know what to delete fro database table, query is fast.

But delete query with exists is very slow. I have no idea how this is working.
Did you have similar issues?
Maybe i should delete all from table and only append new records?

Exists function is very slow for me and i suspect not only for me - i saw issues in web about them...But not one solution...

Please help,
Jacek

Jacek
Just to remind you to have another look at my Synchronise Data article. It may help.

Thank you Colin for reminding.

I saw a lot of methods for that but i am trying to delete only not existing rows in database table. So your method 1 and 2.

But in this case i can not do this because i have to build query first and on this query delete data.

So the solution seems to be delete whole data from table. But this will cause database bloating.

How to handle database bloating? Ech...

Best,
Jacek

Could you use

jaryszek said:

hi theDBguy,

i do not understand. This the same table is not the same - one is from CSV, one is from database.
In CSV i have to add fields with ID to create left join like in the picture above and know what to delete fro database table, query is fast.

But delete query with exists is very slow. I have no idea how this is working.
Did you have similar issues?
Maybe i should delete all from table and only append new records?

Exists function is very slow for me and i suspect not only for me - i saw issues in web about them...But not one solution...

Please help,
Jacek

Click to expand...

Hi. What I was saying was this. It appears to me that you're trying to delete from tblOsServers all the records that didn't have a matching ServerID and others fields from another data source, correct? If so, I wonder if you can leave out the Find Unmatched Query from the equation. For better understanding, can you post a cut down version of your db with some test data? Thanks.

thank you very much.

Gasman - your query is no making any difference.


ok, thank you, i will provide sample.

Colin - your method is not explaining how to deal with database bloating.
Constantly deleting from table will result with database bloat...

Jacek

Instead of deleting, could you just mark them as deleted/not active with a flag?
Then perhaps delete periodically if necessary?

Jacek
Gasman's suggestion is certainly worth considering. Normally I mark records as inactive rather than delete them asking th and only delete records from 'temp' tables.

You haven't said which of my many methods you are using.
However if you do need to delete/append records repeatedly database bloat will indeed happen. Depending on how significant this is there are two main methods of dealing with the issue.
1. Monitor the file size and when it gets above a specified value, run a compact on close...or better still pop up a message at closedown asking whether the user wants to do that.
OR
2. Use a external 'side-end' database to manage the temp tables involved. This means the FE and BE are hardly affected. At closedown the side-end should be automatically compacted.
A variation is to create a new side end each time you run your routine and delete the file at the end.