Welcome to MapForums!

Register, sign in, or use Facebook Connect above to join in and participate in the forum.

When you are signed in, this message, the ads in this row, and the red-underlined link ads all go away.

Subscribe to receive our newsletter.
Subscribe Unsubscribe
Results 1 to 6 of 6

How do I speed up this code?

This is a discussion on How do I speed up this code? within the MapPoint Desktop Discussion forums, part of the Map Forums category; I am new to programming MapPoint and I would like to speed up the following code that I have written ...

  1. #1
    RichardD is offline Junior Member White Belt
    Join Date
    Oct 2004
    Posts
    5

    How do I speed up this code?

    I am new to programming MapPoint and I would like to speed up the following code that I have written using Excel VBA (this is just an extract not the complete subroutine)

    Set objRS = objDataSet.QueryAllRecords

    'Loop through all the records (if any)
    ' Symbol 336 is first of Custom set
    Do Until objRS.EOF
    Set objFldStatus = objRS.Fields.Item("Status")
    Set objFldType = objRS.Fields.Item("Type")
    Select Case objFldStatus.Value
    Case "M"
    Select Case objFldType.Value
    Case "FG"
    objRS.Pushpin.Symbol = 16 ' black dot
    Case "MC"
    objRS.Pushpin.Symbol = 23 ' purple dot
    Case "RC"
    objRS.Pushpin.Symbol = 17 ' red dot
    Case "TC"
    objRS.Pushpin.Symbol = 22 ' green dot
    Case "SC"
    objRS.Pushpin.Symbol = 21 ' cyan dot
    Case "FT"
    objRS.Pushpin.Symbol = 20 ' blue dot
    Case Else
    objRS.Pushpin.Symbol = 254 ' question mark
    End Select
    Case "D"
    objRS.Pushpin.Symbol = 336 ' transparent
    Case "X"
    objRS.Pushpin.Symbol = 336 ' transparent
    Case Else
    objRS.Pushpin.Symbol = 255 ' exclamation mark
    End Select

    'Move to the next record (or to EOF)
    objRS.MoveNext
    Loop

    I have also tried using the if ... then ... else if construct but I did not detect much reduction in execution time when processing 10,000 records.

    As I do not have VB6 nor do I have Microsoft Office XP Developer I am unable to create a DLL

    Richard

  2. #2
    Wilfried is offline Senior Member Black Belt
    Join Date
    Nov 2004
    Location
    Belgium
    Posts
    2,433
    Hi,

    I dont think you can speed this code up. If/else does the same as a Case statement, also you do a 1 byte compare (or 2), so that is also fast because it is only CPU register comparisation.

    The problem is probably also not because you loop in 10000 records (you can test this of course), but because you are redisplaying all the pushpins (changing the symbols), and that is what take time.

    So what you eventually can do is process the whole data and whilst processing check which symbol you want and put the pushpin on mappoint, so then it is only 1 loop while it is displayed and the whole time will be exact the time needed for display.

    I hope my explanation make sence...

  3. #3
    RichardD is offline Junior Member White Belt
    Join Date
    Oct 2004
    Posts
    5
    Quote Originally Posted by Wilfried
    ... So what you eventually can do is process the whole data and whilst processing check which symbol you want and put the pushpin on mappoint, so then it is only 1 loop while it is displayed and the whole time will be exact the time needed for display...
    Wilfried,

    Thank you for the quick reply - much faster than MapPoint

    I will do some tests to see where the time is being consumed by removing various bits of the code.

    In the quoted paragraph from your reply are you suggesting that I should check to see if the symbol already exists and only update it if it is different? I have tried running without the map display on and I have not noticed any significant different in execution time. MapPoint certainly uses 100% of the available CPU time.

    Richard

  4. #4
    Wilfried is offline Senior Member Black Belt
    Join Date
    Nov 2004
    Location
    Belgium
    Posts
    2,433
    Hi Richard,

    n the quoted paragraph from your reply are you suggesting that I should check to see if the symbol already exists and only update it if it is different?
    No, I'm sorry but my English is not so good to express myself sometime

    what I meant was following:

    Now (as far as I can conclude from your code) you trow all positions into mappoint database. Then you check each record and eventually update symbol. This is time consuming.

    I meant that you go tru your own data first in VBA, and for each record assign the right symbol and trow the symbol to mappoint.

    This way all the work is done in one single loop, and you are not depending on the speed of mappoint.

    Is this more clear what I mean ?

  5. #5
    Winwaed's Avatar
    Winwaed is offline Mapping-Tools.com Black Belt
    Join Date
    Feb 2004
    Location
    Irving,Texas
    Posts
    1,859
    Blog Entries
    60
    Wilfried has a good overall idea. If it fits what you're doing, it will be the fastest option.

    If you must use this code, there are three things you can try:

    - Simplify your comparisons. Can you work with numbers for the first 2 chars?
    If you use numbers, you can use a Look Up Table.

    - Are some options more likely than others?
    List them in order of likelihood. The most likely first. This removes many unnecessary comparisons.
    Also investigate a binary search instead of a linear one.

    - Use the VB6 "With...End" statement. This avoids a lot of VB's member lookup processing time.


    Richard
    Winwaed Software Technology LLC
    http://www.winwaed.com
    See http://www.mapping-tools.com for MapPoint Tools

  6. #6
    RichardD is offline Junior Member White Belt
    Join Date
    Oct 2004
    Posts
    5
    Wilfried and Richard,

    Many thanks for your help. I now understand that it is best to prepare my data in Excel prior to invoking MapPoint. The easiest change for me is to prepare a Symbol field in my Excel spreadsheet and use this to set the pushpin symbol. It is a pity that Microsoft do not recognise a field name of Symbol and use this directly. This would get round the restriction of 8 different pushpins of the Multiple Symbol Map; it is not always necessary to have a legend.

    Am I correct in thinking that an XLA file executes more quickly than the same code run from within the VBA Editor? It seems to for me. I say seems because the timings that I have taken vary such a lot.

    Richard

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Open Map Speed
    By Yazzy in forum MapPoint Desktop Discussion
    Replies: 1
    Last Post: 06-27-2005, 11:52 AM
  2. Speed of the service
    By sbedin in forum Bing Maps and MapPoint Web Service
    Replies: 0
    Last Post: 12-21-2004, 11:13 AM
  3. Excel VBA or VB6 For Speed
    By DavidP in forum MapPoint Desktop Discussion
    Replies: 3
    Last Post: 08-14-2004, 06:50 PM
  4. Speed Print out
    By Anonymous in forum MapPoint Desktop Discussion
    Replies: 0
    Last Post: 12-26-2003, 05:54 AM
  5. How to speed up MapPoint
    By Dean in forum MapPoint Desktop Discussion
    Replies: 0
    Last Post: 08-30-2002, 11:16 AM

Tags for this Thread

Posting Permissions

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


1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36