Results 1 to 13 of 13
  1. #1
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    343
    Thanks
    2
    Thanked 1 Time in 1 Post

    Slow Code? (2002)

    I have Access code that
    You know it's time to diet when you push away from the table and the table moves.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Slow Code? (2002)

    There is a lot of processing going on. I think code like this will always be rather slow.
    A slight improvement would be to put the assignments of mstrChar2 etc. after the If Left$(mstrName, 4) = "THE " Then ... End If block, so they don't get executed if they're not used.

  3. #3
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    343
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Slow Code? (2002)

    Thanks for the quick response and for confirming that it should be slow. I was hoping maybe my code could be improved. Thank you for your suggestion and I will move the assigns you mentioned.

    I also failed to mention I was using a local Access table, but not on the backend, and running with 500MB of Ram and 2GB of virtual memory. I think that would be large enough.

    In my other routine, ChkRemoveCommas, I plan to include about 3 instr functions to remove more than just commas. I think that would slow the performance even more.

    I don
    You know it's time to diet when you push away from the table and the table moves.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Slow Code? (2002)

    Regular expressions would make this fast and easy, although if you want all those count fields set, that would still take a while. To use regular expressions, you need to set a reference to the Microsoft VBScript Regular Expressions 5.5 library, but after that it's simple. The following routine will find and string out all instances of the search string, which in this case is a slash ("/") and a comma. The plus sign means that a sequence any of the search characters will be replaced by a single instance of strReplace. You could also search and remove commas and periods this way.

    <pre>Public Function RegularExpressions(ByVal strInput As String, Optional strReplace as String = " ") As String
    Static rex As New RegExp
    Dim strSearch As String

    ' search for a forward slash or a comma
    strSearch = "[/,]+"
    ' set the pattern to search for
    rex.Pattern = strSearch
    ' set the search to replace all
    rex.Global = True
    ' replace each instance of the pattern with a space and
    ' return the resulting string
    RegularExpressions = rex.Replace(Trim$(strInput), strReplace)

    End Function</pre>


    You could use this in a query as shown in the attachment.
    Charlotte

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Slow Code? (2002)

    10 hours seems awfully high to me also. You might try removing all indexes on the fields being changed, run your "clean-up", then recreate the indexes.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Slow Code? (2002)

    Just my <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15>
    -I don't see Dim statements for the variables mstrChar2, mstrChar3,...
    These variables will be treated as Variants. Shouldn't declaring them as Strings give a little speed gain ?

    -After each If Then statement, you call the function ChkRemoveCommas.
    If you call the function before the assigning of mstrChar2, mstrChar3,... , you could remove a number of or's in the If Then's
    Francois

  7. #7
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    343
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Slow Code? (2002)

    Thanks for everyone's input! I don't have any indexes in the table and the code I posted is only partial. I do mstrChar2 etc defined as module variables instead of dimming them.

    I did learn something important. I found out that the instr and replace functions seem very slooooow to me when used on a table of this size. It probably wasn't intended to be used on an entire table at once. As a work around to that problem I can export the table to Excel and change the special characters there much much faster than Access code.
    You know it's time to diet when you push away from the table and the table moves.

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Slow Code? (2002)

    When you examine every character is a field for several fields for every record in a large table, that will always be slow. One of the reasons I suggested regular expressions is that they are fast, can handle multiple characters or patterns at once, and don't require examining individual characters or substrings.
    Charlotte

  9. #9
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    343
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Slow Code? (2002)

    Hi Charlotte. Thanks for the feedback. I haven't used regular expressions before so this is something new to me. I don't mind learning new things so I'll check this out some more and see how far I can get. Using it in a query for massive changes would be helpful. Thanks for showing me something new!
    You know it's time to diet when you push away from the table and the table moves.

  10. #10
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    343
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Slow Code? (2002)

    Charlotte, I am using regular expressions and it worked. Then I realized I had to put it in an update query and put the function in the Update To cell. It took about a minute to run the conversion. Wow! I was very impressed. I still need to run VBA to swap the last and first names but now I don't have to do anything else but run the update query.

    Thank you so much for taking the time to reply to my post and pointing me to this neat tool. I've already used it on a second table.

    Thanks again!

    P.S. Computing from a boat floating somewhere in Los Angeles! For those who don't know, we're flooded out here. I miss the sun.
    You know it's time to diet when you push away from the table and the table moves.

  11. #11
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Salem, Oregon, USA
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Slow Code? (2002)

    I second Omega's "Wow!"
    I tried it out -- _sooo_ easy. Like Omega, I'll probably be using this tool now that I know about it!
    thx
    Pat

  12. #12
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Sydney, Australia, New South Wales, Australia
    Posts
    251
    Thanks
    0
    Thanked 4 Times in 4 Posts

    Re: Slow Code? (2002)

    You might also want to look into the 'split' and 'join' functions. They are a real treat for manipulating strings.
    Thank you Perl!
    Peter

  13. #13
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Salem, Oregon, USA
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Slow Code? (2002)

    Thank you, Peter!
    Pat

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •