FMP front-end, other database back-end?

I'm after some advice about separating the front-end (layouts,
buttons, etc) from the back-end (tables of data, doing queries).

I am very experienced in FileMaker but almost completely ignorant when
it comes to SQL, etc. What I want to get a feel for is whether it is
worth me investing the time in learning the skills that will enable me
to integrate FMP with other databases....

If the answer is 'yes', I'd appreciate tips on good resources or
courses for learning how to do this. Thanks!!


SOME MORE INFORMATION

I use FileMaker to create user-friendly databases for scientists. It
is fast for me in terms of development time and, of course, is very
easy for me to produce beautiful products that everyone loves.

However, some of the epidemiological and gene databases get very large
(often >1.5 million records). And, although search times are
tolerable, the show-stopper is the time that it takes to import the
data - especially when the fields are indexed (which, of course, they
have to be for reasonable search times). For example, a recent 1.4
million/36 field record import took 26 hours! The indexing also
gives file sizes of >2 GB which also starts to get restrictive.

Is there any way that I can keep the data outside of FileMaker (eg, in
plain text files or some other database) but use Filemaker to send
instructions to a something that might deal with big data-sets a bit
better, before display the found sets in FMP.

Re: FMP front-end, other database back-end?

In article <1192057825.780545.288350@y42g2000hsy.googlegroups.com>,
Gareth.Denyer@gmail.com wrote:

> I'm after some advice about separating the front-end (layouts,
> buttons, etc) from the back-end (tables of data, doing queries).
>
> I am very experienced in FileMaker but almost completely ignorant when
> it comes to SQL, etc. What I want to get a feel for is whether it is
> worth me investing the time in learning the skills that will enable me
> to integrate FMP with other databases....
>
> If the answer is 'yes', I'd appreciate tips on good resources or
> courses for learning how to do this. Thanks!!
>
>
> SOME MORE INFORMATION
>
> I use FileMaker to create user-friendly databases for scientists. It
> is fast for me in terms of development time and, of course, is very
> easy for me to produce beautiful products that everyone loves.
>
> However, some of the epidemiological and gene databases get very large
> (often >1.5 million records). And, although search times are
> tolerable, the show-stopper is the time that it takes to import the
> data - especially when the fields are indexed (which, of course, they
> have to be for reasonable search times). For example, a recent 1.4
> million/36 field record import took 26 hours! The indexing also
> gives file sizes of >2 GB which also starts to get restrictive.
>
> Is there any way that I can keep the data outside of FileMaker (eg, in
> plain text files or some other database) but use Filemaker to send
> instructions to a something that might deal with big data-sets a bit
> better, before display the found sets in FMP.

FMP 9 can integrate directly with SQL-type databases. Look at the
FileMaker web site to get more info on that.

--
For email, change to
Bill Collins

Re: FMP front-end, other database back-end?

Gareth Denyer wrote:

> I'm after some advice about separating the front-end (layouts,
> buttons, etc) from the back-end (tables of data, doing queries).

> Is there any way that I can keep the data outside of FileMaker (eg, in
> plain text files or some other database) but use Filemaker to send
> instructions to a something that might deal with big data-sets a bit
> better, before display the found sets in FMP.

In Filemaker 9, you can put an external SQL table on the relation graph
and use it as if it were a Filemaker table. You can even add fields with
unstored calculation.

I tried this on our site. Results weren't great.

There are databases FMP cannot handle this way. One of them is MS
Navision. You better ask FMP first about your data source.

I'm able to view my oracle table in Filemaker, but searches and display
take extremely long, even though my table are not as huge as yours. I
can view the same table in Access btw. It's a pain to work with, but
searches take milliseconds there.

My advice is you get a trial version of FMP 9 and try in on your site.
--
http://clk.ch

Re: FMP front-end, other database back-end?

wrote:

> For example, a recent 1.4
> million/36 field record import took 26 hours! The indexing also
> gives file sizes of >2 GB which also starts to get restrictive.

I've the same experience, with bases of 5 millions records and 5-10 GB.

Importing, reorganizing and indexing in FMP with one good script takes
36-72 hours (in fact, more than a whole weekend...) ; but after that,
work is in seconds.
If your import operations are infrequent, it's the best to do IMHO.
--
www.D-L-S.org

Re: FMP front-end, other database back-end?

> I tried this on our site. Results weren't great.
>
> I'm able to view my oracle table in Filemaker, but searches and display
> take extremely long, even though my table are not as huge as yours. I
> can view the same table in Access btw. It's a pain to work with, but
> searches take milliseconds there.

Thanks Christoph... that's exactly what I wanted to know. I tried
linking Access with FM6 and it was awful - very slow... but I might
give it anohter go based on your advice.

However, given Manet's comments below, it would seem that the only bit
where FMP is falling down is in the import/indexing/organising
stages... once the data is 'in' and indexed, it goes just as fast as
anything else.

Re: FMP front-end, other database back-end?

> I've the same experience, with bases of 5 millions records and 5-10 GB.
>
> Importing, reorganizing and indexing in FMP with one good script takes
> 36-72 hours (in fact, more than a whole weekend...) ; but after that,
> work is in seconds.
> If your import operations are infrequent, it's the best to do IMHO.

thanks manet.... yes, you're spot on... my imports are once in a blue
moon.... but the searches and sorts are very frequent. It takes
just a couple of seconds to do a search on the 1.5 million records
once everything is 'in'...

And, as you imply, if I script the import, indexing, etc, etc then I
can just leave it on Friday night and it'll be all done by Monday
morning!

Thanks again

Re: FMP front-end, other database back-end?

wrote:

> It takes
> just a couple of seconds to do a search on the 1.5 million records
> once everything is 'in'...

main différence is in data access :

with Oracle or Access or MySQL, you'll probably use the network

FMP is on the local disk.

a simple SATA Raid with 2 recent inexpensive disks and a fresh reserved
partition is a very good improvement.
--
www.D-L-S.org