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.

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

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'?

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

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).