Why does fate/stay night sucks ?
The characters
First we have Shirou. He's your basic harem MC with survivor's guilt, which makes him make choices who makes you want to punch your screen. The two most important ones being that he doesn't want Saber to get hurt and that he doesn't want to kill most of his opponent in the Holy Grail War.
The problem with his philosophy is that it's so stupid it breaks suspension of disbelief. Let's first focus on Saber : he has been proven by fact that he's totally useless in combat. Also, he knows that as soon as he dies, Saber will follow him because she won't have any master left. He also know that she can withstand blows that would kill him instantly. However, he still wants to fight in the front line to “protect” her.
The writers probably wanted to show how much of a good guys Shirou is. However, no one would do that. For Shirou to act like he does in the anime, he would need to be insanely dumb, because he would need to completely fail at understanding the situation. There's no way he wouldn't understand that his way of doing things is not only useless, but in fact increase his and Saber's chances to die. Yet in the anime he just do suicidal things and spout nonsenses as excuses.
Because of his comportment, he ends up in situations where he should die. However, since he's the MC, he is given a huge plot armor which greatly contribute to breaking your suspension of disbelief and making you stop caring about what's happening in the anime.
The way he acts with the other masters is dumb for the very same reason. The part where he is introduced to the Holy Grail Wars can be sumed up like this :
“Shirou, if you decide to fight we will have to try to kill each other”
“Ok.”
“Ok, let's fight.”
“No, I don't want to hurt you.”
“Mouuu Shirou ~~”
Let's look at how the anime present it : Shirou don't want people to get hurt so he join the war.
How will him joining the war prevent people to get hurt ? ->We don't know. Nothing is said about that and he doesn't seems to have a plan.
What will he do to prevent people to get hurt ? → He'll mainly chill at his house with his growing harem and will sometimes get out because he's attacked by an other master and let Saber carry him to victory.
Now the real question is how does this not get him killed early in the war ? Because of his huge plot armor, as said before, and because the other characters are dumbed down/designed so they won't kill him
Shirou has one of the strongest servant. However he's dumb and expose himself way too much. Why doesn't he get killed ? Simple, as as said before, because all the other characters are design so they would like him enough not to kill him.
First we have Thosaka Rin. She's a magus at heart, like her father. Father who didn't hesitate to give one of his children to another family. Rin, despite being like her father, makes an exception for Shirou because she has a crush on him. When she later discovers he has one of the strongest servant, she still spars him and becomes his ally and enter his harem because he's nice.
Of course you could argue that she's manipulating him and plan to kill him once he and saber will finish clearing her path to victory. However, while watching the anime, it's obvious she's just casting away her mage duties, which she is said to put above everything else, because she has a crush on Shirou. It's like the writers were pretending she was a certain type of character, and then made her acts like she had completely different belief that said to have.
Then you have Illya. In F/SN UBW we're given a good explanation why she doesn't outright kill Shirou. However, in the first Fate Stay Night, she just toys with him for no reason, allowing him to, in the end, defeat her. After this, she'll change her mood and begin to live with him in order to add the loli factor to his harem.
The others master don't have such a big impact in the anime so I won't talk about them. Also I saw the first F/SN long ago so I don't really recall what the minor characters did.
The plot
I complained a lot about Shirou's harem. It's because F/SN is not intended to be an harem show but more fantastic/battle one. However most of the war and the battle are just an excuse to put a fucking stupid main character into a harem of girl who's smarter move would be to kill him before he becomes smart enough to use his OP servant to stomp then into the ground.
So the show is just us following Shirou's life, Shirou's love life, Shirou being carried and getting closer and closer to the grail because if he were to die the story would stop. We have huge side-stories when we can see Shirou going on a date with girl, Shirou enjoying school life and so on.
At the end of the anime he's faced with an opponent so strong even the writer though he had to actually do something special to deserve the victory. So he yells “I believe in my ideals” and they the bad guy loses.
In UBW he yells “I believe a little bit in my ideals” and the bad guy loses.
On the other hands Fate Zero had characters who acted like there really where in a battle where they could die, they had real motivation for the grail and did make clever move.
To conclude I'd say that F/SN is a harem with a side story who doesn't fit the harem style while Fate Zero is a good exemple of what F/SN should have been is the writer wanted it to be about an epic battle for a powerful magic item.