Very frustrated. I just don't get it....???

[angelleye]

I just don't understand how anybody gets any actual use out of FileMaker as a database back-end to a web solution. I've got a client that insists it has to be done so I continue to rip my hair out over this type of thing. Please tell me I'm doing something wrong.

We've got an AMD Operton 2.8GHz server with 2GB RAM. I've got FMSA9 and the WPE running on this machine with a web server located off-site.

I'm able to successfully hit FM using the PHP API. I can listDatabases(), etc.

I have a database called Auctions. There are 228,104 records in this database. There is a field called Auction_Status and approximately 4,500 have a value of Current.

I have a layout in this database called UpdateFromPHP which contains only the bare minimum fields that I need to work with...approximately 10 fields. These are all stored fields, no weird cals or anything like that.

Now, if I do the following:

$LookupAuctionStatus = $FMAuctions -> newFindCommand('UpdateFromPHP');

$LookupAuctionStatus -> addFindCriterion('Auction_Number', $_SESSION['eBayItemID']);

$LookupAuctionStatusResult = $LookupAuctionStatus -> execute();

if(FileMaker::isError($LookupAuctionStatusResult))

{

	$ErrorCode = $LookupAuctionStatusResult -> getCode();

	$ErrorMessage = $LookupAuctionStatusResult -> getMessage();



	if($ErrorCode == "401")

		exit("eBay auction number " . $_SESSION['eBayItemID'] . " could not be found in our system.  Please contact our sales dept. at 913-322-2102 for help.");

	else

		header("Location: ../../FileMakerError.php?errorCode=" . $ErrorCode . "&errorMsg=" . $ErrorMessage);

}

else

{

	$AuctionRecord = $LookupAuctionStatusResult -> getRecords();

	$AuctionStatus = $AuctionRecord[0] -> getField('Auction_Status');

	echo $AuctionStatus;

}

I get back a nice and quick result as expected.  Keep in mind I'm searching for a single record based on the auction number provided.  So far so good.



Now, what I need to do is grab a found set (recordset) of all Current auction records.  So, I simply adjust the code as follows:

$FindCurrentAuctions = $FMAuctions -> newFindCommand('UpdateFromPHP');

$FindCurrentAuctions -> addFindCriterion('Auction_Status', 'Current');

$FindCurrentAuctionsResult = $FindCurrentAuctions -> execute();

if(FileMaker::isError($FindCurrentAuctionsResult))

{

	$ErrorCode = $FindCurrentAuctionsResult -> getCode();

	$ErrorMessage = $FindCurrentAuctionsResult -> getMessage();

	header("Location: ../FileMakerError.php?errorCode=" . $ErrorCode . "&errorMsg=" . $ErrorMessage);

}



$CurrentAuctions = $FindCurrentAuctionsResult -> getRecords();

$CurrentAuctionsRecordCount = count($CurrentAuctions);



echo $CurrentAuctionsRecordCount;

When I try and run that code here's what happens...

1) The web browser trying to run the code simply sits in a "loading" state for a very long time. As I type this it's been loading for a good 15 min.

2) Immediately upon loading the page I can see the new user connect via the Server Admin Panel with the IP address. So for this example, I first loaded the page at 1:42 a.m. It's now 1:58 a.m. and it's still simply loading and the user details don't show anything different in the Server Admin.

3) Also, immediately upon starting the page the CPU on my FM/WPE server jumps to 75% and bounces around there and 65% this entire time.

This never seems to end. I haven't been patient enough to actually see this page stop loading. The only way I can get the CPU to go back to normal on the server is to disconnect the PHP user via the Server Admin Panel. Simply stopping and closing the web browser doesn't work.

I've been griping about this problem for almost 2 years now. It started with ODBC which of course I knew sucked anyway, but thought if they'd fix the CPU problem that would solve everything.

Instead they introduced this PHP API which seems pretty cool but I'm having the exact same problems I have with ODBC. It solved absolutely nothing.

Is anybody else experiencing this type of thing? I just don't understand how I'm actually suppose to use FileMaker behind my web solutions. Is it still not ready for that or am I doing something wrong? I've followed all the guides and everything exactly. I've even used the FMStudio plugin for Dreamweaver, not that writing the code is difficult because there really isn't much to it. I just don't see what I'm missing.

Any information would be GREATLY appreciated. Thanks!

[angelleye]

Ah, the one that was loading just finished (total of 19 min loading) and here's what I got:

Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 129386873 bytes) in C:Websitessandbox.angelleye.comclient_sitesdealexpressDE20FileMakerImplementationFileMakerImpl.php on line 348

