CODEWITHSUNDEEP
sql-servert-sqlsql-server-2008-r2
whatwhatwhat
whatwhatwhat

Reputation: 2276

Why does this SQL statement increase in execution time if I conditionally select more columns?

I have a script that has multiple queries that write to temporary tables. Each query builds upon the previous one by taking the results of the previous query and doing something else with it. The script runs in 1 second after writing to #Data2.

The next query after the one that writes into #Data2 conditionally selects which columns to display based on a user parameter called @show. When I conditionally select only 1 column (see below), this runs in about 1 second.

select
    (case when 1 in (select use_object from dbo.DelimitedSplit8K(@show,','))  
             then d.vend_num            
             else null 
     end) vend_num
into 
    #Show
from
    #Data2 d

However, when I do this with 53 columns, the query execution time turns to 1 minute 39 seconds (see below). Why is this? Theoretically, the query should only be making a decision to include or to not include the column. Why would this be computationally expensive?

select

      (case when 1 in(select use_object from dbo.DelimitedSplit8K(@show,','))  then d.vend_num          else null end)          vend_num
    , (case when 2 in(select use_object from dbo.DelimitedSplit8K(@show,','))  then d.terms_code        else null end)          terms_code
    , (case when 3 in(select use_object from dbo.DelimitedSplit8K(@show,','))  then d.last_purch        else null end)          last_purch
    , (case when 4 in(select use_object from dbo.DelimitedSplit8K(@show,','))  then d.purch_ytd         else null end)          purch_ytd
    , (case when 5 in(select use_object from dbo.DelimitedSplit8K(@show,','))  then d.pay_ytd           else null end)          pay_ytd

    , (case when 6 in(select use_object from dbo.DelimitedSplit8K(@show,','))  then d.vend_remit        else null end)          vend_remit
    , (case when 7 in(select use_object from dbo.DelimitedSplit8K(@show,','))  then d.curr_code         else null end)          curr_code
    , (case when 8 in(select use_object from dbo.DelimitedSplit8K(@show,','))  then d.bank_code         else null end)          bank_code
    , (case when 9 in(select use_object from dbo.DelimitedSplit8K(@show,','))  then d.pay_type          else null end)          pay_type
    , (case when 10 in(select use_object from dbo.DelimitedSplit8K(@show,',')) then d.pur_acct          else null end)          pur_acct

    , (case when 11 in(select use_object from dbo.DelimitedSplit8K(@show,',')) then d.vendname          else null end)          vendname
    , (case when 12 in(select use_object from dbo.DelimitedSplit8K(@show,',')) then d.fax_num           else null end)          fax_num
    , (case when 13 in(select use_object from dbo.DelimitedSplit8K(@show,',')) then d.telex_num         else null end)          telex_num
    , (case when 14 in(select use_object from dbo.DelimitedSplit8K(@show,',')) then d.pay_hold          else null end)          pay_hold
    , (case when 15 in(select use_object from dbo.DelimitedSplit8K(@show,',')) then d.pay_hold_date     else null end)          pay_hold_date

    , (case when 16 in(select use_object from dbo.DelimitedSplit8K(@show,',')) then d.pay_hold_reason   else null end)          pay_hold_reason
    , (case when 17 in(select use_object from dbo.DelimitedSplit8K(@show,',')) then d.sitename          else null end)          sitename
    , (case when 18 in(select use_object from dbo.DelimitedSplit8K(@show,',')) then d.siteaddress       else null end)          siteaddress
    , (case when 19 in(select use_object from dbo.DelimitedSplit8K(@show,',')) then d.city              else null end)          city
    , (case when 20 in(select use_object from dbo.DelimitedSplit8K(@show,',')) then d.state             else null end)          state

    , (case when 21 in(select use_object from dbo.DelimitedSplit8K(@show,',')) then d.zip               else null end)          zip
    , (case when 22 in(select use_object from dbo.DelimitedSplit8K(@show,',')) then d.county            else null end)          county
    , (case when 23 in(select use_object from dbo.DelimitedSplit8K(@show,',')) then d.country           else null end)          country
    , (case when 24 in(select use_object from dbo.DelimitedSplit8K(@show,',')) then d.FSMAApproved      else null end)          FSMAApproved
    , (case when 25 in(select use_object from dbo.DelimitedSplit8K(@show,',')) then d.FSMAAuditDatetime else null end)          FSMAAuditDatetime

    , (case when 26 in(select use_object from dbo.DelimitedSplit8K(@show,',')) then d.FSMAAuditEditor   else null end)          FSMAAuditEditor
    , (case when 27 in(select use_object from dbo.DelimitedSplit8K(@show,',')) then d.vend_seq          else null end)          vend_seq
    , (case when 28 in(select use_object from dbo.DelimitedSplit8K(@show,',')) then d.interaction_id    else null end)          interaction_id
    , (case when 29 in(select use_object from dbo.DelimitedSplit8K(@show,',')) then d.interaction_stat  else null end)          interaction_stat
    , (case when 30 in(select use_object from dbo.DelimitedSplit8K(@show,',')) then d.topic             else null end)          topic

    , (case when 31 in(select use_object from dbo.DelimitedSplit8K(@show,',')) then d.Uf_CheckLevel     else null end)          Uf_CheckLevel
    , (case when 32 in(select use_object from dbo.DelimitedSplit8K(@show,',')) then d.approbation_type  else null end)          approbation_type
    , (case when 33 in(select use_object from dbo.DelimitedSplit8K(@show,',')) then d.submitted_date    else null end)          submitted_date
    , (case when 34 in(select use_object from dbo.DelimitedSplit8K(@show,',')) then d.approver          else null end)          approver
    , (case when 35 in(select use_object from dbo.DelimitedSplit8K(@show,',')) then d.approval_date     else null end)          approval_date   

    , (case when 36 in(select use_object from dbo.DelimitedSplit8K(@show,',')) then d.rejector          else null end)          rejector
    , (case when 37 in(select use_object from dbo.DelimitedSplit8K(@show,',')) then d.rejection_date    else null end)          rejection_date
    , (case when 38 in(select use_object from dbo.DelimitedSplit8K(@show,',')) then d.vendstat          else null end)          vendstat
    , (case when 39 in(select use_object from dbo.DelimitedSplit8K(@show,',')) then d.po_num            else null end)          po_num
    , (case when 40 in(select use_object from dbo.DelimitedSplit8K(@show,',')) then d.po_orderdate      else null end)          po_orderdate

    , (case when 41 in(select use_object from dbo.DelimitedSplit8K(@show,',')) then d.po_stat           else null end)          po_stat
    , (case when 42 in(select use_object from dbo.DelimitedSplit8K(@show,',')) then d.po_invnum         else null end)          po_invnum
    , (case when 43 in(select use_object from dbo.DelimitedSplit8K(@show,',')) then d.po_invdate        else null end)          po_invdate
    , (case when 44 in(select use_object from dbo.DelimitedSplit8K(@show,',')) then d.poi_line          else null end)          poi_line
    , (case when 45 in(select use_object from dbo.DelimitedSplit8K(@show,',')) then d.poi_release       else null end)          poi_release

    , (case when 46 in(select use_object from dbo.DelimitedSplit8K(@show,',')) then d.poi_item          else null end)          poi_item
    , (case when 47 in(select use_object from dbo.DelimitedSplit8K(@show,',')) then d.poi_stat          else null end)          poi_stat
    , (case when 48 in(select use_object from dbo.DelimitedSplit8K(@show,',')) then d.qty_ordered       else null end)          qty_ordered
    , (case when 49 in(select use_object from dbo.DelimitedSplit8K(@show,',')) then d.qty_received      else null end)          qty_received
    , (case when 50 in(select use_object from dbo.DelimitedSplit8K(@show,',')) then d.due_date          else null end)          due_date

    , (case when 51 in(select use_object from dbo.DelimitedSplit8K(@show,',')) then d.rcvd_date         else null end)          rcvd_date
    , (case when 52 in(select use_object from dbo.DelimitedSplit8K(@show,',')) then d.poi_itemdesc      else null end)          poi_itemdesc
    , (case when 53 in(select use_object from dbo.DelimitedSplit8K(@show,',')) then d.u_m               else null end)          u_m

into #Show

from

    #Data2 d

Upvotes: 0

Views: 62

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280431

You can do this in a simpler way without involving yet another temp table and without calling this expensive function 53 times:

DECLARE @show varchar(255) = '1,3';

SELECT vend_num   = MIN(CASE y.value WHEN 1 THEN d.vend_num   END),
       terms_code = MIN(CASE y.value WHEN 2 THEN d.terms_code END),
       last_purch = MIN(CASE y.value WHEN 3 THEN d.last_purch END)--,
       -- ...
FROM #Data2 AS d 
OUTER APPLY dbo.DelimitedSplit8K(@show,',') AS y;

Of course I agree with comments above that you should consider table-valued parameters instead of passing a comma-separated string; then you can just join against the TVP instead of doing any expensive splitting at all.

If you're really married to using a function, you should look at the performance tests of several of these splitting functions and maybe consider a better alternative (see this and this).

In SQL Server 2016 (and Azure SQL Database), you'll be able to use STRING_SPLIT(), which is a surprisingly well-performing native option. See this, this, this, and this.

Upvotes: 2

Related Questions