with maya I had a hard time getting predictable results even after working with it for a few days. I don't expect a fluid sim to be straightforward tool that you can pick up and instantly create exactly what you imagined the result to be like, but the Maya tool seems to be on the convoluted side even for a complex tool - a host of sliders with names that all make sense by themselves, but play together in a way that is hardly documented and hard to predict. a lot of the settings seem to react quite volatile even to small numerical changes, too, so 'playing around to figure it out' turned into a quite frustrating game.
fumeFX on the other hand seemed a lot more straightforward. like Demno said, you pretty much get a presentable result from the start and the tweaking of values quite fast seems to get you at least a bit of insight what the values do.
which feed directly into the 'scared of fluid sims' part in the post: if the workflow is slow/hard to predict, then the whole thing turns a bit stale for me. spending too much time to set a sim up, render it, clean it up, get it in the engine and test it without being able to tweak exactly the thing that makes the in game visuals look off in a reasonably fast manner just means that I would rather do it with the in-game methods (UV distortion, some clever blending in the shader, whatever works). so I'm kinda on the scared side, with a dash of undecided.
I guess this reasoning gets less bad once you have a dedicated guy on your team (or someone who knows the tool very well at least). which we sadly don't have at the moment, so I'd like to pick up the necessary basic skills to be able to make a judgement call on whether it should be used in game (and can be polished up to final quality without risking too much rework) without having to invest months of work. I've gone through a bunch of maya tutorial DVDs, but haven't gotten to that point... maybe I just picked the wrong tutorials, but like I said, it seems convoluted in comparison to fumeFX, so I'm curious what else is out there. the main uses would be the cliche explosion, smoke and fire, with a bunch of other effects like water/blood splatters coming next on the list.