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 -

[Larry Linson]

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

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.

Those, of course, are well-structured addresses and, thus, rather easy to
parse. The real problem will be when the addresses aren't well-structured,
and, in real life, they can just be free-form text, such as "Twelve
Intergalactic Circle" or "Worthingto n House" or "One Microsoft Way" or "9876
125th Street SW" or any of many variations.

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).

Implemented correctly, this would work nicely on the well-structured
examples. It wouldn't work so well on free-form addresses, which you are
entirely likely to encounter.

How can I write T-SQL UDFs for these
three fields in Access?

T-SQL (full name Transact SQL) is a feature of Microsoft SQL Server, a
language for writing stored procedures, and triggers. None are supported
in the Jet database engine that is, by default, used with Access. So the
answer is, "You can't."

On the other hand, you can write user-defined-functions (often abbreviated
as UDF) in Access, and call them from Jet SQL. Just bear in mind that,
using Jet, all the work is done on the user's machine.

Using Microsoft SQL Server, a substantial part of the work may be done on
the server, if you have studied, learned well, and properly designed and
implemented stored procedures and triggers on the SQL Server -- which is a
non-trivial skill that will have to be learned, practiced, and perfected in
addition to Access. It is only if you do that study, learning, design, and
implementation that using SQL Server is likely to give you a performance
increase. It is quite common for users to convert their application data
storage to linked server tables, and discover that the application runs
slower rather than faster.

A well-designed, well-implemented single-user standalone database, just
split for multi-user access does not necessarily make a well-designed,
well-implemented multi-user database. A well-designed, well-implemented
multi-user database, with its back-end tables just moved to a server DB does
not necessarily make a well-designed, well-implemented client-server
database. You have to understand how Access works in each environment in
order to take advantage of the benefits of that environment, and to avoid
the stumbling blocks it may put in your path.

Larry Linson
Microsoft Access MVP

[Rick Brandt]

"Lyle Fairfield" <ly***********@ aim.comwrote in message
news:11******** **************@ a75g2000cwd.goo glegroups.com.. .

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

>"SQL Learner" <excelmodel...@ gmail.comwrote in
messagenews:11 *************** *******@s48g200 0cws.googlegrou ps.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.

I meant that if run in Access/Jet you would want the file to be local. A
network wouldn't matter to SQL Server and would be expected.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[SQL Learner]

Hi Larry,

I am not using SQL Server as the backend and no network is involved.
I have the tables all in Access. The reason why the database is not
well designed is because I just took the data from csv files and
dumpped them into Access. I did not design the database.

You raised a good print regarding the free form address. We would try
to convert all the "not well structured" data into a better structured
data before we run our update query. For example, we would
standardized all the words used in the addresses by, for example,
convert "E." or "E" to "East" or "Five" to "5".

The objective is to get as much update as we can. The "Left over"
will need to be handled manually.
So given these fact and assume that there will be only two type of
structures: "East 123 ABC Street" and "100 Apple Road", how can I
write UDFs to parse them into

into the following:

Field1 Field2 Field3
-------- --------- ----------------
East 123 ABC Street
100 Apple Road
- Grasshopper -

[Lyle Fairfield]

On Jan 27, 4:33 pm, "SQL Learner" <excelmodel...@ gmail.comwrote:

I am not using SQL Server as the backend and no network is involved.
I have the tables all in Access. The reason why the database is not
well designed is because I just took the data from csv files and
dumpped them into Access. I did not design the database.

It's quite likely that you will have to make several passes to
"stroke" your data.

Here's a start in a way I would try. It might not do what is needed.
It might need revision.
It's intended to filter the largest (in characters) numeral in an
address field and to put it into a (type long integer) StreeNumber
field. It's intended to put a null in the field if there is no number
in the address fields, in case some previous effort has made it zero,
or something else that's inappropriate.

For northwinds 830 Customer records this procedure takes 0.01 (one one
hundredth) seconds with my Intel Pntium M 1.5 Processor and 512 meg of
ram. Part of that is taken initializing the Regular expression so we
might expect it to reduce its average time per record as the number of
records increases. But there's no guarantee of anything like that.
Access and VBA and JEt are mysterious beasts and there's no guarantee
that at record 831 there will not be some problem with a cache, or a
disk write or read or whatever and the whole thing will slow to a
crawl.

