I guess they intended pyromancy to be something you just added to some other build later on in the game when you have souls to spare.
It's never good. It can never become good. I'm in NG+2 currently just toying around every once in a while and I've done respecs during my NG+ runs to try various builds and the main reason I even play NG+ is just to quickly get more slabs to try and toy with more things. My character has been SL130 and stayed there since the first third of NG+1.
I'll preface by saying that by magic, I mean all schools. Pyro, dark, sorceries, miracles.
Not once have I found magic worthwhile in my tries, despite the high soul level, having all the necessary rings and collected every single spell in existence. Heck, I'd describe it as much less viable than heavy weapons which I tried after seeing Damned Registration argue for them.
Yeah, I'd still take sellswords or longswords over this for a NG run but it's not too bad at high soul levels. Much more viable than magic in any form. Still there's this thing where while it may do more damage per hits, the number of window of opportunities is lower compared to fast weapons. There's many very tiny pauses of bosses where I can hit with fast stuff right in between two dodges but heavy weapons make this either impossible or make you trade the hit.
When I say magic is not viable, it's not to mean "you can't complete the game". It's more that it's just dramatically inferior to anything you could do in this game. The damage is so fucking pathetic and tracking issues making many spells miss like crazy on enemies that spazz out across the arena mean that this playstyle is just absolute tedium. During the time I tried magic after respeccing at rosaria, I even found myself going out of ashen estus flask while fighting Midir. I was hitting his body instead of his head too many times out of impatience. You really need to only cast spells on him when his head is immobile, but if that's the case, why fight with a ranged character over a melee one who rushes in?
Magic is best used for buffing a pure melee character. Only put as many points in faith and int as you need for the spells you find useful. A single cast of Tears of Denial will do more good than spamming sunlight spears. Spook is a must have for running quickly through areas on NG+ if you don't care for completing content and killing mobs.
Funny thing about magic being best used on melee characters is that the only school of magic that does any worthwhile damage is miracle.. when you fight in melee range. You see, spear type faith spells don't do their full damage if you hit at range. But when you hit in melee, they hit like trucks.