0

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:

  1. In the select list I select only "simple columns", with no functions and no concatenation: the execution time is about 3/4 seconds
  2. 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.
  3. 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.

iamdave
  • 12,023
  • 3
  • 24
  • 53
Giovanni
  • 39
  • 4
  • There could be sooo many things going on here due to the "complex" query. Without knowing the data model, indexes, etc. all we can do is shoot in the dark at this point. Concatenation isn't exceptionally expensive - However, it isn't what SQL Server does best. – SQLMason May 31 '17 at 09:45
  • 3
    Questions like this are probably more suited to the Database Administrators https://dba.stackexchange.com – Rachel Ambler May 31 '17 at 09:45
  • You should add execution plans for every query. I would use this tool: https://www.brentozar.com/pastetheplan/ – Bogdan Sahlean May 31 '17 at 11:07

0 Answers0