I've got a query running against SQL Server 2016. The query is quite complex, because it joins several tables (almost 30 tables) using inner and left joins. The result set contains only 4 rows.
I have these cases:
- In the select list I select only "simple columns", with no functions and no concatenation: the execution time is about 3/4 seconds
- In the select list I use a string concatenation on only one field (a
varchar
field), containing an empty string (SELECT A.field + '', ....
). The execution time grows significantly; in some cases, depending on the field I concatenate the empty string to, it arrives at 11 secs. - In a specific table, if I concatenate two fields the execution time remains 3/4 secs, if I concatenate one of these two fields with an empty string, the execution time grows to 11 secs.
I am very surprised and I can't understand why the concatenation operation is so expensive!
I tried using the CONCAT
function, but nothing changes.
PS: I clear the cache every time I execute the queries so the execution time is not affected by caching policies.