On mine (which I'm guessing should be the same for all) that line is:

$Vd1fc8eaf = curl_exec($Vd88fc6ed);

[Vaughan]

I know nothing about php etc, but it looks like the web server engine has gotten into an infinite loop somewhere and run out of memory.

[angelleye]

That is what it seems like, yes, but that is not what's happening. In my testing over the last 2 years I've found FileMaker simply can't handle anything more than a single connection at a time calling for minimal data at a time. The CPU jumps to 75% even on the requests that do come back quickly. You can watch it jump to 75% just for that second that it queries the DB and if the result comes back quickly it drops back down quickly. If the result doesn't come back quickly it sticks there the whole time it's querying the DB.

Anybody that does know about PHP happen to have any ideas for me? I've shown the exact code I'm using. You can see there is no looping involved. I simply change the field I'm doing the find on which includes a few thousand results and it won't come back because it runs out of memory after 19 min of loading.

I should add that I actually can do this with ODBC, however, the same CPU spike problem occurs so you can't have more than a single person doing this at once.

[bcooney]

This post reminded me of your previous post, link . It's discouraging.

Have you posted anything like this to TechNet..or contacted someone like Jonathan Stark or Geoff Coffey (two devs I know that from their posts/sites seem to be experts at fmp/php)?

[angelleye]

Yup, I actually jumped in on that other one. Wasn't the original poster. But yeah, same situation going on here.

My client insists on sticking with FileMaker and since everybody at FileMaker acts like I'm ignorant and "not doing it the right way" I figured I'd go ahead and give it a shot with the new PHP API they've actually provided. My own initial tests I noticed the CPU problem was still there so I didn't figure it was going to help and that seems to be what I'm running into again now.

I just fired an email to these guys you've mentioned through their site. Hopefully I'll get a response. I'd love to hear opinions on this. I feel like I'm doing everything the way FileMaker support and other FM developers have told me to do it. We'll see...

[Genx]

Okay...

1) Don't count the array returned by ::getRecords(), if all you want is returned records ... $result->getFoundSetCount();

2) Is Auction_Status an unstored calc field

3) How many records are you actually returning here - i.e. do the equivilant find in FM and see what you get

4) Is the server serving the php file sitting on a remote server to the FM server and if so, what's the size of the pipe linking the two?

5) For anything over 100 records you should really be implementing a paging system.

[angelleye]

Thanks for the response.

1) That's interesting. I was actually using that method when I first started playing and then I saw in the FileMaker Server 9 Custom Web Publishing with PHP PDF guide they actually use count($records) so I changed to that.

2) Auction_Status is a stored text field with indexing turned on.

3) The find in FM returns 4,531 records out of a total of 228,104.

4) The web server is located off-site. My test server is here on my Time Warner Turbo (i think it's 15Mbit download and 1.5Mbit upload) connection. Our production server is located in a datacenter about 10 min. down the highway from us. I'm not sure of the exact connection speeds there but it's fast I know that. That being said, I've tested this same thing completely internal and the same problem occurs.

5) I'll see if I can play around with pagination. All I'm trying to do is pull all the records and loop through the entire list to update each record with data from eBay. I'm doing it now with ODBC and it works at least. PHP API won't even return the records.

[Genx]

Loop through the entire list and execute an individual call to the FMAPI on every item? Yeh that will take a while with 4,531 records if you're processing them one at a time i'm sorry to say. You're looking at 1-2 seconds per request. There are of course work arounds, but it would still take several minutes.

On an a partially related note, what happens if you try and replicate the same "Auction_Status" request via XML directly?

[jonathanstark]

Hi Andrew -

Sorry for your frustration. I feel your pain - FileMaker can be quirky. And just like anything, it's not the right tool for every job. It sounds like you are stuck with it, for better or worse, so please read on.

I can't be sure what is causing your current problem, but the evidence you've offered leads me to believe that the most likely cause of the trouble that the result set is too large for both FileMaker Server and for your PHP installation.

I know, I know, any garden variety sql db could handle 4,500 records in a blink. Here's the thing: unlike something like MySQL, a query using the FileMaker API for PHP does not return a resource - it returns a massively verbose PHP object. In most cases this is very handy because it allows you to pull all the data and metadata in one round-trip to the database, and to then just interrogate the object for whatever you need.

When you perform a query that returns a large (in FileMaker terms) result set - either very wide records, or records with fields that contain an enormous amount of text, or just a plain old "ton o' records" - you are going to see a logjam on the FileMaker Server box while the WPE tries to convert the result into a huge chunk of XML so it can return it to the PHP page making the request.

Once the XML is returned to the PHP page (well, to the PHP processor, really) it needs to be converted into an object, and if this object requires more memory than is available to PHP, you are going to get a fatal PHP error like the one you noted above.

