A5V9 vs FM9 with MYSQL

[slamdunk]

This is my experience with testing a remote MYSQL database with A5V9 Active-links and Filemaker 9.

There are over 1500 records. I queried same records with both A5V9 and FM9. A5 took 7-9 sec while FM took only 1-2 sec. FM miles ahead for returning results.

I hope A5 developers will work on this in the near future.

[Tom Cone Jr]

Re: A5V9 vs FM9 with MYSQL

Slam, was this an apples to oranges comparison? i.e. were both queries run locally after selecting all the records from the server? were both queries run on the server? Or was the fm query run on the server while the A5 query was run locally after waiting for the local active-link table to be fully populated?

Would be helpful if you supplied the details of your comparison.

[Selwyn Rabins]

Re: A5V9 vs FM9 with MYSQL

This is my experience with testing a remote MYSQL database with A5V9 Active-links and Filemaker 9.

There are over 1500 records. I queried same records with both A5V9 and FM9. A5 took 7-9 sec while FM took only 1-2 sec. FM miles ahead for returning results.

I hope A5 developers will work on this in the near future.

there is not enough information to comment.
what was the query? need to know so that we can see if the query was run locally, or run on the server.
if the query is run locally, then all of the records have to be brought down to the client before before the query is run.

try this comparrison between filemaker and a5:
take a 1,000,000 record table (say mysql, or sql server - does not matter).
open a browse. sort on any column (for which an index was defined).

time taken by a5: couple of seconds - (depends on the speed of the database backend)

time taken by filemaker - serveral hours.

[slamdunk]

Re: A5V9 vs FM9 with MYSQL

Guys I'm not comparing oranges with apples. I like A5 over filemaker and would expect A5 to do better than FM. I havent tried sorting records yet as Selwyn suggestet.

Details of my tests are below:

1. zen cart mysql database hosted on a remote web server
2. products table
3. 1500 + records
4. queries run on "product model" locally (both A5(using default form) and FM)
5. same internet connection, same pc
6. A5 took 7-9 sec, FM took 1-2 sec

[DaveM]

Re: A5V9 vs FM9 with MYSQL

Slam,

If you post as Selwin asked, The guys at Alpha will be able to help make this better for you and the rest of us. That's a good thing.

Technicals are a good thing here.

[NoeticCC]

Re: A5V9 vs FM9 with MYSQL

This is my experience with testing a remote MYSQL database with A5V9 Active-links and Filemaker 9.

There are over 1500 records. I queried same records with both A5V9 and FM9. A5 took 7-9 sec while FM took only 1-2 sec. FM miles ahead for returning results.

I hope A5 developers will work on this in the near future.

If you want to run reports and queries against an SQL database, active link tables are a rather daft way of going about it, if you bothered to think about the differences between the uses of active link tables vs. SQL queries.

Why on earth not think a little and run a report directly against the SQL database for example? Active link tables are there for data that needs to be updated directly within a form

[MikeC]

Re: A5V9 vs FM9 with MYSQL

Just an observation...

Although it appears that "Slam" (for want of a real name) knows a bit about SQL and such, I think that it is obvious only to those who are acquainted with both SQL and Alpha that things should be done a certain way. Using words and phrases like "daft" , "if you bothered", and "not think" really makes even me not want to post a question or an observation if this was a typical response!!!

"Slam", This was not a typical response...believe me (especially for Andrea who does quite a bit of helping of late!)!!

[Bill Griffin]

Re: A5V9 vs FM9 with MYSQL

If you want to run reports and queries against an SQL database, active link tables are a rather daft way of going about it, if you bothered to think about the differences between the uses of active link tables vs. SQL queries.

Why on earth not think a little and run a report directly against the SQL database for example? Active link tables are there for data that needs to be updated directly within a form

Andrea,

I can certainly understand why Slam would THINK that using an active link table just as a a regular table, was not a, as you so eloquently stated, daft way of going about it. From Alpha's Web Site:

Active-link tables allow you to work with data in remote SQL databases. Active-link tables are like regular Alpha Five tables in that you can create Forms, Reports, Operations, etc.

