Improve Efficiency (1 Viewer)

Acropolis · Jul 22, 2015

Hi,

I have the following function, which generates an 8 digit closure code which is issued to engineers when they close down a job.

Before it issues the code, it checks to see if that code has already been issued previously or not.

I have it generating a random code, so that sequential numbers aren't used and therefore the engineers can't take a guess as the closure code.

It works absolutely fine, but the first job that is closed down when the system is first used, is really slow (20 - 30 seconds) to check the if the code has been used previously, before moving onto the rest of the code. Once it has done this once it is nice and quick after that until you close and reopen the system.

What could I do to speed this up? Would doing a rs.RecordCount be quicker that a DCount?

Thanks

Code:

Function ClosureCodeGen()
Dim Code1 As Integer
Dim Code2 As Integer
Dim code3 As Integer
Dim code4 As Integer
Dim code5 As Integer
Dim code6 As Integer
Dim code7 As Integer
Dim code8 As Integer

LabelA: Code1 = Int((9 - 0) * Rnd + 1)
        Code2 = Int((9 - 0) * Rnd + 1)
        code3 = Int((9 - 0) * Rnd + 1)
        code4 = Int((9 - 0) * Rnd + 1)
        code5 = Int((9 - 0) * Rnd + 1)
        code6 = Int((9 - 0) * Rnd + 1)
        code7 = Int((9 - 0) * Rnd + 1)
        code8 = Int((9 - 0) * Rnd + 1)
        Code = Code1 & Code2 & code3 & code4 & code5 & code6 & code7 & code8
If DCount("[ID]", "tblClosureCodes", "[ClosureCode] = " & Code & "") = 0 Then
    DoCmd.RunSQL ("INSERT INTO tblClosureCodes (ClosureCode) VALUES (" & Code & ")")
Else
    GoTo LabelA
End If
'ClosureCodeGen = Code
TempVars("tmpClosureCode") = Code
End Function

Ranman256 · Jul 22, 2015

DCOUNT takes a lot of time. I dont see why you need this.
Just lookup the code to see if you used it with
DLOOKUP()
could be faster, but it may not have to check every record.

Minty · Jul 22, 2015

Yes a RS count would be considerably quicker I believe, as the select query would be better optimised.

plog · Jul 22, 2015

The most efficient way would be to use autonumbers. Stay with me.

You want both a unique and 'random' identifier. You've solved the random part, but the unique is slowing you down. And its going to get slower as you add more numbers to your database and it takes more effort to find one not in use. So, tackle the unique part first (with an autonumber) then tackle the random.

Assign every record an autonumber, but don't give that value to the user instead, you hash it and then give them the hash. This means you build your own hash function (https://en.wikipedia.org/wiki/Hash_function) and reverse hash function. The hash function (F1) takes a number and returns a seemingly random one based on the input number. Then you build a reverse hash function (F2), which undoes the hash and returns your initial value.

Here's a very simple example.

F1 converts a base10 number to binary
F1(3) returns 11
F1(9) returns 101
F1(68) returns 1000100

So, 3, 9 and 68 are autonumbers from the database, but the users see 11, 101 and 100100 (again a very simple example). Then if you need to unconvert what the user has you use the reverse function:

F2 converts a binary number to base10
F2(11)=3
F2(101)=9
F2(1000100)=68

With that method, you no longer have to check for uniqueness, you simply take the record's autonumber value and convert it. Again, a very simple example. Your hash can be more complex as long as every unique value sent to F1 returns a unuqie value and can be undone by F2.

CJ_London · Jul 22, 2015

also make sure your closurecode field is indexed

can also try a recordset which should be faster

Code:

dim rst as dao.recordset
  
set rst=currentdb.openrecordset(" SELECT [ID] FROM tblClosureCodes WHERE [ClosureCode] = " & Code)
if rst.eof then 'code does not exist
       ...
       ...
  
 end if
 set rst=nothing

vbaInet · Jul 22, 2015

Select Count(id) in the recordset sql and return that field value. That's lightening fast. Include a forward only option too.

Acropolis · Jul 22, 2015

I tried DLookup and the difference between that and DCount wasn't much.

Also tried receordset, which was about 3 times longer! Index is in place on the ClosureCode column in the DB.

Tried it with rsCode.Record = 0 and rsCode.EOF and neither made any difference, have updated the linked table and that doesn't make any difference.

Got to be something simple I'm missing somewhere.

Code now looks like this:

Code:

Function ClosureCodeGen2()
Dim Code1 As Integer
Dim Code2 As Integer
Dim code3 As Integer
Dim code4 As Integer
Dim code5 As Integer
Dim code6 As Integer
Dim code7 As Integer
Dim code8 As Integer
Dim rsCode As DAO.Recordset

LabelA: Code1 = Int((9 - 0) * Rnd + 1)
        Code2 = Int((9 - 0) * Rnd + 1)
        code3 = Int((9 - 0) * Rnd + 1)
        code4 = Int((9 - 0) * Rnd + 1)
        code5 = Int((9 - 0) * Rnd + 1)
        code6 = Int((9 - 0) * Rnd + 1)
        code7 = Int((9 - 0) * Rnd + 1)
        code8 = Int((9 - 0) * Rnd + 1)
        Code = Code1 & Code2 & code3 & code4 & code5 & code6 & code7 & code8
Set rsCode = CurrentDb.OpenRecordset("SELECT ID FROM tblClosureCodes WHERE ClosureCode = " & Code & "")
If rsCode.EOF Then
    DoCmd.RunSQL ("INSERT INTO tblClosureCodes (ClosureCode) VALUES (" & Code & ")")
Else
    GoTo LabelA
End If
'If IsNull(DLookup("[ID]", "tblClosureCodes", "[ClosureCode] = " & Code & "")) Then
'    DoCmd.RunSQL ("INSERT INTO tblClosureCodes (ClosureCode) VALUES (" & Code & ")")
'Else
'    GoTo LabelA
'End If
'ClosureCodeGen = Code
TempVars("tmpClosureCode") = Code
Set rsCode = Nothing
End Function

I'm no expert on Indexes by a long stretch, so maybe I have done that incorrectly (if that's possible). I use SQLyog to manage the DB and have added an Index to the Table, given it an Index Name and the Columns I have selected just the ClosureCode column and tried with both ID and ClosureCode selected and it didn't make any difference. Have tried with IndexType as Unique and also left blank and made no difference.

Improve Efficiency (1 Viewer)

Acropolis

Registered User.

Ranman256

Well-known member

Minty

AWF VIP

plog

Banishment Pending

CJ_London

Super Moderator

vbaInet

AWF VIP

Acropolis

Registered User.

Similar threads

Users who are viewing this thread