Large runtime difference for function with pivot calculation

Question

Large runtime difference for function with pivot calculation

Hans Furmann 40

Hello,

I have a SQL Server function that outputs the results of a table function as a pivot table. If I run the pivot function directly, it takes 6 seconds, if I call this function as a SQL script with the same parameters, it takes 26 seconds. What could be the reason for this?
This is the function with 6 seconds execution time:

If I Run the above function this way, it uses 26 seconds:

SELECT * FROM [MyPivotFunction]('2024-01-01', '2024-12-31', 371)

Does anyone have an idea why there are such time differences with the SQL server?

Regards

Hans

Hans Furmann 40

The Pivot function is:

SELECT  [Field1], Field2,   
       [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23], [24], [25], [26], [27], [28], [29], 
       [30], [31], [32], [33], [34], [35], [36], [37], [38], [39], [40], [41], [42], [43], [44], [45], [46], [47], [48], [49], [50], [51], [52], [53], [54], [55], [56], [57], 
       [58], [59], [60], [61], [62], [63], [64], [65], [66], [67], [68], [69], [70], [71], [72], [73], [74], [75], [76], [77], [78], [79], [80], [81], [82], [83], [84], [85], 
       [86], [87], [88], [89], [90], [91], [92], [93], [94], [95], [96], [97], [98], [99], [100], [101], [102], [103], [104], [105], [106], [107], [108], [109], [110], [111], 
       [112], [113], [114], [115], [116], [117], [118], [119], [120], [121], [122], [123], [124], [125], [126], [127], [128], [129], [130], [131], [132], [133], [134], [135], 
       [136], [137], [138], [139], [140], [141], [142], [143], [144], [145], [146], [147], [148], [149], [150], [151], [152], [153], [154], [155], [156], [157], [158], [159], 
       [160], [161], [162], [163], [164], [165], [166], [167], [168], [169], [170], [171], [172], [173], [174], [175], [176], [177], [178], [179], [180], [181], [182], [183], 
       [184], [185], [186], [187], [188], [189], [190], [191], [192], [193], [194], [195], [196], [197], [198], [199], [200], [201], [202], [203], [204], [205], [206], [207], 
       [208], [209], [210], [211], [212], [213], [214], [215], [216], [217], [218], [219], [220], [221], [222], [223], [224], [225], [226], [227], [228], [229], [230], [231], 
       [232], [233], [234], [235], [236], [237], [238], [239], [240], [241], [242], [243], [244], [245], [246], [247], [248], [249], [250], [251], [252], [253], [254], [255], 
       [256], [257], [258], [259], [260], [261], [262], [263], [264], [265], [266], [267], [268], [269], [270], [271], [272], [273], [274], [275], [276], [277], [278], [279], 
       [280], [281], [282], [283], [284], [285], [286], [287], [288], [289], [290], [291], [292], [293], [294], [295], [296], [297], [298], [299], [300], [301], [302], [303], 
       [304], [305], [306], [307], [308], [309], [310], [311], [312], [313], [314], [315], [316], [317], [318], [319], [320], [321], [322], [323], [324], [325], [326], [327], 
       [328], [329], [330], [331], [332], [333], [334], [335], [336], [337], [338], [339], [340], [341], [342], [343], [344], [345], [346], [347], [348], [349], [350], [351], 
       [352], [353], [354], [355], [356], [357], [358], [359], [360], [361], [362], [363], [364], [365], [366], [367], [368], [369], [370], [371], [372], [373], [374], [375], 
       [376], [377], [378], [379], [380], [381], [382], [383], [384], [385], [386], [387], [388], [389], [390], [391], [392], [393], [394], [395], [396], [397], [398], [399], 
       [400], [401], [402], [403], [404], [405], [406], [407], [408], [409], [410], [411], [412], [413], [414], [415], [416], [417], [418], [419], [420], [421], [422], [423], 
       [424], [425], [426], [427], [428], [429], [430], [431], [432], [433], [434], [435], [436], [437], [438], [439], [440], [441], [442], [443], [444], [445], [446], [447], 
       [448], [449], [450], [451], [452], [453], [454], [455], [456], [457], [458], [459], [460]
       FROM 
       (
SELECT  [Field1], Field2, Field3, DATEDIFF(d,'2024-1-1', Datum) + 1 As Tag
FROM [MyTableFunction]('2024-1-1', '2024-1-31', 371)

       ) As SourceTable
             PIVOT
             (
             Max( [Field3]) 
             FOR Tag IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23], [24], [25], [26], [27], [28], [29], 
             [30], [31], [32], [33], [34], [35], [36], [37], [38], [39], [40], [41], [42], [43], [44], [45], [46], [47], [48], [49], [50], [51], [52], [53], [54], [55], [56], [57], [58], [59], 
             [60], [61], [62], [63], [64], [65], [66], [67], [68], [69], [70], [71], [72], [73], [74], [75], [76], [77], [78], [79], [80], [81], [82], [83], [84], [85], [86], [87], [88], [89], 
             [90], [91], [92], [93], [94], [95], [96], [97], [98], [99], [100], [101], [102], [103], [104], [105], [106], [107], [108], [109], [110], [111], [112], [113], [114], [115], [116], 
             [117], [118], [119], [120], [121], [122], [123], [124], [125], [126], [127], [128], [129], [130], [131], [132], [133], [134], [135], [136], [137], [138], [139], [140], [141], [142], 
             [143], [144], [145], [146], [147], [148], [149], [150], [151], [152], [153], [154], [155], [156], [157], [158], [159], [160], [161], [162], [163], [164], [165], [166], [167], [168], 
             [169], [170], [171], [172], [173], [174], [175], [176], [177], [178], [179], [180], [181], [182], [183], [184], [185], [186], [187], [188], [189], [190], [191], [192], [193], [194], 
             [195], [196], [197], [198], [199], [200], [201], [202], [203], [204], [205], [206], [207], [208], [209], [210], [211], [212], [213], [214], [215], [216], [217], [218], [219], [220], 
             [221], [222], [223], [224], [225], [226], [227], [228], [229], [230], [231], [232], [233], [234], [235], [236], [237], [238], [239], [240], [241], [242], [243], [244], [245], [246], 
             [247], [248], [249], [250], [251], [252], [253], [254], [255], [256], [257], [258], [259], [260], [261], [262], [263], [264], [265], [266], [267], [268], [269], [270], [271], [272], 
             [273], [274], [275], [276], [277], [278], [279], [280], [281], [282], [283], [284], [285], [286], [287], [288], [289], [290], [291], [292], [293], [294], [295], [296], [297], [298], 
             [299], [300], [301], [302], [303], [304], [305], [306], [307], [308], [309], [310], [311], [312], [313], [314], [315], [316], [317], [318], [319], [320], [321], [322], [323], [324], 
             [325], [326], [327], [328], [329], [330], [331], [332], [333], [334], [335], [336], [337], [338], [339], [340], [341], [342], [343], [344], [345], [346], [347], [348], [349], [350], 
             [351], [352], [353], [354], [355], [356], [357], [358], [359], [360], [361], [362], [363], [364], [365], [366], [367], [368], [369], [370], [371], [372], [373], [374], [375], [376], 
             [377], [378], [379], [380], [381], [382], [383], [384], [385], [386], [387], [388], [389], [390], [391], [392], [393], [394], [395], [396], [397], [398], [399], [400], [401], [402], 
             [403], [404], [405], [406], [407], [408], [409], [410], [411], [412], [413], [414], [415], [416], [417], [418], [419], [420], [421], [422], [423], [424], [425], [426], [427], [428], 
             [429], [430], [431], [432], [433], [434], [435], [436], [437], [438], [439], [440], [441], [442], [443], [444], [445], [446], [447], [448], [449], [450], [451], [452], [453], [454], 
             [455], [456], [457], [458], [459], [460]
             )
       ) AS PivotTable