Working with SQL is new for many of us and are going thru a learning curve. Instead of being critical of ones approach, perhaps you could enlighten us as the reasons why, and the methods that are better to accomplish things. For instance when an Alpha developer designs a report and has been for years in Alpha, why would we NOT want to run a report from Alpha against a SQL database?

Bill

[Tom Cone Jr]

Re: A5V9 vs FM9 with MYSQL

Bill, I think that's a good question. It's my impression that the key distinction here is whether the SQL query returns all the records (in which case Alpha will have to filter them once they're in the local table) or whether the SQL query itself asks the back end database to serve up only the desired (filtered) records. In this latter case the back end database runs what we think of as a query and sends done the pipe only the records that would be used in the report. Seeing examples of the wrong way to do it AND seeing examples of the right way to do it would be very helpful.

[NoeticCC]

Re: A5V9 vs FM9 with MYSQL

Andrea,

I can certainly understand why Slam would THINK that using an active link table just as a a regular table, was not a, as you so eloquently stated, daft way of going about it.

Well I wouldn't say eloquent.... I would like to belatedly apologise for the inappropriate rant, I should never type in a hurry.... :o

At any rate, I do think Alpha would do well to build in some sort of function that allows you to write reports etc. against active link tables - updatable ones at that - that block any attempts to refresh data continually*...

... and of course to extend the help files and provide examples of how each type of table can and SHOULD be used optimally.... because active link tables are not ideal for ALL cases...

Case 1: Reports

It is always faster to run a report against an SQL DB directly, at least in my experience. Define a named datasource for your active link table, then base the SQL report on the same datasource/definition. That way you only ever have to change ONE definition...

Case 2: Browses & Forms

Unless you need to update ALL or MOST rows in a table, I have found it is FAR more efficient to base an active link table only on the latest entries, or use arguments in the datasource definition... IDEALLY it should be possible to create an active link "shell" that can handle incrementing sequences and other things, but is there SOLELY for data entry for NEW records... this should speed up the opening of forms based on a-l tables TREMENDOUSLY...

* I believe this is what slows things down - however, running a report directly against an SQL table is heaps faster than against EITHER passive OR active link tables though

[Bill Griffin]

Re: A5V9 vs FM9 with MYSQL

I guess from my "Alpha" mindset, I was excited to see active link tables, I have just started playing with them. Tables with small amounts of records work well enough but when we are working with tables with over a million records, many things are very slow and "seem" unpractical. The queries are extremely fast. I have yet to be able to create a set based on tables with a large number of records (have been in contact with Selwyn regarding this) MY use of active link tables would be simply queries and reports, and almost never using them to update data. I certainly would like to know the best practices when using Alpha with SQL. It certainly was (and still is) my impression that using an active link table should be a valid way of doing everything we can with a native dbf file.

[Bill Griffin]

Re: A5V9 vs FM9 with MYSQL

Well I wouldn't say eloquent.... I would like to belatedly apologise for the inappropriate rant, I should never type in a hurry

I recall thinking when I first read your reply to Slam that you were perhaps having a bad day. Then you seemed to disappear for while (I thought the Board police came and dragged you away from your keyboard) :)

Thanks for your explanation. One of the great things about this board is that there are many people with a wide range of experience. I appreciate having all of the different points of view and approached to problem solving.

[NoeticCC]

Re: A5V9 vs FM9 with MYSQL

MY use of active link tables would be simply queries and reports, and almost never using them to update data. I certainly would like to know the best practices when using Alpha with SQL. It certainly was (and still is) my impression that using an active link table should be a valid way of doing everything we can with a native dbf file.

For that, I would suggest creating the active link tables based on a named datasource and then using the named datasource as a source for your reports etc... IF you need the active link tables at all...

[NoeticCC]

Re: A5V9 vs FM9 with MYSQL

I recall thinking when I first read your reply to Slam that you were perhaps having a bad day. Then you seemed to disappear for while (I thought the Board police came and dragged you away from your keyboard) :).

I was on holidays (hence the hurried, inappropriate response - I was trying to reply from a PC that I am not used to and couldn't connect again to edit the post) until last night! :o

[Bill Griffin]

Re: A5V9 vs FM9 with MYSQL

Good to have you back. Hope you had a nice restful holiday.