Chest or corpse with three Souvenirs of reprisal

You are correct in saying that stat scaling doesn't really improve at all, beyond a minor improvement from ascensions (a sword might go from E --> D for example). In general, the stat classes that each weapon has in the game won't change much, even after being ascended fully.

Compared to Demon's Souls, upgrading a weapon to its max in Dark Souls provides less benefit because of this.

The bonus damage from stat scaling does increase for +n weapons, albeit it's a minor, linear increase (for example, a Battle Axe from +0 to +2 made its bonus damage from stats go +20->+22->+24). Thanks Tacroy

If you need to know when to replace your Drake's Sword, I would say get rid of it when you have high enough stats in one of the "damage" stats (Strength/Dexterity/Faith) and you have an adequate amount of resources to reinforce a scaling weapon to its max.

If you are planning on leveling up a Pyromancer keep a few things in mind:

Pyromancy is not affected by any stats, even Intelligence. Your Pyromancy Flame weapon is directly upgraded at trainers at a later point in the game. So, in the mean time you should be upgrading stats that improve your survivability. Such as Vitality (more hit points) and Endurance (more stamina, equip load, defensive stats).

Even though you should never neglect Vitality, you should aim to cap Endurance at 40 points as soon as possible. At 40 points any additional Endurance won't contribute to your Stamina (green bar).

You should allocate points on Strength and Dexterity in order to meet the minimal weapon requirements to wield them.

Faith and Intelligence upgrade Sorceries and Miracles, so ignore them.