Clever Geek Handbook

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

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 this fact causes these methods to be bypassed when compared to full programming languages?

+2


source to share


3 answers


Comparing strings is not slower than in any compiled program, but usually you are comparing many strings (e.g. every row in a table) and slower.

As for loops: DB is not a compiler. It reads your SQL and then interprets it. If you can send the compiled code and execute it to the DB, you won't notice the difference in speed, but the DB must interpret the same code over and over again.



If you're lucky, the DB converts the loop to some internal form (bytecode or data structure), but what a) takes longer than just pointing the CPU at some code, and b) it still needs to run more code to interpret byte code or data structure. For cycles, he must evaluate the conditions for each round.

+1


source


The relational database is optimized for collections. Anything that looks like a cursor or a loop or a correlated subquery that requires the database to run row by row will be slower, often painfully slow. Replacing this code with setup code (the kind that the database is designed to best manage) often improves performance by hours to milliseconds.



+1


source


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

0


source






More articles:


All Articles