The wall would be effective as long as it is sufficiently patrolled and maintained. It's difficult to exactly compare it to other nations with effective border walls/fences though as theirs are infinitesimal compared to the size of US' southern border. Even those against the wall have shifted the argument from being ineffective to being immoral and impossible to maintain.There should be stricter, more efficacious immigration laws, a wall won't do jack shit.
Trump says illegal immigration costs US $200 billion annually while his enemies admit at least $54 billion on the low end. The numbers are all over the place on everything depending on the agenda of who their coming from, but the max maintenance cost is pegged around $750 million annually, so it would really only need to stop of a fraction of illegals in order to break even.