speed comparison of sorting 3000000 records with 10 fields!

[alimrb]

anybody have compared a filemaker database with other relational databases in sorting a table with 3000000 records that have 20 text fields?

[Søren Dyhr]

Hope you're aware of the abstract nature, your line of questioning is. Becasue it's highly dependent on the number of indexed fields, and if you make a cartesian product calc'field of the other fields shown, to have one sole indexed field to sort on will you yield different result as well, comparing tool to tool.

I have seen your previous post as attempts to endorse other tools over Filemaker, but such benchmarks are close to meaningless, just like the maxspeed for a Farrari on bended and bumpy road.

I would say that boasting with such features to strive your own ego as execise - might have it's place, just like casino gambling might have it's point to, show you can afford to loose a lot of money. Using such figures to chatting up "birds" or professors prejudice might have thier points, but hardly a matter for people actually using the features found in filemaker!

I can still remember my unsuccessful attempt to get you away from your fixed idea that filemaker should be filebased just like Access, are your "convincing" benchmarks once again, based on this prejudice deployment??

--sd

[Vaughan]

I read this last night: it sounded like a school assignment question to me.

[Genx]

... or just general performance. On a related note, they teach FileMaker at school nowadays?

[Søren Dyhr]

Hence my speaking of flattering professors:

http://www.fmforums.com/forum/showtopic.php?tid/178413/tp/1/

...well it could be an attempt of self marketing, but in my huble opinion, is this way much better:

http://www.onegasoft.com/

http://web.mac.com/zueiv/

...if you as developer wish to launch your self as utterly skilled - show that what you do is usefull!

I have very little understanding for these "denounce the devil" exercises as well as degree mills in deployment. Both is wrong Corn brings in some very good points here:

http://www.fmcollective.com/2007/04/architects_buil.html

--sd

[Genx]

I thouhgt we'd been down this path before...

The only way you'll find out your question is if you get 5 machines, each with their optimum configurations for each database system -- MySQL, MS SQL, PostgreSQL, Oracle, FileMaker... then create your record based scenario and test them out.

[alimrb]

number of fields up to 49 fields and sort and find must be done on every fields, so every fields must be indexed, after indexing find worked good bud sort was so very slow.

Edited May 9, 2007 by Guest

[Genx]

The fact that you are sorting 3,000,000 records at once in the first place is... strange to say the least, in which scenario in a properly structured relational database would it be useful to present 3,000,000 sorted records to a user at any one time?

[Søren Dyhr]

after indexing find worked good bud sort was so very slow

Genx is saying something important here, eventhough the sorting migh have something to be desired, would a potalized sorting never ever show as many records at all. If you read Corn's blog i made a link to, will go as far as calling the behaviour of Filemakers scaling a subject of brittle performance ...which is urging the developer NOT to throw any under-architected task at it!

You should go elsewhere to get the numbercrunching you're after, if you refuse to approach a tool in a skilled and virtued manner - when it comes to it might this tool:

http://www.paradigmasoft.com/

...paired with this:

http://www.runrev.com/

Outperform any filemaker solution, but the time it takes to develope a solution might very well give a different ROI, which here means "later"!

The reading of this might get you somewhere:

http://www.runrev.com/newsletter/january/issue18/newsletter3.php

--sd

[alimrb]

the case was done with alpha five v8, sorting, finding, summarizing, done in a few second without any indexing time, filemaker vs alpha five is just for fun, its a plathing only for kids.

[Søren Dyhr]

its a plathing only for kids

Plaything ...perhaps, but never the less are both totally inadequate solution as well as efficient dito probably build in both ...but stating something without a propper list giving an overview over the premises and a debate of why each of them is important, and especially when you assume that one size fit's all must very strong arguments be presented.

Both Genx and I fail to see why the sorting of 3 million records is of any importance to our everyday utilization of databases, our customers concern is if you can build a reliable tracking of event's in a production company, in order to have as little money as posible tied up in a warehouse - but instead having the database letting you order new raw material just in time.

This means are relational structures posible, and even recursive relational structures posible with whats facilitated in the tool at present : - the answer is yes.

Would filemaker be a first choise, if I needed to calculate the optimum placement of a special offer in a supermarked, using all the data found in previous purchases made over say a month, the disipline is called datamining ...no it's not filemakers taget marked at all!

There are so many other tools aiming at such task, why should we the users of this tool be bothered, as long as the tool can get us from A to B... it might be we indulge ourself in ignorance here, but what is your point in bringing such a matter up - my guess is there never really have been anyone expecting a filemaker to do datamining before you insited on it!

Why should we worry about the perfomance, where all accept it performs brittle, if no measures is instated to prevent it. You could try to fix a jet engine on the roof of a beetle VW, but would you send your family out shopping in it - I would almost immidiately start to worry 'bout the brakes!

--sd

[David Jondreau]

Why feed trolls?

[Martin Brändle]

Please give proof for that (URL or document).

This was probably a highly artificial case which can not stand real world conditions.

As a good programmer (and I assume that you are one) you know that sorts scale with N*log N at best, in worse cases with 0.5*N^2, if Quicksort algorithm is used.

One call of the recursive Quicksort subroutine needs about 10 lines of code (if integer data, e.g. int4 is used only). Let's assume the compiler is highly optimizing, so you may get the same number of processor ops (although this is already highly unlikely, given that you have to stack push/pull the subroutine calls and the OS has to deal with that).

Let's assume further that about 10 CPU cycles are used on average per op. Let's assume you have a 3 GHz CPU, single core.

So for one data field (always assumed, it is int4), you have

3'000'000*log 3'000'000*100 cpu cycles * 1/3'000'000'000 secs/cpu cycle = 0.64 seconds

This is for int data. However, in a real DB you have strings, which cost much more CPU cycles. And you pretended that the sort was done on 10 fields.

So a few seconds will not do that.