This is not old proven code. It's new and has been created right now
while I am packing (for that vacation I mentioned). It's very likely
that it can be improved.

If you decide to run it though, please run the whole thing not part of
it. It's unlikely that part of it will behave well. If you are a Reg
Exp/ SQL fiend go right ahead and inporve it. I'll be happy. As for
the other parts, (they're what re(1) to re(3) are for) you'll need
someone else to step up to the plate. I'll be surf boarding.

Private Declare Function GetTickCount& Lib "kernel32" ()

Dim re(0 To 3) As Object
Dim matches As Object
Dim match As Object

Public Sub ParseAddress()
Dim t As Long
t = GetTickCount
On Error GoTo ParseStreetNumb erErr
InitializeRegul arExpressions
DBEngine(0)(0). Execute "UPDATE Customers SET StreetNumber =
GetStreetNumber (Address)"
Debug.Print GetTickCount - t
ParseStreetNumb erExit:
TerminateRegula rExpressions
Exit Sub
ParseStreetNumb erErr:
MsgBox "Error Number: " & Err.Number & vbNewLine &
Err.Description , vbCritical
Resume ParseStreetNumb erExit
End Sub

Private Sub InitializeRegul arExpressions()
Dim z As Long
For z = 0 To 3
Set re(z) = CreateObject("V BScript.RegExp" )
re(0).Pattern = "\d+"
re(0).Global = True
Next z
End Sub

Private Sub TerminateRegula rExpressions()
Erase re
End Sub

Public Function GetStreetNumber (ByVal vAddress$)
Dim temp$
Dim streetNumber$
Set matches = re(0).Execute(v Address)
For Each match In matches
temp = match.Value
If Len(temp) Len(streetNumbe r) Then _
streetNumber = temp
Next match
If Len(streetNumbe r) 0 Then
GetStreetNumber = CLng(streetNumb er)
Else
GetStreetNumber = Null
End If
End Function

[Larry Linson]

Looks to me as if Lyle went out of his way to code and test something for
you. I'm not anywhere near as familiar with Regular Expressions as he is.
Thus, I think I will just say that Lyle is a highly experienced, very
capable developer in Access and other development environments. I suggest
you carefully review, and try, what he provided.

Larry Linson
Microsoft Access MVP


"SQL Learner" <ex***********@ gmail.comwrote in message
news:11******** **************@ q2g2000cwa.goog legroups.com...

Hi Larry,

I am not using SQL Server as the backend and no network is involved.
I have the tables all in Access. The reason why the database is not
well designed is because I just took the data from csv files and
dumpped them into Access. I did not design the database.

You raised a good print regarding the free form address. We would try
to convert all the "not well structured" data into a better structured
data before we run our update query. For example, we would
standardized all the words used in the addresses by, for example,
convert "E." or "E" to "East" or "Five" to "5".

The objective is to get as much update as we can. The "Left over"
will need to be handled manually.
So given these fact and assume that there will be only two type of
structures: "East 123 ABC Street" and "100 Apple Road", how can I
write UDFs to parse them into

into the following:

Field1 Field2 Field3
-------- --------- ----------------
East 123 ABC Street
100 Apple Road
- Grasshopper -

[Lyle Fairfield]

Not surf boarding if there is such a thing ... I'm afraid of the
surf ... sail boarding or wind surfing.

[Gord]

SQL Learner said:

>So given these fact and assume that there will be only two type of
structures: "East 123 ABC Street" and "100 Apple Road", how can I
write UDFs to parse them into

into the following:

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

and then Lyle Fairfield said:

>Here's a start in a way I would try.

[suggested a solution using Regular Expressions]

As for the other parts, (they're what re(1) to re(3) are for) you'll
need someone else to step up to the plate.

I'll push it ahead a bit further by splitting up the three parts.
(Note that it does not find the number with the most digits as Lyle's
code does, it just finds the first number in the string.)
Sub foo()
' requires reference to:
' Microsoft VBScript Regular Expressions 5.5

' output:
'
' "East","123","A BC Street"
' "","100","A pple Road"
' "","1410"," 33rd Ave NW"

Dim re As RegExp, matches As MatchCollection , match As match
Dim TestValues As Variant, AddrLine As Variant

TestValues = Array( _
"East 123 ABC Street", _
"100 Apple Road", _
"1410 33rd Ave NW")

For Each AddrLine In TestValues
Set re = New RegExp
' late-binding syntax is...
'Set re = CreateObject("V BScript.RegExp" )

re.Pattern = "^(.*?)(\d+)\s+ (.*)$"
Set matches = re.Execute(Addr Line)
If matches.Count = 0 Then
Debug.Print "[no match]"
Else
For Each match In matches
Debug.Print """" & Trim(match.SubM atches(0)) & _
""",""" & Trim(match.SubM atches(1)) & _
""",""" & Trim(match.SubM atches(2)) & _
""""
Next
End If

Set match = Nothing
Set matches = Nothing
Set re = Nothing
Next
End Sub

On Jan 27, 5:58 pm, "Lyle Fairfield" <lylefairfi...@ aim.comwrote:

On Jan 27, 4:33 pm, "SQL Learner" <excelmodel...@ gmail.comwrote:

I am not using SQL Server as the backend and no network is involved.
I have the tables all in Access. The reason why the database is not
well designed is because I just took the data from csv files and
dumpped them into Access. I did not design the database.It's quite likely that you will have to make several passes to

"stroke" your data.

Here's a start in a way I would try. It might not do what is needed.
It might need revision.
It's intended to filter the largest (in characters) numeral in an
address field and to put it into a (type long integer) StreeNumber
field. It's intended to put a null in the field if there is no number
in the address fields, in case some previous effort has made it zero,
or something else that's inappropriate.

For northwinds 830 Customer records this procedure takes 0.01 (one one
hundredth) seconds with my Intel Pntium M 1.5 Processor and 512 meg of
ram. Part of that is taken initializing the Regular expression so we
might expect it to reduce its average time per record as the number of
records increases. But there's no guarantee of anything like that.
Access and VBA and JEt are mysterious beasts and there's no guarantee
that at record 831 there will not be some problem with a cache, or a
disk write or read or whatever and the whole thing will slow to a
crawl.

This is not old proven code. It's new and has been created right now
while I am packing (for that vacation I mentioned). It's very likely
that it can be improved.

If you decide to run it though, please run the whole thing not part of
it. It's unlikely that part of it will behave well. If you are a Reg
Exp/ SQL fiend go right ahead and inporve it. I'll be happy. As for
the other parts, (they're what re(1) to re(3) are for) you'll need
someone else to step up to the plate. I'll be surf boarding.

Private Declare Function GetTickCount& Lib "kernel32" ()

Dim re(0 To 3) As Object
Dim matches As Object
Dim match As Object

Public Sub ParseAddress()
Dim t As Long
t = GetTickCount
On Error GoTo ParseStreetNumb erErr
InitializeRegul arExpressions
DBEngine(0)(0). Execute "UPDATE Customers SET StreetNumber =
GetStreetNumber (Address)"
Debug.Print GetTickCount - t
ParseStreetNumb erExit:
TerminateRegula rExpressions
Exit Sub
ParseStreetNumb erErr:
MsgBox "Error Number: " & Err.Number & vbNewLine &
Err.Description , vbCritical
Resume ParseStreetNumb erExit
End Sub

Private Sub InitializeRegul arExpressions()
Dim z As Long
For z = 0 To 3
Set re(z) = CreateObject("V BScript.RegExp" )
re(0).Pattern = "\d+"
re(0).Global = True
Next z
End Sub

Private Sub TerminateRegula rExpressions()
Erase re
End Sub

Public Function GetStreetNumber (ByVal vAddress$)
Dim temp$
Dim streetNumber$
Set matches = re(0).Execute(v Address)
For Each match In matches
temp = match.Value
If Len(temp) Len(streetNumbe r) Then _
streetNumber = temp
Next match
If Len(streetNumbe r) 0 Then
GetStreetNumber = CLng(streetNumb er)
Else
GetStreetNumber = Null
End If
End Function

[SQL Learner]

Thank you for your code, Lyle!

I tried to use it but the GetStreetNumber function kept returned a
null value and hence, the Update SQL

DBEngine(0)(0). Execute "UPDATE Customers SET StreetNumber =
GetStreetNumber (Address)"

cannot be executed properly.

Also, let's assume the function does return value other than null (or
empty), the SQL statement will only update the records based on that
particular value, but how about others? In addition, do we need a
loop to loop through all the records? May be I am just not getting
your code logic right. Please advice.

- Grasshopper -