Why exactly are non-DB related functions in SQL Server so much slower?

Question

I always hear that non-DB related functions (e.g. string comparison, loops) in SQL Server are slow because, well, it's a database. But why exactly does that fact make these methods crawl in comparison to full programming languages?

Answer 1

Because they are interpreted instead of compiled to machine/CPU instructions.

Answer 2

String comparison isn't slower than in any compiled program but usually, you compare many many strings (say, every row in a table) and that's slow.

As for loops: The DB isn't a compiler. It reads your SQL and then interprets it. If you could send compiled code and have the DB execute it, you wouldn't notice a speed difference but the DB has to interpret the same code again and again.

If you're lucky, the DB will convert a loop into some internal form (byte code or a data structure) but that a) takes more time than just pointing the CPU at some code and b) it still needs to run more code to interpret the byte code or the data structure. For loops, it has to evaluate the conditions for every round.

Answer 3

Relational database are optimized to do things in sets. Anything like a cursor or a loop or a correlated subquery which requires the database to work row by row will be slower, often painfully slow. Replacing that code with set-based code (the kind the database is designed to handle best) will often improve peformance from hours to milliseconds.