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
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:
Code:
SELECT tblOsServers.OsServerID
FROM tblOsServers LEFT JOIN qryIm_TempOsServersDatabase2 ON (tblOsServers.ServerID = qryIm_TempOsServersDatabase2.ServerID) AND (tblOsServers.Customer = qryIm_TempOsServersDatabase2.CustomerName) AND (tblOsServers.SystemVersionOSImageIDFK = qryIm_TempOsServersDatabase2.SystemVersionOsImageID)
WHERE (((qryIm_TempOsServersDatabase2.CustomerName) Is Null) AND ((qryIm_TempOsServersDatabase2.ServerID) Is Null) AND ((qryIm_TempOsServersDatabase2.SystemVersionOsImageID) Is Null));
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:
Code:
DELETE tblOsServers.*
FROM tblOsServers AS t1
WHERE exists (select 1 from qryIm_TempOsServersSourceToDelete as t2 where t1.OsServerID = t2.OsServerID);
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