Infinite Loop?

A place to discuss the implementation and style of computer programs.

Moderators: phlip, Moderators General, Prelates

dshizzle
Posts: 116
Joined: Thu Feb 09, 2012 3:45 am UTC

Infinite Loop?

Postby dshizzle » Fri May 18, 2012 2:03 pm UTC

So I'm working on some code which will look at two tables. It will take the first string value from one table and then compare it with all other values of the other table finding the closest match. It will then cycle to the second string value from the first table and repeat the process. Each of the tables has about 1500 entries. Currently all the code does is lookup the string values, but when I run the code Access freezes up. I can't seem to find an error in the loop, but I cant imagine this process taking more than a couple minutes. Any thoughts?

Code: Select all

Option Compare Database

Private Sub Go_Click()

Dim tableOne, tableTwo As String
Dim tableOneId, tableTwoId As Integer
Dim strOne, strTwo As String
Dim tableOneCount, tableTwoCount As Integer         'Declare Variables
Dim s As Variant
Dim t As Variant
Dim d As Variant
Dim m, n
Dim i, j, k
Dim a(2), r
Dim cost
Dim maxIdOne, maxIdTwo As Integer
Dim mydb As Database
Dim rst As DAO.Recordset
Dim Distance, MinDistance

tableOne = Me.tableOne                              'Grab Table Values from Form
tableTwo = Me.tableTwo                              ' And Initialize Variables

maxIdOne = DMax("ID", tableOne)
maxIdTwo = DMax("ID", tableTwo)

Set mydb = CurrentDb()
Set rst = mydb.OpenRecordset("strValueAn")

tableOneId = 1
tableTwoId = 1

        Do While tableOneId < maxIdOne + 1          ' Outer Do Loop cycles through strOne values
       
                strOne = Nz(DLookup("strValue", tableOne, "ID = " & tableOneId), 0)
               
                Do While tableTwoId < maxIdTwo + 1  ' Inner Do Loop compares each strOne value against all strTwo values, looking for the closest match
               
                strTwo = Nz(DLookup("strValue", tableTwo, "ID = " & tableTwoId), 0)
               
               
                tableTwoId = tableTwoId + 1
               
                Loop
       
        tableTwoId = 1
       
        tableOneId = tableOneId + 1
       
        Loop


End Sub
Reality must take precedence over public relations, for nature cannot be fooled. - Feynman

dshizzle
Posts: 116
Joined: Thu Feb 09, 2012 3:45 am UTC

Re: Infinite Loop?

Postby dshizzle » Fri May 18, 2012 2:04 pm UTC

Also general advice/talk about Access is welcome, I'm new to the language, but it seems like you can do some pretty cool stuff with it.
Reality must take precedence over public relations, for nature cannot be fooled. - Feynman

dshizzle
Posts: 116
Joined: Thu Feb 09, 2012 3:45 am UTC

Re: Infinite Loop?

Postby dshizzle » Fri May 18, 2012 3:00 pm UTC

Well I tested it on a much smaller data set and it works fine, so I'm lead to assume that it must just be the size of the data that I'm working with.
Reality must take precedence over public relations, for nature cannot be fooled. - Feynman

User avatar
Xanthir
My HERO!!!
Posts: 5426
Joined: Tue Feb 20, 2007 12:49 am UTC
Location: The Googleplex
Contact:

Re: Infinite Loop?

Postby Xanthir » Fri May 18, 2012 4:35 pm UTC

This operation is O(n^2), so yeah, scaling will be your problem.
(defun fibs (n &optional (a 1) (b 1)) (take n (unfold '+ a b)))

User avatar
freakish777
Posts: 354
Joined: Wed Jul 13, 2011 2:14 pm UTC

Re: Infinite Loop?

Postby freakish777 » Fri May 18, 2012 5:26 pm UTC

dshizzle wrote:Access



Microsoft Access??

If so, that's your problem. Get SQL Server/Oracle PL-SQL instead, and port your data.


EDIT:

Something else to make sure you aren't doing (regardless of whether you're using Access/SQL/MySQL/or even Amazon's SimpleDB). When you grab your data from a database, make sure you're grabbing it only ONCE. There are so many ways to screw your DB calls up where instead of doing a table scan once (and grabbing all your data once), you're actually doing a table scan each time through your loop (so, O(wtf??) instead of O(n^2)). Not sure if Access has anything similar to SQL Profiler, but if it does, you may want to use it to ensure you only touch the database twice, once for each table you pull data from.

dshizzle
Posts: 116
Joined: Thu Feb 09, 2012 3:45 am UTC

Re: Infinite Loop?

Postby dshizzle » Mon May 21, 2012 2:21 pm UTC

Yeah, I'm thinking that this was probably the worst possible way to go about doing it. O(wtf) made me laugh.
Reality must take precedence over public relations, for nature cannot be fooled. - Feynman

dshizzle
Posts: 116
Joined: Thu Feb 09, 2012 3:45 am UTC

Re: Infinite Loop?

Postby dshizzle » Mon May 21, 2012 2:24 pm UTC

In addition, does anyone know of a simple way to set a variable equal to a recordset field value?
Reality must take precedence over public relations, for nature cannot be fooled. - Feynman

dshizzle
Posts: 116
Joined: Thu Feb 09, 2012 3:45 am UTC

Re: Infinite Loop?

Postby dshizzle » Mon May 21, 2012 2:25 pm UTC

Just got the answer, var = rst!fieldname.
Reality must take precedence over public relations, for nature cannot be fooled. - Feynman


Return to “Coding”

Who is online

Users browsing this forum: No registered users and 6 guests