469,266 Members | 1,951 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,266 developers. It's quick & easy.

How to fix Access Query that is running very slow?

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
Jan 25 '11 #1

✓ answered by 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

7 20521
code green
1,726 Expert 1GB
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
Jan 25 '11 #2
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
Jan 25 '11 #3
TheSmileyCoder
2,321 Expert Mod 2GB
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.
Jan 25 '11 #4
There are 13,026 in one and 1,505 in the other. it was taking 15+ mins.
Jan 25 '11 #5
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
Jan 25 '11 #6
Rabbit
12,516 Expert Mod 8TB
Indexes on postcode and Add wouldn't hurt either.
Jan 25 '11 #7
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
Jan 25 '11 #8

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Puneet Murgai | last post: by
2 posts views Thread by vicky | last post: by
3 posts views Thread by =?Utf-8?B?bXNjZXJ0aWZpZWQ=?= | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.