Chaos path weapon scaling

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.

The answer is "it depends".

The first thing to consider is that elemental weapons don't scale, but do have high base damage due to their elemental attack (depending on the enemy). Due to this elemental weapons, and weapons with a high base damage in general, are preferable in the early portions of the game to weapons with high scaling but low base damage. This is because early in the game your stats are not high enough to give you a sizeable scaling bonus. With that said however, your damage with a scaling weapon will eventually greatly out weigh the damage you do with elemental weapons in the end, and is therefore preferable in most situations.

There is one last thing to consider though, which is all enemies have different resistance to elemental and physical attacks. Some enemies are really weak against lightning, and others take almost no damage from it. Whereas others will take a bunch of damage from physical attacks, whereas others will only take a small amount of damage. Due to this, regardless of your level, you will always find enemies where a weapon with high elemental damage will be preferable, but as a general rule of thumb, you should be focusing on using weapons with high stat scaling, and using elemental damages as back-up weapons.