Comparison: CTE vs CURSOR

Question

Comparison: CTE vs CURSOR

Navigation

#1 by (8 votes)

10

I do not know if this will be a question that many pose, but I have always had it in mind almost always.

Normally when I need to deliver a corrective script to a client, used to only run once, I use a CURSOR , perhaps because it is more readable for people who do not know much about TSQL and because it is, in a way, simpler to maintain (recursion can often "complicate").

But in development I try, whenever possible, to use CTE because it is " what to use" , according to those who understand it.

So I question: CTE or CURSOR ?

I know that CTE is supposed to offer more performance compared to CURSOR, but is it enough to use it to the detriment of the other option?

The issue of recursion, as I mentioned above, can often complicate a simple process, and may even make it difficult to maintain a multi-level script in a more complex scenario.

In CTE we are not able to see exactly what is executed in the middle, whereas with CURSOR we can put a PRINT or SELECT with important or useful information in a debug process or validation of results.

In short, does using a CURSOR with a temporary (eg), or even a WHILE , cycle is so worse than using a CTE?

Of course it should depend on the scenario, but where should we use one and the other?

sql sql-server cursor cte

asked by anonymous 24.12.2018 / 12:39

1 answer

Include / Require 'file.php' or Include / Require ('file.php') Parse error given when checking variables with empty () function

score 8 · Accepted Answer

Good remarks in the question. In fact, the cursor is more imperative , even though it is still placed in declarative form in the code. The CTE is more functional. The reason we find it more readable is that we program it imperative all the time.

The CTE can offer more performance, but this is not automatic and in fact being something less common to many people can go wrong without realizing it and getting worse. But it can do optimizations that you probably would not think of or could not otherwise adequately decide on. There are cases you can provide better algorithm. From what I know SQL Server will always throw the data from the cursor to disk, this conforming may be that the CTE is faster in all simpler situations because it stays in memory, but it is an implementation failure, and that can change one day. I found a comparison in a specific scenario .

Remembering that CTE is often incompatible across databases. Not that the cursor is more standardized like that. There are cases where the SGDB does not have one or the other and you run out of choice. Not the case with SQL Server, of course. And if you opted for it in the question I imagine this question is not very relevant.

Because CTE is an abstraction it may be more difficult to understand what happens there and how to debug the result, you have to fight with the API instead of fighting with the algorithm. For simple cases it may be easier, but for complex cases the CTE can be excruciating. With the CTE you give up control, which may or may not be good.

Try doing it without CTE in tree templates . Just like abusing recursion is bad , abusing CTE can be bad as well. But just like recursion is better in many cases , the CTE too.

In general I think you should use whatever feels more comfortable, more readable. Performance should be measured, if you notice something worse, change, the way you feel most comfortable. Just do not leave the CTE aside for complex queries by can make the code at least more expressive.

But if you are a "programmer" SQL, you should opt for the CTE. It's the most idiomatic way to do it, it has more expensive SQL. The same thing I'm talking about CTE holds for SQL. If you have a form, and some DBs have this form, to access without using SQL you would do it in a simpler, and possibly legible, way for you, in an imperative way.

Doing subquery makes you more comfortable? The CTE is just this in a more "disguised" way.

There is no magic answer that tells which to use. It is so wrong to say that you should only use a cursor as it says, and a lot of people say, you should only use CTE.