How to fix Access Query that is running very slow?

[David Goldstraw]

I have two databases and I want to automate a process to find duplicates. I originally linked the table from one into the other but I have now gone down the route of importing the table from one db into the main one because it was running too slow.

Now I have imported the table I am still getting a very slow response from the query. I have tried numerous routes i.e. update, make table and even select and it is till slow. The query I am running is (will example update mode)

UPDATE tbmaindata SET tbmaindata.InChineseDB = "Y"
WHERE left(tbmaindata.add1,6) & tbmaindata.postcode = left(tbmaindata1.add1,6) & tbmaindata1.postcode;

This query no matter how I seem to do it whether it be like the example or using various queries to get the result is really slow and this process need to be automated every time the db is opened. This would take forever.

All I want to do is confirm the existence of certain records in another db. I was going to achieve this by using a macro to import the table, run query to update a column and then delete the imported table.

Can someone please help me speed this process up or point out if there is something amiss with my query.

Thanks in advance

Dave

[code green]

It's the string functions and string comparison.
The fact string manipulation is done across two databases won't help either.
Can the WHERE clause compare some numerical field instead of postcode?
If not could you add numerical comparison fields?

All I want to do is confirm the existence of certain records in another db

If this is the case, it is better to JOIN the two tables ON a suitable field, and using
WHERE field IS NOT NULL

[David Goldstraw]

Hi Thanks for your reply, I have actually imported the table in to my main db and it is still slow. I will try your suggestion by linking the tables

[TheSmileyCoder]

Just to have something to relate to, how many records are present in each database, and how long is it taking?

For some things/users, 3 seconds is long time, and sometimes 3 minutes is a long time, so you need to be a bit more accurate :P

Other then that "Code Green" is right, the string Manipulations are slowing you down.

[David Goldstraw]

There are 13,026 in one and 1,505 in the other. it was taking 15+ mins.

[David Goldstraw]

Hi Code Green,
You certainly pointed me in the right direction. I have used the following which appears to give me my required result.

SELECT tbmaindata.URN, tbmaindata.Add1, tbmaindata.postcode, tbMainData1.URN, tbMainData1.Add1, tbMainData1.postcode
FROM tbmaindata INNER JOIN tbMainData1 ON (tbmaindata.postcode) = tbMainData1.postcode AND (left(tbmaindata.Add1,6)) = left(tbMainData1.Add1,6);

Thanks for your help

Dave

[Rabbit]

Indexes on postcode and Add wouldn't hurt either.

[David Goldstraw]

I had already indexed them but it didn't help, that was my first thought. The db is sat on a server which doesn't help. It seems to be working a treat now anyway so thanks eveyone