Hans Furmann 40 Reputation points

2024-01-08T09:05:53.1266667+00:00

The funtion gives back 460 pivot columns
SELECT [Field1], Field2, [1], …, [460]

FROM

(

SELECT [Field1], Field2, Field3, DATEDIFF(d,'2024-1-1', Datum) + 1 As Tag

FROM MyTableFunction

) As SourceTable

PIVOT

(

Max( [Field3])

FOR Tag IN ([1], …, [460])

) AS PivotTable

Hans Furmann 40

The function gives back 460 Pivot columns

SELECT [Field1], Field2, [1], …, [460]
FROM
(
SELECT [Field1], Field2, Field3, DATEDIFF(d,'2024-1-1', Datum) + 1 As Tag
FROM [MyTableFunction]('2024-1-1', '2024-1-31', 371)
 
) As SourceTable
PIVOT
(
Max( [Field3])
FOR Tag IN ([1], …, [460])
) AS PivotTable

Erland Sommarskog 122.7K Reputation points MVP Volunteer Moderator

2024-01-08T22:52:21.71+00:00

There is a lot of code in your posts, but it is difficult to follow. Do I understand it correctly that you have two table-valued functions? On which you refer to MyPivotFunction and one that you refer to as MyTableFunction?

It seems that the code for MyTableFunction is missing.

Can you also show us the exact calls that take 6 and 26 seconds respectively?

I can help if you clear the contents of some of your posts to reduce the noise. (One was marked as deleted, but as I am a moderator, I undeleted it.)
Hans Furmann 40 Reputation points

2024-01-09T11:58:05.61+00:00
I think the code of MyTableFunction is not really relevant for my question, because it's used in both cases.
My Question is why needs MyPivotFunction much less time when I run it directly and why runs it 4 times long when I run it via

SELECT * FROM MyPivotFunction

In both cases I get exactly the same result

You can see the code of MyPivotFunction above. Its starting wtih

