1 2 Previous Next 18 Replies Latest reply on Jun 19, 2010 9:57 PM by jond

    A Question of Speed



      A Question of Speed


      I'm sure that the topic of speed has been raised before, but I couldn't find anything that seemed to address my situation, so I thought I would put this question out again to those who have more experience with this software. 


      I have built two databases. Neither one is particularly complex. In fact the second one only has about 12 fields, with a couple of entry calcs. I mainly use these dbases to store big chunks of information. I then do searches and export found sets. That's 90% of what I do with them.


      But they are big. The second one mentioned above already has 138 million records, and I may be adding as much as 15-20MM more each month. So speed is a serious issue. For example I recently tried to do a search, and as often happens the dbase started out by indexing the field in question. The problem is that indexing this one field was going to take over 46 straight hours! That won't work.


      Likewise, I've noticed with my smaller dbase (about 3.5MM records) that importing new records got WAY slower as more fields were indexed. I turned off indexing on every field I could, and that helped, but it's still slow. I can only imagine what will happen with the larger dbase once a few fields are indexed.


      So here's my thoughts on this: I've noticed when running FM that typically the CPU use is low. Often less than 5%, and rarely more than 15%. So the slowdown does not appear to be a factor of CPU power. Also, I have 8 GB of RAM, and the amount of RAM used by the system for FM never seems to get anywhere near topping out, so I don't think it's that either. The hard drive on the other hand is always going, and the speeds are not impressive. 


      So it seems to me that the overall speed of of indexing, adding/exporting records, and finds (which is most of what I need) would be greatly improved by adding an external RAID 0 hard drive. But before I throw down the coin for that, I wondered if anyone else here had any experience with this, and could offer some advice. Thanks!


      My system -- iMac 27" Quad I7; 8 GB RAM; FM Pro 10.0v3; no networking

        • 1. Re: A Question of Speed

          You've clearly discovered the trade-off between indexed and unindexed fields. Indexed fields search/sort fast but import slowly and a faster hard drive is definitely a good idea.


          One trick for searching large data sets more quickly is to split your search into two steps:


          Step 1:


          Enter find mode and perform a find entering criteria only in stored, indexed fields.


          Step 2:


          Enter find mode and enter criteria in any unstored/unindexed fields.

          Select Constrain found set.


          If step one locates a relatively small number of records, this two step find will perform many times faster than trying to perform the find in one step.


          Given the size of your files, you will need to examine the design of all your fields, relationships and layouts carefully to minimize the delays. Sometimes you even have to denormalize your data to get acceptable response times.

          • 2. Re: A Question of Speed

            Thanks Phil.


            Have you had any experience over the years with increasing speed for yourself or a client by using a RAID 0 external hard drive? Do you remember how much of a difference it made?


            I'm concerned that my shiny new RAID will drop my indexing time from 46 hours to 38 hours (or some such thing). In other words faster, but not fast enough to really matter.



            UMG Direct Response

            • 3. Re: A Question of Speed

              I don't have any hard data I can give you on that.


              We've definitely seen improvement by upgrading to RAID but we also upgraded to new processor, OS version and Filemaker Server version at the same time so can't point to any one performance improvement.


              We also import much smaller blocks of records (< 5000 worst case) on a daily basis so we don't have an issue with keeping as many fields indexed as possible for faster finds and sorts.

              • 4. Re: A Question of Speed
                Steve Wright

                Im constantly trying to squeeze a little extra performance out of my system / solution, I do a lot of data migration from other systems for my clients, the faster I can get FM to process it, the more I can get done in the day..


                I have just decided to invest in a 160GB SSD to see how much of a difference that will make, Ill let you know..

                Ive been running in raid 0 for quite some time, so couldn't really tell you if it offers an improvement.


                All I know is my CPU is at 2% usage during some hefty manipulation processes, so I'm hoping the SSD gives me a good boost in performance.



                • 5. Re: A Question of Speed

                  I do a lot of IT services, namely server database systems, so I do have a lot of expertise in this area. Below are rough average's based on that.


                  Standard SATA II - typically will transfer read/write between 70-90 mb/s. 

                  Raid 0 using the same drives improves performance by 20% on average.

                  SSD will double and in some cases (really depends on drives - don't expect a cheap drive to perform) 300%.


                  Raid 10 is a Merge of Raid 1 and Raid 0. It offers Raid 0 speed imrovements with Raid 1 redundancy. It is the ONLY way for a database server to be setup, really. 


                  Raid 5 is the slowest RAID commonly used. It offers great redundancy but sadly it's the slowest.


                  An external case housing dual Intel SSD drives in Raid 0 would be the fastest option next to replacing your internal with an SSD. Cost wise, your going pay through and out the nose. Newegg.com has some of the best retail pricing on Intel SSD drives. I've used Patriot, Intel, Kingston and Ocz. I personally like the Intel's. SLC is Ok too. Read the reviews to find the best resulting unit.


                  As a point of reference I did a PowerBook G4 12" 1.0ghz from a 60gb IDE to 32gb SSD with IDE interface. Bootup time was around 7 seconds with leopard. Before it was three to four times longer. It shutdown so quick it was hard to time it, honest. I've got a Lenovo Netbook with an Intel 30gb SSD and it is faster operning FM files than my T400 with a C2D T9400 and a WD 7200rpm drive.


                  OSX typically does 1000x better on data imports than Windows. When I do data migrations for clients I don't even waste my time on a windows machine. Back in FM 8 days we did a test. Used a Quad Xeon machine against a PPC G4 eMac. The eMac won... less than 2min to over 13mins.

                  • 6. Re: A Question of Speed

                    Thanks SWS.


                    I thought about an SSD too, but decided against it for two reasons:

                    1) Cost. Ouch.

                    2) I am using an iMac, so I am currently limited to Firewire 800. The iMac has no SATA or eSATA - so whatever gain I might see with an SSD would probably be more than lost due to the slower I/O connection.


                    I'll be curious to hear how it works out for you though. This dbase is already really big, and over the next few months it could turn into a monster. Assuming that I can monetize on it, a hardware upgrade could definitely be in my near future.



                    • 7. Re: A Question of Speed

                      Here's a good list to pick from before you spend the wazoo money.



                      • 8. Re: A Question of Speed

                        Thanks simplistik - I will definitely keep a copy of this post on file as I move forward.


                        Initially I'm probably moving forward with a simple 2-drive RAID 0 setup. Probably a CalDigitVR.

                        I already have a separate harddrive for backing up the data, so I don't need RAID 1 (or equivalent RAID data protection) at the moment.

                        Right now this is all about speed.


                        As I move forward however I will almost certainly have to invest in new hardware.

                        At that point I will definitely be considering RAID SSD's.


                        Thanks again.

                        • 9. Re: A Question of Speed
                          Steve Wright

                          I have been "umming and arring" as they say... for some time on an SSD, yes they are ridiculously expensive in terms of £ per GB and its not something you can 'try out' really either..


                          The way I look at it is this : If it knocks 1 hour waiting time from my days work, it will have paid for itself in no time.


                          Something else I have noticed recently is FileMaker is much faster on a mac than windows, well at least with manipulating data.  Even though my windows machine has a faster CPU, more ram and faster HD's... My iMac will cut around 10-15 mins off a scripted import / manipulate routine which generally takes around an 90 minutes on Windows.


                          • 10. Re: A Question of Speed



                            STICKER SHOCK WARNING - LINK


                            Speed? You want SPEED! 1400mb/s read and write and it's 1TB. 


                            Ferarri's and Vettes cost money for a reason. I have a client eye'n these up... I think I'd have to "borrow" the server for a week to play with.

                            • 11. Re: A Question of Speed

                              Well if the need is there and you have the budget you spend what you need to spend.

                              But wow. $4,400! Sheesh.


                              On the plus side however, shipping is only $6.98.

                              • 12. Re: A Question of Speed
                                Steve Wright


                                Michael Arnovitz wrote:


                                I'll be curious to hear how it works out for you though. This dbase is already really big, and over the next few months it could turn into a monster. Assuming that I can monetize on it, a hardware upgrade could definitely be in my near future.


                                Apart from Windows performance improvements, so far the tests I have run in FileMaker have remained the same as my 7200RPM HD setup.

                                admittedly, I'm working on relatively smaller data sets, 40k records being imported with a quite a few searches and replace field contents


                                On my standard HD it took 21 mins 40 seconds, on the Intel X25-M SSD it took 21 mins 14 seconds

                                Changing cache from 64mb to 256mb made a massive difference (not) and took 21 mins, 34 seconds.

                                So no improvement in FM for me :-(  Still, its improved a lot of other things so Im still happy I purchased it.


                                Of course, I can see there being improvements on larger data sets, especially where writing the cache to disk is concerned, whether that would be worth it, I doubt at the moment... 


                                The most noticeable performance increase I have had with filemaker was when I added a new I7 980x CPU and clocked it to 4GHz

                                Obviously, 90% of this is going to waste in FM,  since it does not use multiple cores, but the clock speed itself made a very noticeable difference.  Besides.


                                I think the only thing that will speed FM up now, is FMI themselves...







                                • 13. Re: A Question of Speed

                                  SWS - that's a little depressing. If I bought a SSD and ended up with a lousy 26 second savings I would not be very happy.


                                  I've checked my system stats a number of times during different FP functions, and it sometimes doesn't look like the system is using very much CPU or Hard Drive. And Mac OS X supplies the RAM dynamically, so I really don't think that the RAM is an issue either.


                                  This makes my worry that, at least with certain functions, the speed I'm getting is simply the speed I'm going to get, and that faster drives aren't going to really make much of a difference. Which would not be good, because I can't have my entire dbase out of action for 40-50 hours every time I need to index a field.


                                  On the plus side my iMac (with Quad Intel 2.8 GHz I-7's) does seem to show all 4 cores being used.

                                  But despite that, if this is all about CPU instead of RAM or hard drives I'm in trouble because Apple doesn't make many computers more powerful than the one I already have.


                                  I have a CalDigit VR coming in next week. I'm setting it to RAID 0, crossing my fingers and hoping for the best.

                                  • 14. Re: A Question of Speed
                                    Steve Wright

                                    I'm sure there will be some improvement, there has to be... with a data set  of the size you are working with there will definitely be more disk usage than what I have tested with..


                                    I wasn't expecting miracles myself, but did expect to see something different, a little more than a few seconds anyway.  Im going to experiment further over the weekend.

                                    1 2 Previous Next