Speed issue in Access

[SQL Learner]

Hi all,

I have an Access db with two large tables - 3,100,000 (tblA) and 7,000
(tblB) records. I created a select query using Inner Join by partial
matching two fields (X from tblA and Y from tblB). The size of the db
is about 200MBs.

Now my issue is, the query has been running for over 3 hours already -
I have no idea when it will end. I am using Access 2003. Are there
ways to improve the speed performance? (Also, would the query run
faster if I index the two fields?)

Any helpful suggestion will be appreciated.
- Grasshopper -

[Tom van Stiphout]

On 25 Jan 2007 20:05:39 -0800, "SQL Learner" <ex***********@ gmail.com>
wrote:

No. Assuming you mean indexing the two fields used in the
relationship: they are already indexed behind the scenes.

Don't keep us guessing. Give us some SQL.

-Tom.

>Hi all,

I have an Access db with two large tables - 3,100,000 (tblA) and 7,000
(tblB) records. I created a select query using Inner Join by partial
matching two fields (X from tblA and Y from tblB). The size of the db
is about 200MBs.

Now my issue is, the query has been running for over 3 hours already -
I have no idea when it will end. I am using Access 2003. Are there
ways to improve the speed performance? (Also, would the query run
faster if I index the two fields?)

Any helpful suggestion will be appreciated.
- Grasshopper -

[SQL Learner]

Thank you, Tom. Here is the SQL:

SELECT *
FROM tblA AS M INNER JOIN tblB AS S ON M.X Like "*" & S.Y & "*";

[Tim Marshall]

SQL Learner wrote:

Thank you, Tom. Here is the SQL:

SELECT *
FROM tblA AS M INNER JOIN tblB AS S ON M.X Like "*" & S.Y & "*";

Holy crap, what the blazes prompted you to write a hideous join like
this? No wonder it's going forever. If someone gave me a join like
this in MS Jet/Access or Oracle I'd kick his ass out into the snow
drifts outside my office.

Use the query builder that Access provides to construct acceptable SQL.
However, it appears you may not have a properly designed schema. If
you're having to use the like operator to make joins, then you do NOT
have a proper linking field between the two tables.

You should have X being a pointer to Y or visa versa. In other words,
they should be the same value where you want records from the two tables
to be joined. Fix this first.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Tom van Stiphout]

On Fri, 26 Jan 2007 02:08:43 -0330, Tim Marshall
<TI****@PurpleP andaChasers.Moe rtheriumwrote:

Me too. Straight into the spines of a Cholla cactus :-)
-Tom.

>SQL Learner wrote:

>Thank you, Tom. Here is the SQL:

SELECT *
FROM tblA AS M INNER JOIN tblB AS S ON M.X Like "*" & S.Y & "*";

Holy crap, what the blazes prompted you to write a hideous join like
this? No wonder it's going forever. If someone gave me a join like
this in MS Jet/Access or Oracle I'd kick his ass out into the snow
drifts outside my office.

<lip>

[SQL Learner]

Thanks, double T.

The query was for partial match. Not quite sure if it can be done
using query analyzer. Let me give you the original spec of my issue
below:

=============== =============== =============== =============== =============== =============== ==
How can I UPDATE table using partial word match?

TABLE: tblStreet_Value (Table to be updated)

Street Value2
--------------- ------
123 ABC Street
124 ABC Street
125 ABC Street
1 XYZ AVE
2 XYZ AVE
3 XYZ AVE
10 CBS Street
11 CBS Street
12 CBS Street
100 Apple Road
101 Apple Road
102 Apple Road

TABLE: TblWord_Number

Word Number Value
---- ------ -----
ABC 123 NY
ABC 125 CA
CBS 11 MA
CBS 12 TX

Expected Result

Street Value2
--------------- ------
123 ABC Street NY
124 ABC Street
125 ABC Street CA
1 XYZ AVE
2 XYZ AVE
3 XYZ AVE
10 CBS Street
11 CBS Street MA
12 CBS Street TX
100 Apple Road
101 Apple Road
102 Apple Road

=============== =====

[Tim Marshall]

Tom van Stiphout wrote:

On Fri, 26 Jan 2007 02:08:43 -0330, Tim Marshall
<TI****@PurpleP andaChasers.Moe rtheriumwrote:

Me too. Straight into the spines of a Cholla cactus :-)
-Tom.

<Sighwish it was cactus here... we've got a "weather bomb" bearing
down on us for Saturday - which would be great if it were a weekday and
meant a day off work, but on saturday? What a waste... 8)

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me

[Tim Marshall]

SQL Learner wrote:

SELECT *
FROM tblA AS M INNER JOIN tblB AS S ON M.X Like "*" & S.Y & "*";

PS

My response may have come across as aggressive. Please don't let it
scare you away from this group, it's a wonderful resource. It looks as
if you have some major issues with your design and we can help you here
if you're not sure how to correct things.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me

[SQL Learner]

Just to follow up with my information provided above, the Street value
may not always follow the pattern as shown above (Street number +
Streen Name). It was only for simplification reason that I presented
the data that way. Some addresses can be "East 123 ABC Street" or "S.
100 Apply Road, Apt. 4D". So in such cases, string concatenation will
not work.

The SQL works (and it was from an Access MVP's help) - I have tried it
on a sample database where the records are less. I just need to find a
way to speed up the query.

- Grasshopper -

[SQL Learner]

=============== =============== =============== ======
My response may have come across as aggressive. Please don't let it
scare you away from this group, it's a wonderful resource. It looks as

if you have some major issues with your design and we can help you here

if you're not sure how to correct things.
=============== =============== =============== ======

Please do not worry about, Tim. I am very easy going and can take a
lot of "hits". : )

- Grasshopper -