I would say that a villian without a motive or with a simple motive is much scarier than one with a motive. If only because by having a movtive the villian is then presented as having some kind of rationale and can there for be reasoned with and/or stopped.
The perfect example of villians without motive are zombies. Although they have been used to death (see what I did there?) Zombies are scary because they want nothing but to eat you. They have no other reason for being. You can’t ask them to stop, nor can you feed them something else. Your only options are to slow the inevitable by putting as much space between you and them or cutting them down before they rip the flesh from your bones.
A villian that wants nothing save distruction will always be scarier than one with a clear plan. Chaos will always be more terrifying than order.