I did a little experiment with my Ricoh GR with pictures of moving water at various speeds, which might be of interest and use to people, so hey here are the results. (The pictures were taken at Camden Lock incidentally, and cropped quite significantly as the GR has a 28mm equivalent lens.)
At 1/30, moving droplets are blurred lines, and water in greater volume is a textured sheet. This is about the limit of how low I could handhold the camera while perching on the banks of the canal.
At 1/60, individual drops are more visible but still turn into lines. There are more gaps visible in sheets of water but detail is still lost.
At 1/250 there is a balance. Individual drops are visible if they aren’t moving too quickly perpendicular to the direction of the shot, but they still blur slightly, and if they are moving perpendicular (i.e. across the shot) they’re certainly blurred. The water is still definitely moving but you can see some detail in it.
1/500 is coming close to freezing the water, though there is still a little bit of motion blur in areas that are moving particularly quickly.
And at 1/1000 the water is pretty much still, and blur is due to the limits of the camera.
Which is best? Okay, the answer is always going to be “depends on what you want”, but, some thoughts:
In a landscape photo there’s a lot to be said for freezing the static details of the scene and blurring the moving ones – Ansel Adams did this a lot. The lower speeds achieve this.
The higher speeds are slightly alien. You only see water like this in real life if the scene is lit by a strobe. I would generally use them if I wanted to capture a very deliberate slice of somebody interacting with water drops, and I wanted to emphasise the fact that drops were involved.
For most purposes 1/250 is a good speed for me. People generally don’t move faster than 1/250 unless they’re doing something really quick like sports or martial arts (more so if they’re close), and if I was taking pictures of a scene with people plus moving water I’d get more out of freezing the people in the shot and retaining water movement via motion blur.