Mark Welch

> "...requires some good - and possible unique - design approaches" < And there's the issue: to achieve success with FileMaker, I need to adopt and learn a "completely different" way of doing things. My whole reason for using FileMaker was for simplicity, ease of use, and quick development -- not a long learning curve requiring help at every single stage. The cache issue is a perfect example. On the phone today, after I had complained several times about the fact that even running "flat-out," FileMaker rarely used more than a few percent of CPU capacity (peaking at 9% maximum), the FM technician eventually suggested that I check the cache size setting, which defaults to 8MB. Huh? An 8MB cache on a computer with 4GB? My cache size, for some bizarre reason, was 7MB. The technician suggested that I change it to 16MB to see if performance improved; I immediately asked, why 16MB? Why not 32MB or 64MB? The technician couldn't answer. When I set it to 16MB, CPU usage seemed to nearly double; when I set it to 64MB, CPU usage bumped up to 44 percent (it's a dual-core CPU; FileMaker can use only one, so its max CPU% would be 50%). I tried in vain to find any useful references to the cache settings in my FileMaker books or in the online help (I did find some comments in this forum, of course, but only after I knew what to search for). A learning curve, indeed. And yes, getting CPU usage from 2% up to 44% would certainly have improved overall performance (especially in sorting and summarizing, and in deleting records). It would still not have solved the problems that triggered the worst performance (and I was only just started, with more fields still needing to be indexed, and more tables needing to be related).

At some point, I must decide whether to continue with a solution that is rapidly getting more complex and unworkable, with the hope that somehow I might make it work, or to move back to start over with another solution. That "help geek at FM," like the other help geeks at FM that I have talked with, made it very clear that FM does not wish to provide any support for its product; he was happy to confirm that yes, FileMaker is just really slow, and with that many records, yes, it might just not work. And absolutely yes, to get a list of unique values and a count for each, I'd need to go through a complex series of steps that would slow down my entire database to a crawl -- for a function that I know how to do with SQL with a single command. I appreciate the assistance that folks on this forum have provided. And yes, after spending time "exploring" the performance problems, I did not find a solution, I stopped looking for a solution, because I recognized that every single problem I encounter ends up taking many hours or days to solve, and after a few dozen of those any ease-of-use and simplicity benefit from using FileMaker was completely lost. When things are getting worse and worse, and when two unexpected new problems arise after each problem is solved, I have to accept that I am not making progress, and I need to change direction. Maybe FileMaker could meet my needs, but after spending so much time and realizing how much more work is required to learn this tool, I recognize that there are other tools that are much more likely to get me to a solution much sooner. The obvious solution, of course, is for me to hire a programmer. I've done that, several times, with ASP/SQL Server and later with PHP/MySQL, and what I ended up with, after spending lots of money, were some programmers (I don't pick 'em very well, I know), and some partial code that I couldn't understand or adapt. I'm sure that there are some extraordinary folks who use FileMaker, whom I might hire to help me, but unfortunately it's more likely that if I keep trying, I'll just spend more time and money on "the wrong things," and I'll end up nowhere. So it's time for me to stop and find a new strategy.

Thanks to everyone for your advice. After spending time on the phone with FileMaker support, it seems very clear that these performance issues are neither unusual nor unexpected -- FileMaker simply cannot handle the size database that I need, despite its marketing claims.

I am afraid that I'm at the point of pulling my hair out over FileMaker's performance. I finally surrendered and called technical support and used my "one free ticket" on essentially the most basic question, "Does FileMaker work?" The answer, in the end, is that FileMaker will not work for my needs. I am abandoning 30 days of work attempting to get this tool to work for my needs. I will contact the client whose project first led me to this product, and advise them that the project is returning to "zero," with no technology solution selected. My personal project will also revert to exactly where it was 6 months ago when a programmer "flaked out" after partially developing a solution using PHP and MySQL. FYI, the issue seems to be that FileMaker's "sweet spot" for performance lies somewhere between 250,000 and 500,000 records in the database (it might be the database size, I don't really know). When I pushed it past a million and then past 2 million, it slowed to a crawl and could simply no longer function. When I went through an excruciating series of steps to remove data, it crawled while removing records until it fell somewhere around 500,000, at which point performance was fast again. Both of my projects require a database tool that can work with many millions of records. Therefore, I'm returning to MySQL and PHP, and will be starting over. What's especially frustrating is that FileMaker (the company) kept "stringing me along," suggesting that if only I upgraded, my problems might be solved. So I did so, and I invested more time, until I was unable to proceed because of FileMaker's undisclosed limits. I have uninstalled FileMaker from my system, and I am requesting a refund of the license fees paid. I don't know if they'll refund my money, but I need to stop wasting time on this solution and move on to a database solution that will work.

