Implicit conversion uses a better plan

Peachy 2016-04-11 14:28:29

The statements in question are the first 2 update statements. Notice that the difference between those 2 is the convert(varchar(255), t.segreqid) in the filter. With this conversion, which forces an implicit conversion in the plan, the exec plan is better, less reads. Without this, the plan is similar to the plan of the second update statement. more reads and inefficient.

Why is that?

SQLkiwi 2016-04-13 10:18:44
I can't give you a fully satisfactory answer based on the information available, but I can say that small changes to query syntax and cardinality estimates can often result in different plans.

Sometimes, the change is simply down to the different starting point (initial shape of the tree) that resulted from the change in syntax. Sometimes, it is because a seemingly innocuous change caused the optimizer to explore the available options in a different order.

The optimizer hardly ever performs an exhaustive analysis (within the scope of things it knows how to do), so it's not really all that surprising that small changes to the inputs can result in a different path being taken through the optimizer code, with a correspondingly different result.

Generally speaking, implicit conversions are bad news for cardinality estimates, and consequently for plan quality. There is a large element of luck involved in that adding the convert gave a lower cost and faster performing plan. This will not often be true, and it's certainly not something I would recommend you try, or rely on, routinely.