It's lacklustre. High drain value and only useful after your preparation has basically worn off.
But is it not effective?
It's effective enough, I guess, but it's not helpful to the archetype that needs it most - Alchemists. If Alchemists could learn it as a spell and cast it using Alchemy, I think it might be worth having.
IMO it's not worth the spell slot. There are so many other things you can take instead of something that can give you another couple hours of something that costs you nothing to create.
Alchemy is not as good as Sorcery or Conjuring or Spell Defense for that matter. If, as a mage, you've got a couple spare skill points at chargen it's not bad to throw a couple points into Alchemy and a specialization of some sort and pick up one or two small buffs. Increase Intuition, Deflection, Improved Invisibility, all good choices. What you want are spells that provide a buff even with a few hits, are useful at a low force, and have low enough drain that can be dealt with during the run. Bonus points for picking spells that your Mentor spirit buffs.
To the OP's strategy of summing Spirits of Man to sustain Drain buff spells. Force 5 or 6 Spirits will likely cause as much drain as whatever Preparation you're creating, I would instead recommend things like the Positive Quality Focused Concentration, Adept power Heightened Concern, Sustaining Foci, or Metamagic Quickening. If you've taken an Increase Drain stat Spell, you'll want to have it on as much as possible, there are lots of ways to do it that don't tie up your Spirits or cost you drain. If you've taken multiple sustaining buffs with the intention of only using them to create Alchemical Preparations you're doing it wrong.
IMO, Alchemy is worth a minor investment. Few extra skill points, one or two Spells, and you get a very good return on that, especially if you're willing to share with the team. Unfortunately additional investment doesn't get you much more IMO. Alchemy 6 vs. Alchemy 3 just isn't a big deal, on average it's one less dice your Potency gets to throw. And you've only got so much time to create Preparations before a run, weather it's 10 of these spells, or 10 of these other spells isn't really even a consideration.