Thanks -- I definitely did try to exclude the body section, but I'm not sure if I succeeded since I cannot see what the three sections were called. I am simply not going to waste any more time on a "summary field," as it clearly drags performance. But then again, maybe it's not the summary field. Even after deleting it, I am getting long periods when FileMaker won't respond but actually doesn't seem to be doing anything. Eventually I had to shut down the program via Windows, and when I reloaded it started "checking for consistency..." and if the progress bar is a reasonable measure, that's going to take at least a half-hour. When I started, I viewed FileMaker as a prototyping tool, and did not actually expect FileMaker to be scalable beyond 100,000 records, but when it seemed to work even with 250,000 records, I thought maybe I could go live with FileMaker Server as my live database. Now it seems quite clear that it's not going to work as a live database with millions of records. Now I am beginning to understand why most companies that offer FileMaker Server hosting set such low file-size limits.

IdealData wrote: > "I think you need to define a SELF JOIN RELATIONSHIP using the URL as the match on both sides of the relationship. Then you can use the COUNT function to evaluate the number of records that contain the same URL. The SELF JOIN is a strange concept at first - a TABLE pointing at ITSELF, however it is perfectly valid and works just like any other relationship." < I simply don't understand this -- it just doesn't sound right, and given the incredibly long delays I've experienced trying to use a summary field in my database, I don't think this is something I should try. comment wrote: > "Add a sub-summary part to your report, defined as "when sorted by: URL". Place the summary field in this part, and sort the records by the URL fields. // If you like, you can delete the layout's body part to get a summary report." < I believe that this is exactly what I did, although while repeating the sequence (as described below) I realized that I had not selected "include subtotals"). The result (after a 4-5 minute delay while FileMaker was "Summarizing field 'count_of_thumb'") was either a list of URLs all containing the value 1, or all containing the value 2102538. Maybe I should replace the Count() function in the summary field with a calculated field that just always contains the value 1. It really does seem absurd to embed a separate field into the database table just to generate this type of report, especially seeing that this field forces many 4-5 minute delays while FileMaker recalculates these values. Here are my steps: (1) View the layout that matches the table that contains the data. (2) Change to Layout Mode (View|Layout Mode) (3) New Layout/Report (4) Select "List/Report" as the layout type (5) Select "Report with grouped data" (this time, I checked "include subtotals" but earlier I did not check that because the count_of_thumb field is supposed to already contain the count. (6) Select two fields: thumbnail_image_url and count_of_thumb (7) Organize Records by Category "thumbnail_image_url" (8) Sort records by thumbnail_image_url (9) Default theme (10) no headers or footers; do not create a script (11) View report in Browse Mode (12) Finish FileMaker then sorts the data (another 5-minute wait). Eventually, the result is a list of URLs, all accompanied by the value 2102538. In layout mode, I can't even see what the sections are called; nothing is resizable except the field values (which oddly default to very short stubby fields, forcing me to change the layout and then wait yet again for 4-5 minutes for FileMaker to summarize. Eventually, I clicked on the 'count of thumb' field in the bottom row and changed it somehow, I think into a 'subsummary' value which it was NOT in the layout as generated. Then it display some values other than 1 or 2102538 -- but clearly it's not working because there are URLs with the count value of 0 (which is illogical, since the very existence of a URL mandates a 'count' value greater than zero). Navigating this immense layout is simply impossible; any attempt to scroll triggers a long, long pause. I surrender -- I am going to write a script that uses brute force: load each record in the database, check if the image URL is the same as the record before, and if so increment a counter and store it in the count field, otherwise store 1 in the count field.

I'm trying to identify duplicate URLs (for thumbnail images) in a database, in order to identify "stand-in" images that should not be shown (for example, many merchants have a standard image that says "image not available" which is silly for me to display). I've defined a summary field called "count_of_thumb" and I've tried creating new layouts several different ways, but all I ever end up with is either all 1's or all values the same (being the count of all records, I'm not sure if it's all records with a value in the field or a count of unique valies). Clearly I am not understanding the concept of a "summary field" or a "subsummary" in a layout. I've tried defining a layout to contain just the URL and the count_of_thumb values, but again it just shows either 1 or the total count; I cannot figure out any way to get a report that lists, for each URL, the number of occurrences of that value in this field across the database.

Adam sent me a link for a new beta version of the plug-in, and it definitely works now for the http downloads to a specified destination. Thanks!

Thanks. My first reaction was, how could I possibly delete all records without having the records in the current view. The obvious answer was to use a short script (go to layout, show all, delete all). Sure enough, it was much faster.

I'm not sure how "having fields on the screen" would affect this; the screen isn't updated during the deletion process. I assume that you are right that some (perhaps most) of the delay may come from updating the index. What I don't understand is, if I'm deleting all the records from a table (with no data from the table related to any other tables), why can't the entire table contents AND all index entries for the table be deleted together, much more quickly?

During the current development stage of my project, I need to delete all records from certain tables before re-importing data again. What confuses me is the incredibly slow speed to "delete all records." It seems to be deleting one record at a time, taking more than a second per 100 records deleted. I'm still waiting for 550,000 records to be deleted, before I can start importing again (this was already a "reduced data set" which excluded files containing another 1.5 to 2 million records of source data; for the next few cycles I'll use an even smaller set of source files). At this pace, it will take more than 6,000 seconds (100 minutes) just to delete all these records. Then, of course, it will take several hours to re-import source data. Am I missing something here? Surely there must be a quicker way to empty out a table? (And no, I don't want to save a clone of the file, since that would sacrifice the data from my other tables.)

I'm still a newbie at using FileMaker, and I'm definitely having some trouble finding the right plug-ins to handle these tasks: (1) Download FTP files (fine, I can do this with MooPlug, if I know the exact path and file name). (2) Get file-dates and directory listings for FTP sites (MooPlug can't do it; after spending an hour with FTPit Pro, I'm confident that it will take many more hours to decipher its sparse documentation, to see if it can actually meet this set of needs -- but it appears that it can't handle other functionality I need). (3) Download files via http (MooPlug can apparently do this for certain static files, but it won't work for my needs, in part because it doesn't allow for specification of a destination path or file name; it just drops the file into the TEMP directory using the URL as the file name, which simply won't work for dynamic URLs). (3) Get file-dates and file-size (HEAD info) for http files (I can't find any plug-in that promises this; I suppose it might turn out that the data isn't accurate or complete anyway). (4) Access local files, including getting file dates and sizes, directory listings, etc. (I tried the Troi and 24U file products, but despite "trial" and "shareware" references, neither would function without paid registration.) I'm using MooPlug to get a basic list of files in a directory, but without the file-size and date information which I absolutely need. (5) Unzip local files (MooPlug seems to do this fine, at least to unpack a file into the current directory). I don't mind paying for a tool that meets some of my needs. However, thus far none of the plug-in makers have persuaded me that they have working plug-ins which would actually meet any of my needs! The http download functionality seems to be a huge problem -- I cannot find ANY plugin that promises this functionality (other than MooPlug, which promises some very basic functionality but clearly can't meet my needs). The other functions (ftp & file) seem to require two separate plug-ins. Absolutely the worst part of this is that I've downloaded and tried to use a half-dozen different plug-ins, but these things are so poorly documented that it's almost impossible to figure out what they might actually be able to do. One plug-in maker wrote back, acknowledging that their product was released without any documentation! What confuses me is that I don't think my needs are unusual -- I need to import data which is updated, but not on a fixed schedule, from multiple sources. I need to check remote servers (ftp and http) to see if there are new versions of any of the data files I am using; if there are new versions, I need to download the new version, unzip it, and import it. While it's probably unlikely that someone else would need to access remote files using both ftp and http, I certainly don't think any other aspect of the project is unusual. And yet it appears that this need is NOT met by any single plug-in or even a pair of plug-ins. I would appreciate any specific advice or direction. Thanks!

To follow up: TextPipe Pro is definitely doing more for me than I think I could have ever expected from scripting within FileMaker. I've been able to incorporate several different filters (including removal of embedded HTML code, character mapping, white-space removal, and a wide range of text-string substitutions, as well as transforming from pipe-delimited to CSV. My current "test suite" of files consists of 139 files totalling 1.2GB, and TextPipe Pro filtered through the files in 25 minutes. FileMaker was then able to import more than 1 million records from those files in less than two hours (I don't have the exact time as I went to dinner after an hour.) I still have a lot of work to do, both with TextPipe Pro and with FileMaker, but I definitely am glad that I went "outside" FileMaker (and plug-ins) to find a more effective solution.

> So it seems the best route would be to pre-process the data in another application. < I reached the same conclusion, and right now I'm test-driving TextPipe Pro, which seems like it might actually help me a lot more.

Thanks for the replies. I am writing a script for this, and that's where I'm encountering problems. There are several hundred of these pipe-delimited text files, each with its own irregular update schedule. A few files are updated daily; some are updated once a week or so; some don't seem to be updated more than once a quarter. Each file contains these fields: ProductID|Name|MerchantID|Merchant|Link|Thumbnail|BigImage|Price|RetailPrice|Category|SubCategory|Description|Custom1|Custom2|Custom3|Custom4|Custom5|LastUpdated|status|manufacturer|partnumber|merchantCategory|merchantSubcategory|shortDescription|ISBN|UPC and of course each field has a defined type and size. Many fields are frequently empty. Here's an example row of data: 453740635|ArizonaSunr Fragrant scented Candle & Clay Holder|8216|Arizona Sun Products, Inc.|http://www.shareasale.com/m-pr.cfm?merchantID=8216&userID=104451&productID=453740635|http://www.arizonasun.com/Arizona-Sun/906B_sm.jpg|http://www.arizonasun.com/Arizona-Sun/906B.jpg|10.95||Gifts/Specialty|Misc. Holiday|About the ArizonaSunr Fragrant scented Candle & Clay Holder - This Scented Candle and Clay Holder gives you the ArizonaSunr fragrance experience. ArizonaSunr Fragrant Candle includes a clayholder.|Gifts|General Gifts||||2005-08-26 12:54:38.590|instock||||||| Every file is actually furnished by a separate merchant, so there are a wide range of problems in the source data which my script(s) will need to clean up. Some of the files contain embedded HTML; many contain "transmogrified characters" such as the above record which lists a brand name as "ArizonaSunr" because it translated the registered-trademark symbol (R-in-a-circle) into a lower-case "r." As I mentioned, I've paid programmers (twice) to try to implement this using "the tools they use," and each time I was left with code that I can't understand or modify, using tools I am not familiar with. I have several versions of a "change list" or "substitution list" or "filter list" (depending on how you use vocabulary, these terms clearly mean very different things). I finally decided to try doing this with FileMaker, but I think I'm learning that this was also a mistake, in part because FileMaker apparently doesn't handle regular expressions (or anything like them), and I suspect that its scripting language and Substitute() statements will take WEEKS to process several million records.