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 Fri, 26 Jan 2007 14:31:04 -0330, Tim Marshall
<TI****@PurpleP andaChasers.Moe rtheriumwrote:

Same here. Last week it didn't get above 65. Brrrr.
-Tom.

>Tom van Stiphout wrote:

>On Fri, 26 Jan 2007 02:08:43 -0330, Tim Marshall
<TI****@Purple PandaChasers.Mo ertheriumwrote:

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)

[SQL Learner]

Are you guys going to help or just chat about the weather?

[Lyle Fairfield]

"SQL Learner" <ex***********@ gmail.comwrote in
news:11******** *************@l 53g2000cwa.goog legroups.com:

Are you guys going to help or just chat about the weather?

I would just chat about the weather, at least until I had some more
information.

1. I want to see the REAL problem. A regular here can spend thirty minutes
or so coming up with a suggested solution for a problem. Often, the
original poster responds that the solution didn't work. Why? Because the
problem presented was just an example, or he/she didn't mention that the
problem occurred in Access 2.0, or that one of the tables is a text file.
Your situation of designating a State through identification of street name
and number seems to be a candidate to be the origin of one of these
frustrating experiences. Surely every State has several 120 Main Streets so
how could 120 Main Street identify the State? Of course, maybe I'm
misunderstandin g what you wrote. That is not my problem; it's your problem.
Perhaps you have a very small amount of data, so that, indeed, there is
only one 120 Main Street and it is in NY. But a record count of 2 100 000
does not seem to suggest this.
Regardless, the problem you present is quite challenging, and is the result
of a very poorly though out design, or some kind of legacy situation.

2. I want to know about the REAL problem. Is a this a one time need or will
the data be used time and time again to answer question after question? If
it's the latter, we need to parse the address data, probably into fields
recognized and used by the US Post Office. Then the State Designation may
become easier and faster. And the answer to other questions down the line
will be as well.

My guess is that Tim and/or Tom would charge you a $100 bucks an hour if
you were paying them. As you aren't, they may chat about whatever they
please.
If you describe your problem again, very precisely and openly, then one of
them, or someone else, may take a run at it.

[Larry Linson]

"SQL Learner" <ex***********@ gmail.comwrote

Thank you, Tom. Here is the SQL:

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

My guess is that using the LIKE comparison requires a manipulation and
comparison of every record, vastly increasing the time and disk accesses
required.

If you can redesign your tables so that you are comparing indexed fields,
you are likely to see a significant increase in speed, as the initial
comparison will only use the indexes.

If you can come up with another way to solve the business purpose, you may
be able to use a different approach that is faster, as Lyle pointed out.
Because, if you stick with the approach you came up with to deal with the
problem at hand, there may not BE a way to improve the speed.

And, also, as he pointed out, oversimplifying can be even worse than burying
us in irrelevant information. And, the FAQ should, but doesn't state,
"realize that each additional request for clarification that the potential
responders have to make reduces your probability of getting a good answer to
your question". If it is like "pulling one's own teeth" to determine what
you are actually trying to accomplish so they can help you, some will just
lose interest and go answer some other questions.

Larry Linson
Microsoft Access MVP

[SQL Learner]

=============== =============== =============== =====
....... LIKE comparison requires a manipulation and
comparison of every record, vastly increasing the time and disk
accesses
required.
=============== =============== =============== =====
..........we need to parse the address data, probably into fields
recognized and used by the US Post Office. .........
=============== =============== =============== =====

Hi Larry and Lyle,

Thank you. That is the kind of answers (above) I need.

The actually problem is very complex and may take 2000 words to
describe it. People may lose their interest reading such complex spec
(or really ask me to pay $100 per hour - which I definely could not
afford). That is why I would rather present the question in a
simplified way. Once I got some hints, I might be able to solve the
problem myself.

I already got the solutions with the help from another MVP:

UPDATE tblStreet_Value AS S INNER JOIN tblWord_Number AS W ON
S.Street
Like "*" & W.Number & "*" & W.Word & "*" SET S.Value2 = W.Value;

