Delete records in a 50 mb .mdb

learn
learn used Ask the Experts™
on
Hi,

In VB6, I want to do my DELETE JOB in a big database, a 50 mb .mdb, something like:

s = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=a50mb.mdb"
objConnection.Open s
sql = & _
"Select [User] From Table2 Union All " & _
"Select [User] From Table3 Union All " & _
"Select [User] FROM Table4 IN  '' [MS Access;PWD=123456;DATABASE=" & anotherfile.mdb & "] Union All " & _
"Select [User] FROM Table5 IN  '' [MS Access;PWD=123456;DATABASE=" & anotherfile.mdb & "] "
sql = sql & "Group By [User] Having Count(*)=1"
sql = "Delete * From Table1 Where [User] in (" & sql & ")"
objConnection.Execute sql

Before doing that, I run my TEST JOB in the same procedure but only for select:

s = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=a50mb.mdb"
objConnection.Open s
sql = & _
"Select [User] From Table2 Union All " & _
"Select [User] From Table3 Union All " & _
"Select [User] FROM Table4 IN  '' [MS Access;PWD=123456;DATABASE=" & anotherfile.mdb & "] Union All " & _
"Select [User] FROM Table5 IN  '' [MS Access;PWD=123456;DATABASE=" & anotherfile.mdb & "] "
sql = sql & "Group By [User] Having Count(*)=1"
sql = "Select * From Table1 Where [User] in (" & sql & ")"
objRecordset.Open sql, objConnection

It looked working correctly, but took about 10 minutes! I then think it will take even longer time to delete the records.

My question would be how to do my DELETE JOB quickly?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I can't say for certain, but I believe your query will be faster if you use four separate subqueries instead of one union query:
Delete * from Table1
where [User] in (Select [User] From Table2)
or  [User] in (Select [User] From Table3)
or [User] in (Select [User] From Table4 in ...)
or [User] in (Select [User] From Table5 in ...)
--
Graham

Author

Commented:
My problem is I need to detect if the [User] is
"Group By [User] Having Count(*)=1"
for all tables>

Author

Commented:
I have detected all the steps in a few seconds, only
sql = "Select * From Table1 Where [User] in (" & sql & ")"
spent 10 minutes!
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Are you saying the [User] needs to exist with EXACTLY ONE record in EACH OF the four tables?
That is not what your original query is doing.  You are looking for any instance of [User] in any of the four tables.
Please clarify.
--
Graham

Author

Commented:
[User] needs to exist with EXACTLY ONE record in all of the four tables. if it in table2 then should not in table3, table4 nor table5.
Ah, then that is an entirely different prospect!
I think the best way would be to do a Count(*) subquery on each table and add them together:
Delete * from Table1 as T
where (Select Count(*) from Table2 as A where A.[User]=T.[User])
+ (Select Count(*) from Table3 as B where B.[User]=T.[User])
+ (Select Count(*) from Table4 in ...  as C where C.[User]=T.[User])
+ (Select Count(*) from Table5 in ... as D where D.[User]=T.[User]) =1
--
Graham

Author

Commented:
Thank you.
Can you explain:
Delete * from Table1 as T
where (Select Count(*) from Table2 as A where A.[User]=T.[User])

Author

Commented:
This is very interesting/new code for me.

How this sub query can get T from outside and you don't need Join:
(Select Count(*) from Table2 as A where A.[User]=T.[User])
Top Expert 2014

Commented:
The fastest way to do your deleted is to populate a (temporary) table with the unique [user] values.  Then inner join table1 with the temporary table when you do your delete.

You can use a Make-Table query or clear/re-populate the same table in two queries.

Author

Commented:
aikimark,

Do you think I can do what you suggested by VB6?
I am using VB6 to connect Access and do the above job.
<quote>
Can you explain:
Delete * from Table1 as T
where (Select Count(*) from Table2 as A where A.[User]=T.[User])
</quote>
The subquery will return the count of records in Table2 where the [User] field (in Table2) matches the [User] field in the main query (Table1).
<quote>
How this sub query can get T from outside and you don't need Join:
(Select Count(*) from Table2 as A where A.[User]=T.[User])
</quote>
T is an alias for Table1, defined by "Table1 as T".  A is an alias for Table2.  These are simply used in this case to same on typing.  I could just as easily have said:
(Select Count(*) from Table2 where Table2.[User]=Table1.[User])
The advice from aikimark about using a temporary table is generally good, but in this case it will be very difficult to populate such a table.  To find the eligible records, you would need to find those [User] values that occur EXACTLY once in ONLY ONE of the four tables.  To do this, you would need to run all four subqueries simultaneously anyway, so there would be no advantage in using a temporary table.
--
Graham

Author

Commented:
GrahamMandeno,

Thank you for your explanation. Now, I got only one thing still not clear:
"Select Count(*) from Table2 as A where A.[User]=T.[User])"
returns the number of records, say returns 123, not the records themselves. In this case
"Delete * from Table1 as T where (Select Count(*) from Table2 as A where A.[User]=T.[User])"
will become
"Delete * from Table1 as T where (123) "??
If you look again at my query, I am taking the four counts and adding them together, then comparing the sum with 1:
Delete * from Table1 as T
where (Select Count(*) from Table2 as A where A.[User]=T.[User])
+ (Select Count(*) from Table3 as B where B.[User]=T.[User])
+ (Select Count(*) from Table4 in ...  as C where C.[User]=T.[User])
+ (Select Count(*) from Table5 in ... as D where D.[User]=T.[User]) =1
If the total count from all four tables is 1, then it stands to reason that there exists only one record in all four tables.
--
Graham
Top Expert 2014
Commented:
@learn

>>Do you think I can do what you suggested by VB6?
Yes.

Unlike the UI environment, running a make table query when the table already exists will cause an error.  After the table has been created, you will most likely use something like you see in the code snippet below:

Note that you will need to execute these queries through your connection object, not by opening a recordset.  These queries do not return any rows.  If you created a separate stored query for the UNION ALL of tables 2-4, this SQL will be much cleaner.
Delete * From UniqueUsers

"INSERT INTO UniqueUsers ( [User] ) " & _
"SELECT [%$##@_Alias].User " & _
FROM [Select [User] From Table2 Union All " & _
"Select [User] From Table3 Union All " & _
"Select [User] FROM Table4 IN  '' [MS Access;PWD=123456;DATABASE=" & anotherfile.mdb & "] Union All " & _
"Select [User] FROM Table5 IN  '' [MS Access;PWD=123456;DATABASE=" & anotherfile.mdb & "] ]. AS [%$##@_Alias] " & _
"GROUP BY [%$##@_Alias].User " & _
"HAVING (((Count(*))=1)) "


DELETE Table1.*
FROM Table1 INNER JOIN UniqueUsers ON Table1.User = UniqueUsers.User;

Open in new window

Author

Commented:
GrahamMandeno,

Yes, I understood your code now, thank you. I tried run your code, unfortunately, it also spent very long time....I got to stop running after 10 minutes.
Is [User] an indexed field in all four source tables?
--
Graham

Author

Commented:
Thank you, the code is working...though not fast.
Top Expert 2014

Commented:
@learn

Since the thrust of this problem was performance, would you consider reopening the question and allowing me to pursue this a bit further?  I have at least two more things I'd like to try.

I'm a bit limited in my ability to replicate your environment, so I rely on you to provide me with feedback.

>>though not fast
How is its speed relative to your original Delete speed?

How is the speed of the query that populates the temporary table relative to the speed of the Delete query?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial