Execute SQL to MySQL

[Jalz]

Hi Guys,

I have 8500 records and 3 text(indexed) fields in my fmp file I need to transfer over to a table in MySQL. WHat is the most effiecient way of doing this in FMP 8.5. I have looped through each record and executed an insert into the MySQL database. Although this works, it is taking about 25 minutes to perform....

So my next concept was to loop through 8500 records and make a list of records with an insert statement as the heading all in a global field. This taking roughly about 7 minutes to transfer..its the loop that seems to be taking its time.

Anyone have any other methods they can think of where I could transfer that amount of data quickly from FM to MySQL in 8.5 (I do have v9, but need to do more testing before we deploy).

[Brian C]

If you were using v9 you could place an SQL TO directly into your filemaker file where your data exists. You just need the driver for the particular SQL product installed on the machine you are doing it from. You could then create a looping script to transfer the data to your SQL table one record at a time...

[Genx]

If its the loop that's taking the time, just ensure you have a freeze window step in there.

[Brian C]

With v9 I have found that freeze window does not work as well as Toggle Window(hide) and Toggle Window(restore)

For some of my processes - this seems to make a world of difference...

[Genx]

I don't know... freeze window freezes the window to stop the records refreshing and the FM Screen redrawing: the idea is to stop unstored calcs recalcing and data being unnecessarily loaded to the screen.

Whether you hide the window or freeze it, I believe it would probably have the same effect.

[Brian C]

Test it for yourself and see. I just did in v9 to make sure I was not confusing it with v6.

Depending on what you are doing - using hide window will save you more time than freeze window.

In my sample file using 100,000 records and only 1 field it shaves off about 1-2 seconds. As soon as you introduce more fields and calculations etc that can be affected - the time it saves you may be increased dramatically.

Time from looping script that uses set field:

32 Freeze

30 Hide

Time from import script that deletes old set and imports new set:

freeze 28

hide 27

Edited July 26, 2007 by Guest

[Genx]

Righto, I believe you... Though seeing as you have the environment set up - could you try it with 2 fields (just for my curiosity).

Thanks Brian.

[Brian C]

I was wrong and you are right.

I used one of my scripts in a real table of data and only 2 fields were being updated in the process.

The difference between freeze and hide is negligible in version 9.

It is interesting to note however that if the layout is in list mode the time it takes to execute some types of scripts increases. In my example the time it takes is almost 2 minutes longer while using list view.

In my sample using 143,000+ records (real data) in a table consisting of roughly 100+ fields where only 2 fields are being updated:

Form View:

Freeze 3m 4s

Hide 3m 4s

List View

freeze 4m 42s

hide 4m 40s

I must have been going off of the assumption of how earlier versions acted. Maybe I am just remembering how the preview release of 9 acted or maybe I am just remembering this from v6... Oh well.

Lesson learned.

Lesson also learned to execute complex scripts while the layout is in form mode and not list mode.

[Genx]

Lol - I wasn't actually saying "I'm right you're wrong" - though I can't see logically why the hide would perform faster unless the FM Engineers did something kinda weird...

I actually wanted to know the results of your second test so i could know for my own benefit and switch to hiding the window in case that was faster.

As for list view - lol can't explain that one.

Thanks Brian.

[Brian C]

;) I just felt I needed to eat a little crow because I was saying something without having checked it on the current version I was using. (I was so sure too! ) LOL. I know that for one of the versions it made a HUGE difference and I had adopted using hide over freeze for that very reason. In any case I am fully upgraded to v9 for development.

As for the reason why list mode slows things down - It has to do with a periodic update to the window for the scroll bar on the side. Even if Freeze Window is used, the slider scrolls down and gives you an idea how far down the list the script is while it is running. I suspect the same is happening while the window is hidden which would explain the increase in time and the fact that they both match in times.

[Jalz]

Thankyou very much guys,

Ive managed to populate 8500 recoords with three text fields from FMP 8.5 to MySQL in 7 minutes. Ive used the Freeze command which does speed things up. I was thinking of using the getnthrecord and a recursive custom function to obtain the values in one global field before feeding it in MySQL. Have any of you guys fed thousands of records with a single query using the ExecuteSQL command?

[Genx]

No, haven't really tried with that many before... though you could just use your script to collect the data construct a SQL query in a variable and execute that - not sure how well it will work though, or even if FM allows for that large a command to be executed.