The query works very fast for the sample tables, but when it is
implied to the real data (with 3,200,000 records on tblStreet_Value
and 7,000 on TblWord_Number) , it took forever for the result to come
up.

My objective is, as stated in my initial post, to find a way to speed
up the query process. After experimenting with different type of
queries while I am waiting for answers, I have tried the same methods
that you suggested: Parse the street field into several different
fields and use a "perfect match" instead of using Like for partial
match AND indexed the fields being linked.

For experimental purpose, I used a SELECT query instead of a UPDATE
query. They improved the speed performance, but still not fast enough
(3 hours on my duo CPU laptop).

Holding all other things constant/same (same machine spec and same
query), would the query be faster if it is ran on a SQL Server ? Is
Access 2003 still using the same jet engine as SQL Server?
Would using indexing really slow down the update query (as I read from
somewhere)?
In general, would the use of Group By or Distinct slow down the
query? Which is fast - Group By or Distinct?

(I understand that the slow down may be caused by the poor design of
the database and inadequate SQL statement.)

Sorry guys, next time I will be more thoughtful.

- Your Humble Grasshopper -

[Rick Brandt]

"SQL Learner" <ex***********@ gmail.comwrote in message
news:11******** **************@ s48g2000cws.goo glegroups.com.. .

=============== =============== =============== =====
...... LIKE comparison requires a manipulation and
comparison of every record, vastly increasing the time and disk
accesses
required.
=============== =============== =============== =====
.........we need to parse the address data, probably into fields
recognized and used by the US Post Office. .........
=============== =============== =============== =====

Hi Larry and Lyle,

Thank you. That is the kind of answers (above) I need.

The actually problem is very complex and may take 2000 words to
describe it. People may lose their interest reading such complex spec
(or really ask me to pay $100 per hour - which I definely could not
afford). That is why I would rather present the question in a
simplified way. Once I got some hints, I might be able to solve the
problem myself.

I already got the solutions with the help from another MVP:

UPDATE tblStreet_Value AS S INNER JOIN tblWord_Number AS W ON
S.Street
Like "*" & W.Number & "*" & W.Word & "*" SET S.Value2 = W.Value;

The query works very fast for the sample tables, but when it is
implied to the real data (with 3,200,000 records on tblStreet_Value
and 7,000 on TblWord_Number) , it took forever for the result to come
up.

My objective is, as stated in my initial post, to find a way to speed
up the query process. After experimenting with different type of
queries while I am waiting for answers, I have tried the same methods
that you suggested: Parse the street field into several different
fields and use a "perfect match" instead of using Like for partial
match AND indexed the fields being linked.

For experimental purpose, I used a SELECT query instead of a UPDATE
query. They improved the speed performance, but still not fast enough
(3 hours on my duo CPU laptop).

Holding all other things constant/same (same machine spec and same
query), would the query be faster if it is ran on a SQL Server ? Is
Access 2003 still using the same jet engine as SQL Server?
Would using indexing really slow down the update query (as I read from
somewhere)?
In general, would the use of Group By or Distinct slow down the
query? Which is fast - Group By or Distinct?

(I understand that the slow down may be caused by the poor design of
the database and inadequate SQL statement.)

Sorry guys, next time I will be more thoughtful.

To speed this up you need exact matches on fields that are indexed. Parsing the
data up in a SELECT query does not allow you to index on the individual fields
so accomplishes nothing.

You need to create a NEW table where the existing data is split into fields you
can index and then apply exact matching to. Anything less than that and you are
stuck with how long it takes to scan the entire table a row at a time which is
99% driven by the speed of your disk drive access on the PC.

SQL server would not help unless it was on a machine that had faster disk i/o
than the one you have now and you absolutely do NOT want this to run over a
network. That would slow i/o to a point that would be brutal.
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[SQL Learner]

Thanks, Rick.

I created a new table for the parsed data. For now, I will continue
to use Access.

- Grasshopper -

[Lyle Fairfield]

On Jan 27, 10:22 am, "SQL Learner" <excelmodel...@ gmail.comwrote:

