Run Code  | API  | Code Wall  | Misc  | Feedback  | Login  | Theme  | Privacy  | Patreon 

Sql Server's curse

 run  | edit  | history  | help 0

I've been using Sql Server for a long time (~6 years) and I've come out to the conclusion that it's a quality product, but you need to be an expert in it. And it's not easy to become one. After 6 years of working with it I know I'm not. The data sets that I had to work with were not that large, the largest being around 25 Gb of data, roughly speaking. But I have come into myriad of problems using it, some of them being:

  • lock escalation issue (what?)
  • database engine using wrong index
  • cached wrong execution plans
  • deadlocks
  • slow insert/merge
  • slow queries

After some work, googling and pain most of these are solvable, except for slow writing - that's by design.

I like tsql despite it's problems like working with strings and other quirks. I like the functional programming feel it gives. But unfortunately such code has one problem - query planner will decide how to execute it and it might be slow. And you won't know why, because execution plan will tell you that 50% of time is spent in some weirdly-named Sql Server operation, but it won't tell you what to do about it. And even having to look at execution plan is already a bad thing, it has rarely helped me even after googling long about what the problematic operation was.

I've just spent 3 days trying to make one query fast. It was just one select with 10 joins and many where conditions, some quite complicated. The largest table had 600 000 rows. And it was painfully slow, sometimes. I've tried to dig into why, but failed, it was slow because Sql Server made a bad decision when executing it and I couldn't influence it. Finally, what I did was to keep temp table of resulting Ids and take one condition at a time intersecting the condition results with current result set. It worked.

So the functional-like code that is promoted might and likely will be inefficient.

What's the moral of all this? Sql Server is overkill in most cases. Today Sql Server is a success because of sales-people and not because of it's design. First it shoots you in the foot then politely offers some medicine. Sql Server is hard. And things don't have to be hard.



It's all relative guys! I had to work with oracle a little and now I love sql server. Also checkout linqdb:


Ok, sorry Oracle you're actually pretty ok.

  by  renz, 6 years ago

Please log in to post a comment.