3

How can I fix the following cardinality estimation warning?

while exists(select 1 from @images)
begin
   declare @imageid     int
   declare @filename    varchar(max)
   select TOP (1)
      @imageid = [imageid],
      @filename = concat([imageid], '.', [extension])
   from
      @images
   order by
      [imageid]
   ...
   delete @images where [imageid] = @imageid
end

enter image description here

SQL Server Compatibility Level: SQL Server 2019 (150)

Improve this question
1

2 Answers 2

Reset to default
6

Sometimes the warning is nothing more than that, just a warning, and not something actually affecting performance.

The two things that are most likely affecting your performance are the Table Variable, and the fact you're looping instead of using a more relational solution. So I'd first run a SELECT * INTO #images FROM @images to put it inside of a Temp Table before your WHILE loop, and then use that Temp Table inside the loop instead, to potentially improve performance.

To answer your question though, I believe the fact that your imageid is an INT but you're using it in a string function like CONCAT() is where the Implicit Conversion is coming from that is inducing that Cardinality Estimate warning. If you stored a copy of it in your @images table variable already casted as a string data type that was the same type as the extension field and used it in the CONCAT() function instead then the warning should go away.

Also, Table Variables typically result in poor Cardinality Estimates themselves because of their lack of statistics, which may be why the "Estimated Number of Rows Per Execution" is showing 1. (Note there's been improvements in SQL Server 2019.)

Improve this answer
2
  • 2
    Thanks J.D. I chose a table variable over a temp table because the data set is < 20 records. I like your idea of storing the same value as a string in an additional column just to get rid of the warning.
    – kyletme
    Dec 28, 2020 at 2:08
  • 1
    @kyletme No problem, even so there's pretty much no benefits in Table Variables over Temp Tables, at least regarding performance. And in general, the only benefit I'm aware of is that it persists the schema for you but again selecting it into a Temp Table and using that Temp Table instead will generally always be the better route to go.
    – J.D.
    Dec 28, 2020 at 2:14
5

Let me just emphasize that those types of warnings are essentially useless, unless you actually dig into the query and see where the conversion is taking place.

Below will produce such warning, which is total nonsense. The CAST does in no way affect cardinality estimation:

SELECT CAST(p.ProductID AS varchar(20))
FROM Production.Product AS p

The warning for below is totally valid, however:

SELECT *
FROM Production.Product AS p
WHERE CAST(p.ProductID AS varchar(20)) = 23

Note that I'm using an explicit CAST in above examples, for clarity. The same principle applies for implicit conversions as well.

Improve this answer
1
  • Thanks Tibor. In the past year I upgraded our databases compatibility from pre SQL Server 2014 to SQL Server 2016 or later. The changes to cardinality estimation with SQL Server 2014 presented a few performance problems with our cached plans. Since then I have been very alert to any cardinality estimation warnings.
    – kyletme
    Dec 29, 2020 at 3:39

Not the answer you're looking for? Browse other questions tagged or ask your own question.