The best video mode, by far, is Whodunnit. It utilizes the Dmd in a way that perfectly complements the theme as well as the game play itself.
The worst would be the Video Modes for Eight Ball Wizard.... especially the flying cue ball.
Also the Spitting Pontoon Video mode for Championship Pub is pretty horrendous, come to think of it.