So, the thing to do is optimize your query. If what you are looking for is really just a count of records where Auction_Status = "Current", then I would recommend writing a FileMaker script that does that for you and just returns the result in a single record. See this link for a brief overview of how to do so:

http://jonathanstark.com/questions.php#14

If you actually need to return the records and allow the user to browse through them, I agree with Genx that you should do so with paging. This is very easy to implement with the setRange() method of the Find Object. You can find some FileMaker/PHP resources at the following links:

http://jonathanstark.com/filemaker_api_for_php_documentation.php

http://jonathanstark.com/filemaker_api_for_php_documentation/FileMaker/FMC_Find.html#setRange

http://jonathanstark.com/web_publishing_with_filemaker_and_php.php

Please let us know how you make out.

HTH,

j

[Genx]

1) That's interesting. I was actually using that method when I first started playing and then I saw in the FileMaker Server 9 Custom Web Publishing with PHP PDF guide they actually use count($records) so I changed to that.

The primary reason we do it this way is because if you are implementing paging, you can return any number of records at a time but still be able to know how many records were actually found by your request. Count($records) would only return a count of the number of records actually requested - i.e. if you wanted a count of your entire foundset you would need to return every record in that set to your webserver to be processed. I may have made that more confusing than it should be, if you're still confused I'll try rephrase after a coffee.

[angelleye]

Thanks guys, I appreciate the advise. I'll definitely give pagination a shot and see if I can get some better performance out of the PHP API for things like this.

Let me as you this, though. I just put together another quick PHP doing the exact same thing with ODBC:

$FMAuctions = odbc_connect('deAuctions', 'username', 'password');

$SQL = "SELECT Auction_Number FROM Auctions WHERE Auction_Status = 'Current'";

$rsCurrentAuctions = odbc_exec($FMAuctions, $SQL);

while(odbc_fetch_row($rsCurrentAuctions))

{

	echo odbc_result($rsCurrentAuctions, 'Auction_Number') . '

';

}

odbc_close($FMAuctions);

I load that and the CPU spikes like I'm used to, but within a few seconds I get a list of all my current auction numbers displayed on the screen and the CPU goes back to normal because the ODBC user disconnects after it's done, of course.

So, I find myself asking what is the true advantage of the PHP API? I mean, I know I can run FM scripts from the web is one thing...and I don't have to worry about SQL errors when dealing with the PHP API either...that's cool. But with such drastic differences in performance is it really worth it?

Do you see any problem with me mixing a bunch of ODBC connections (for larger recordsets) with PHP API connections (for anything it can handle)?

Another thing, I've been trying my best to make layouts that only contain the fields I need when working with the PHP API as I've read this is a large part of the performance hit. It's generating XML for all of the fields on the layout. I haven't tried this yet, but if I had a completely separate layout with nothing but the Auction_Number on it to pull the 4500 records from and then used my regular layout with all of the fields I actually need for the updates back into FM one at a time....would that help em out at all?

Seems like lots of over-kill and I'd probably just stick with ODBC if you guys don't think that'd be weird...??

[angelleye]

Ok, I did go ahead and try the PHP API on a layout with only 2 fields: Auction_Number and Auction_Status. This way I got my result back within a couple of seconds just like ODBC.

So that seems to be the answer...too many fields on a layout with that many records coming back. Do you guys think I'd get better performance creating all these extra layouts for things like this or by going ahead with the normal layout and pagination?

[angelleye]

Just to add on to this too while I've got you're attention... :

This auction update thing is just one of many projects I'm worried about. My main concern is using FileMaker as a back-end for our web site to serve up dynamic product pages.

That CPU spike occurs whether I'm asking for 4500 records or just 1. The only difference is the amount of time it's stuck at 75%.

If I create dynamic product pages that query FileMaker as people are clicking through different categories on our web site and every click causes a 75% CPU spike how can I count on that solution to work?? Are you guys using FM in this way?

Order checkout is one thing where we get roughly 5 per hour or so. Our product pages are constantly getting hit, though. Should I rely on FM or just go with MySQL and use the 3rd Party Data Sources features?

[Genx]

In honesty I'd go with MySQL, as much as I love FM I dump it as often as I go with it for more robust systems. Choose whatever is appropriate to your problem my friend. If you're experienced with SQL based systems and want this to be able to grow in the future, go ahead and use SQL and just pull your data back into fm with ESS - be selective about what data you actually pull out to SQL though.

In relation to your 75% CPU spike, thats always sort of worried me a bit, but in truth if you're looking at < 1000 requests per hour, spanned out fairly evenly it's not going to be a huge problem.

I'd say the primary advantage of the API is broad scale deployments to customers as well as the fact that it ships with FMS vs FMSA and finally that it provides all in one solutions vs all in many - that's about it.