I recently order a DVD of the Swedish horror film, "Let The Right One In." It was a beautifully filmed vampire film, but I can't say I found it particularly scary. Having said that, I did enjoy it. The story was great.
The book was disturbing as hell, but it was a great book. Great film too, both of them.
I rarely watch horror films, though. I can stand reading horror books because my mind partly censors the horror, and video games because (usually) you can fight back against the horror. But horror films are usually not my thing, albeit with exceptions to the rule.
As for vampires being not so scary, clearly you have not played
Resident Evil: Village. Alcina Dimitrescu and her three adopted daughters may not be conventionally supernatural vampires, but they tick the boxes, they are scary, and they are persistent. Hell, Alcina actually stalks you around parts of her castle, so you have to be careful to avoid drawing too much attention, a bit like Mr X or the Nemesis from the remakes of previous instalments. And her daughters are basically sentient swarms of horseflies. And the theme that plays around the daughters is basically a ripoff of the
Jaws theme, which doesn't help matters.
And then there's the Nasuverse's take on vampires. They're basically descendants of an alien called Crimson Moon Brunestud that was invited from the Moon by Gaia because Gaia felt humans were a threat, and basically needed antibodies. Brunestud's direct descendants are super-vampires known as True Ancestors, while what we consider vampires are Dead Apostles. True, at least one True Ancestor (Arcueid Brunestud from
Tsukihime) and one Dead Apostle (Kischur Zelretch Schweinorg, aka Ridcully if he was an impish prankster rather than a Brian Blessed ripoff) are confirmed to be on humanity's side, but vampires in the Nasuverse are feared for a reason. Zelretch got turned into a vampire by Crimson Moon Brunestud himself, and after a battle where Zelretch had to
hold back a copy of the Moon. No, really. And another vampire seen briefly in
Fate/Apocrypha is a hybrid of Vlad Tepes (who hates being considered a vampire) and a rather immoral mage who usurped his soul, and who had world-ending potential.