=============== =============== =============== =====
...... LIKE comparison requires a manipulation and
comparison of every record, vastly increasing the time and disk
accesses
required.
=============== =============== =============== =====
.........we need to parse the address data, probably into fields
recognized and used by the US Post Office. .........
=============== =============== =============== =====

Hi Larry and Lyle,

Thank you. That is the kind of answers (above) I need.

The actually problem is very complex and may take 2000 words to
describe it. People may lose their interest reading such complex spec
(or really ask me to pay $100 per hour - which I definely could not
afford). That is why I would rather present the question in a
simplified way. Once I got some hints, I might be able to solve the
problem myself.

I already got the solutions with the help from another MVP:

UPDATE tblStreet_Value AS S INNER JOIN tblWord_Number AS W ON
S.Street
Like "*" & W.Number & "*" & W.Word & "*" SET S.Value2 = W.Value;

The query works very fast for the sample tables, but when it is
implied to the real data (with 3,200,000 records on tblStreet_Value
and 7,000 on TblWord_Number) , it took forever for the result to come
up.

My objective is, as stated in my initial post, to find a way to speed
up the query process. After experimenting with different type of
queries while I am waiting for answers, I have tried the same methods
that you suggested: Parse the street field into several different
fields and use a "perfect match" instead of using Like for partial
match AND indexed the fields being linked.

For experimental purpose, I used a SELECT query instead of a UPDATE
query. They improved the speed performance, but still not fast enough
(3 hours on my duo CPU laptop).

Holding all other things constant/same (same machine spec and same
query), would the query be faster if it is ran on a SQL Server ? Is
Access 2003 still using the same jet engine as SQL Server?
Would using indexing really slow down the update query (as I read from
somewhere)?
In general, would the use of Group By or Distinct slow down the
query? Which is fast - Group By or Distinct?

(I understand that the slow down may be caused by the poor design of
the database and inadequate SQL statement.)

If you've decided to parse the address field why not tell us? Then
give us a few examples of what the text in an address field really
looks like. Then ask, "What's your best solution on how to parse the
address field and populate the (more) atomic fields?" I'm guessng I
would use a UDF with Regular Expressions but others might have better,
faster solutions.

IMO, the notion that indexing will drastically slow down an update
query is greatly exaggerated. Of course, in your case, as you will not
have indexed the new fields (you may want to later) it will not slow
things at all. (The reason an index might slow down an update is that
a new index key and pointer must be written for each new or updated
value; if there's no index on the field; there's no new key and hence,
no slowdown).

SQL-Server is likely to do an update based on an existing field at the
Server level. As I recall, SQL Server 2005 let's us use Regular
Expressions with a T-SQL UDF. If that's the case we should be able to
parse out the data in ten minutes. But I've never done this.
In any case SQL-Server might be faster. As it would do it without
stopping your local machine at all, you are unlikely to notice how
long it takes, unless you are waiting breathlessly for results.

[SQL Learner]

Thanks, Lyle.

I think the best solution is to parse the string into different fields
and index them. (It is good that none of these fields will be the
updated fields.)

It is quite a challenge to parse the following format
East 123 ABC Street" or "S. 100 Apple Road

into

Field1 Field2 Field3
-------- --------- ----------------
East 123 ABC Street
S. 100 Apple Road

since there is no comment "delimited character" to use.

May be one can use the following logic - split the string using the
first space location before the number (field1), extract the number
using the first and second spaces (field2), and extract the last piece
of data after the last number digest + space (field3). How can I
write T-SQL UDFs for these three fields in Access?

- Grasshopper -

[Lyle Fairfield]

On Jan 27, 10:29 am, "Rick Brandt" <rickbran...@ho tmail.comwrote:

"SQL Learner" <excelmodel...@ gmail.comwrote in messagenews:11* *************** ******@s48g2000 cws.googlegroup s.com...

SQL server would not help unless it was on a machine that had faster disk i/o
than the one you have now and you absolutely do NOT want this to run over a
network. That would slow i/o to a point that would be brutal.

Please, expand on this. It seems you are relating SQL Server to
running over a network and suggesting that running the query in SQL-
Server "over a network", whatever that would mean, would be slow. I
doubt very much if that is what you mean.