This guide aims to help you understand better how the weapon upgrades work in this game cause honestly the game has a lot of secrets and lacking explanation.
The information here is either from my own experience or collected from the internet, if you find anything inaccurate please ping me.

I'll be splitting the weapons into 2 categories:
-buy able weapons
-craft able weapons

Except the acquisition method these 2 categories have the following differences:
Craftable weapons have lower base CC and higher max CC (this is important) and additional effects (3rd tab on the weapon description).

Weapon stats:
Arguably the most important and usually overlooked stat for a weapon is the CC.
The CC determines how long your combo can be, how many actions you can do before recharging.
All the other stats, Physical Attack, Cryas Attack and Accuracy can be increased with level ups and titles but the CC is 90% from the weapon you have equipped.

When you reach a new town there is always a new weapon at the store with higher base stats and more CC for you to buy usually a direct upgrade over your old equipment.

You can also use shards to dualize into your current weapon to increase it's base stats and give it special effects. These upgrades don't transfer to your new weapons.

So you're asking what is better to do: buy or upgrade?
If this is your first playthrough: BUY.
If you are struggling to win battles: BUY.
If you can still get better gear with money 90% of the time you BUY it.
Buying new weapons will always increase your CC opening more option in combat, raw stats alone aren't enough in this game.

Now if you already did the above and want more power you can start upgrading and there are ways you can also increase a weapon's CC.
Getting a weapon to +30, +60, +90 and +99 will increase the weapons min CC by 1. This is NOT easy to do and takes a lot of time.
Increasing a weapons max CC is also possible. All shards have a quality (not the quality of the resulting weapon). If you look at the user guide in-game in the qualities list each quality has enhancement %. To increase the max CC you do:
weapons min CC * weapons max CC = total enhancement % needed from shards dualized to the weapon.
For example:
5*7 = 7 shards with enhancement 5% or 8 shards with enhancement 4%.
This also takes time to accomplish.

You probably noticed that dualizing weapons with certain crystals will create a new weapon.
Should you do that? Yes and No.
The new weapon has higher stats, higher max CC and lower min CC so you start the battle with fewer CC. This weapons are meant for players with better understanding of the combat so if you're not feeling confident this upgrade won't help you.

However there is a minor detail that the game doesn't explain well:
The upgrades that you have done to the base weapon will TRANSFER to the new craftable weapon.
But only half of them.
For example an iron sword +20 will carry over half of the stats from the upgrades (CC included i think) to the new weapon. This is the recipe here to make truly powerful weapons.

Best buyable weapons:
Mythril weapons bought from Telos Astue
Rare weapons bought from various shops once you have enough stamps, this can take some time to unlock for all characters.

Best reward weapons:
In the final dungeon defeating all the optional minibosses (their name are references to the older games) will give you the best weapons in the game that can be obtained before defeating the final boss, except for Asbel, he doesn't get one this way.
For Asbel you need to complete the side quest: "The Keepsake Sword".

Best craftable weapons:
To get these you need to defeat the final boss and complete the story. Load your completed save file and view a new skit in the saving point before the final boss. This will unlock you the ex-dungeon.
The ex-dungeon is HARD and the drop rates for the material you need are LOW but with enough tries you will get them.
The recipes are as follows:
Asbel's : Long Sword + Enasphere.
Sophie's : Iron Gauntlets + Diosphere.
Hubert's : Rune Dualblade + Triasphere.
Cheria's : Throwing Knives + Tessarasphere.
Malik's : Steel Bladerang + Pentesphere.
Pascal's : Steel Shotstaff + Eksisphere.

If you noticed the base weapon needed in each's character starting weapon (except Sophie she starts with "Gauntlets") + the item from the ex-dungeon.
To craft the most powerful weapons you can from the start of the game upgrade the weapons without buying new ones and dualize them with the spheres once they reach +99.
Again this is very hard and quite grindy and best to leave for a new game+ if you value your fun.