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 5 of 5

Faster was to store MapPoint Recordset to SQL Server ?

This is a discussion on Faster was to store MapPoint Recordset to SQL Server ? within the MapPoint Desktop Discussion forums, part of the Map Forums category; Following a .QueryPolygon method call I populate a MapPoint Recordset. To persist this data into SQL server, I find myself ...

  1. #1
    tfmiltz is offline Member Green Belt
    Join Date
    Feb 2005
    Posts
    52

    Question Faster was to store MapPoint Recordset to SQL Server ?

    Following a .QueryPolygon method call I populate a MapPoint Recordset.

    To persist this data into SQL server, I find myself iterating through many SQL UPDATE transactions.

    I am using SQL Server 2000, however, does anyone know if there is a more innate, or built in means to push the contents of a returned query in MapPoint to a table in SQL server ? 2000, 2005 OR 2008 ?

    When I have some 13,000 pts returned, it seems it can take forever.

    I SUPPOSE I could dump to a flat text file ? Then use bulk insert/DTS or something, but I'm surprised there isn't a faster means for MapPoint Recordset to be transferred to a SQL server table, or any database format for that matter.

    Tim Miltz
    Jr. Sr Mid-level something or another in a field that's changing so far, I forgot what I was even referring to here.

  2. #2
    Eric Frost's Avatar
    Eric Frost is offline Administrator Black Belt
    Join Date
    Jul 1992
    Posts
    5,094
    Blog Entries
    4

    Re: Faster was to store MapPoint Recordset to SQL Server ?

    I wonder if you can copy a MapPoint Recordset to another kind (DAO recordset) then you could use something like Recordset Converter --
    Download Recordset converter (MDB/DBF export), Recordset converter (MDB/DBF export) 1.0 Download
    Otherwise, yes I think your Text file idea would be good to try, there has to be less overhead writing to a text file.

    Eric

    P.S. Congrats on your 50th post. Took you 4.5 years! hehe
    ~ Order MapPoint MapPoint 2013 Here and Get Free Access to the MapForums Downloads ~
    ~~
    ~ Upgrade to Get Access to the MapForums Downloads ~

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

    Re: Faster was to store MapPoint Recordset to SQL Server ?

    Hi,

    It is not clear to me in your mail what exacly takes so long time. Is it iterating through the mappoint recordset or is it the sql 'update'?

  4. #4
    Winwaed's Avatar
    Winwaed is offline Mapping-Tools.com Black Belt
    Join Date
    Feb 2004
    Location
    Irving,Texas
    Posts
    1,859
    Blog Entries
    60

    Re: Faster was to store MapPoint Recordset to SQL Server ?

    And are you using MapPoint in thread or out of thread?

    If out of thread, thenr you program is probably spending most of its time waiting on both SQL Server and MapPoint. In which case adding some asynchronous access to both would result in a big speed up. Even with one core, your program could be calling MapPoint whilst waiting for SQL Server - and vice versa. A simple 1-in-1-out queued thread model should do the job.

    Also, I'm assuming SQL Server is running locally? If it is not, and you are running over a network, then that is thee reason for slow processing.


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

  5. #5
    tfmiltz is offline Member Green Belt
    Join Date
    Feb 2005
    Posts
    52

    Smile Re: Faster was to store MapPoint Recordset to SQL Server ?

    To Wilfried:

    Yes, it is that I'm cycling through each result returned from my query, firing off an UPDATE transaction PER recordset tuple/row item.

    That's what I mainly was looking for - some innate interoperability between MP and SQL server.


    To Winwaed -

    In process. Also, SQL server may or may not be local, I don't have control over that. Client does.

    When DB is remote, I've had to skip DTS options, as I'm not aware that I can call on DTS object model if SQL Server is remote, but in a perfect world I'd say DTS this and accept that as optimum speed.

    To both:

    You two are the best !

    Because SQL server is sometimes NOT local, I do not believe I can call on any bulk insert, or DTS features, I suppose I could branch and say - if local, use DTS, if not, pay long distance fee's ! (time wise that is- kind of a joke there).

    CRAZIEST solution might be push flat file OVER to SQL machine, then have a predefined DTS script (I prefer the OM though), and do it that way.

    I GUESS what I was hoping for was some innate/interopability between SQL Server and MapPoint, even OVER the network such that I could say to MapPoint - HERE, you have the query results, YOU go dump this to a table behind the scenes - as I am guessing MS MP programmers could optimize such an iteration even of UPDATES, better than I can.

    Then again, rethinking all of this, I suppose it's MOSTLY just a SQL server issue on what's the fastest way to dump some data through updates to keep that as fast as possible. Maybe I can re-explore some things I've forgotten on SQL optimization, I recall turning off logging helps, etc.

    THANK you MUCH - everyone for the response. Although I don't visit MapPoint Forums often enough, it's ALWAYS golden as to community my book, in fact ? in all my programming over the years - I like this forum the best.

    Tim Miltz
    Some yayhoo developer that should have gone into writing children's stories ! But still loves automating thought processae using the ole bit pushin' box.
    We'll come back to the mainframe full circle in 2015 - you'll see ! jk

    I can see it now "Now, with Cloud computing, you get what's called a time share on the cloud" lol.

    OK OK enough with the taglines ! (I don't get out much, in fact, AT ALL).

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Gotta be a faster way to export to excel?
    By alexramo in forum MapPoint Desktop Discussion
    Replies: 7
    Last Post: 12-08-2009, 10:03 AM
  2. Site Selection for Retail Game Store with Microsoft MapPoint
    By Eric Frost in forum News and Announcements
    Replies: 0
    Last Post: 12-21-2008, 08:54 AM
  3. Sales per store
    By satyamkoli in forum MapPoint Desktop Discussion
    Replies: 1
    Last Post: 12-07-2008, 08:59 AM
  4. Update Mappoint by GPS faster then 15 seconds, how?
    By Anonymous in forum MapPoint Desktop Discussion
    Replies: 2
    Last Post: 01-11-2003, 09:15 PM
  5. Deriving a recordset from a mappoint recordset
    By RichardHayes in forum MapPoint Desktop Discussion
    Replies: 0
    Last Post: 09-06-2002, 02:44 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 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57