SELECT [Field1], Field2, [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31], [32], [33], [34], [35], [36], [37], [38], [39], [40], [41], [42], [43], [44], [45], [46], [47], [48], [49], [50], [51], [52], [53], [54], [55], [56], [57], [58], [59], [60], [61
Viorel 122.9K Reputation points

2024-01-09T13:07:51.1833333+00:00

What queries are you comparting and did you try to add option (recompile) after test select statements?
Erland Sommarskog 122.7K Reputation points MVP Volunteer Moderator

2024-01-09T22:43:58.1866667+00:00

I think the code of MyTableFunction is not really relevant for my question, because it's used in both cases.

It's up to you. If you are not interested in sharing the information we ask for, you don't have to. But then you may not get any answers. To be able to answer, I need to see the code that takes six seconds and I need to see the code that takes 24 seconds, and yes, I need to see what it's inside that table function.
Hans Furmann 40 Reputation points

2024-01-10T11:41:59.32+00:00
I cannot understand your comment, I already showed you both codes.
The 6 second code ist the long one with the > 400 flield with numeric fieldnames.
The other one is (again) this:

SELECT * FROM [MyPivotFunction]('2024-01-01', '2024-12-31', 371)

It simply uses the 6 'seconds code' (MyPivotFunction) in this select-statement above.
Greg Low 1,985 Reputation points Microsoft Regional Director

2024-02-10T03:51:16.1133333+00:00

If you want Erland (or anyone) to help, you need to give them code that they can execute that demonstrates the problem. Providing code that uses a function that contains unknown code doesn't allow anyone to test anything. They are left with guessing what might be wrong. The nested code can completely change the query plan. Are you able to demonstrate the issue with code that you can share?

Your answer

Hans Furmann 40 Reputation points

2024-01-08T09:05:53.1266667+00:00

The funtion gives back 460 pivot columns
SELECT [Field1], Field2, [1], …, [460]

FROM

(

SELECT [Field1], Field2, Field3, DATEDIFF(d,'2024-1-1', Datum) + 1 As Tag

FROM MyTableFunction

) As SourceTable

PIVOT

(

Max( [Field3])

FOR Tag IN ([1], …, [460])

) AS PivotTable
Hans Furmann 40 Reputation points

2024-01-08T09:07:28.3066667+00:00

The function gives back 460 Pivot columns

SELECT [Field1], Field2, [1], …, [460] FROM ( SELECT [Field1], Field2, Field3, DATEDIFF(d,'2024-1-1', Datum) + 1 As Tag FROM [MyTableFunction]('2024-1-1', '2024-1-31', 371) ) As SourceTable PIVOT ( Max( [Field3]) FOR Tag IN ([1], …, [460]) ) AS PivotTable
Erland Sommarskog 122.7K Reputation points MVP Volunteer Moderator

2024-01-08T22:52:21.71+00:00

There is a lot of code in your posts, but it is difficult to follow. Do I understand it correctly that you have two table-valued functions? On which you refer to MyPivotFunction and one that you refer to as MyTableFunction?

It seems that the code for MyTableFunction is missing.

Can you also show us the exact calls that take 6 and 26 seconds respectively?

I can help if you clear the contents of some of your posts to reduce the noise. (One was marked as deleted, but as I am a moderator, I undeleted it.)
Hans Furmann 40 Reputation points

2024-01-09T11:58:05.61+00:00

I think the code of MyTableFunction is not really relevant for my question, because it's used in both cases.
My Question is why needs MyPivotFunction much less time when I run it directly and why runs it 4 times long when I run it via

SELECT * FROM MyPivotFunction

In both cases I get exactly the same result

You can see the code of MyPivotFunction above. Its starting wtih

SELECT [Field1], Field2, [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31], [32], [33], [34], [35], [36], [37], [38], [39], [40], [41], [42], [43], [44], [45], [46], [47], [48], [49], [50], [51], [52], [53], [54], [55], [56], [57], [58], [59], [60], [61
Viorel 122.9K Reputation points

2024-01-09T13:07:51.1833333+00:00

What queries are you comparting and did you try to add option (recompile) after test select statements?
Erland Sommarskog 122.7K Reputation points MVP Volunteer Moderator

2024-01-09T22:43:58.1866667+00:00

I think the code of MyTableFunction is not really relevant for my question, because it's used in both cases.

It's up to you. If you are not interested in sharing the information we ask for, you don't have to. But then you may not get any answers. To be able to answer, I need to see the code that takes six seconds and I need to see the code that takes 24 seconds, and yes, I need to see what it's inside that table function.
Hans Furmann 40 Reputation points

2024-01-10T11:41:59.32+00:00

I cannot understand your comment, I already showed you both codes.
The 6 second code ist the long one with the > 400 flield with numeric fieldnames.
The other one is (again) this:

SELECT * FROM [MyPivotFunction]('2024-01-01', '2024-12-31', 371)

It simply uses the 6 'seconds code' (MyPivotFunction) in this select-statement above.
Greg Low 1,985 Reputation points Microsoft Regional Director

2024-02-10T03:51:16.1133333+00:00

If you want Erland (or anyone) to help, you need to give them code that they can execute that demonstrates the problem. Providing code that uses a function that contains unknown code doesn't allow anyone to test anything. They are left with guessing what might be wrong. The nested code can completely change the query plan. Are you able to demonstrate the issue with code that you can share?

Share via

Large runtime difference for function with pivot calculation

Your answer