0

In a large stored procedure I am creating a temp table.

I am then selecting from that temp table after inserting into it at various points.

There is no WHERE statement, just a simple select.

  • If I select one column, it takes 0ms to return results.
  • If I select the same column twice, it takes 0ms to return results.
  • If I select the same column three times, it takes over 200ms to return results.

The column is VARCHAR(81)

If I increase the size of the column, it makes no difference.

0 ms

SELECT Product_Description FROM #Products

0 ms

SELECT Product_Description, Product_Description FROM #Products

200 ms

SELECT Product_Description, Product_Description, Product_Description FROM #Products

200 ms

SELECT Product_Description, Product_Description, Product_Description, Product_Description FROM #Products

I'm not sure if there is something fundamental I'm not understanding about SQL server but this can be replicated every time.

The execution plan shows the same for each select (just the select, with cost 0%).

EDIT: Here is what I am seeing.

This is the code selecting from the temp table. The top and bottom lines are printing the system time:

enter image description here

This is the printed output when those 2 columns (same column) are being selected:

enter image description here

Here is the printed output when just one column is being selected:

enter image description here

enter image description here

Execution plan for 2 columns:

enter image description here

Execution plan for 1 column:

enter image description here

Finally, if I select Product_ID (int) 5 times, it executes at 0-1ms. If I select it 6 times it jumps to 190+ ms:

5x

enter image description here

enter image description here

6x

enter image description here

enter image description here

Benjo
  • 179
  • 1
  • 8
  • Why would you need to select it multiple times? What's the purpose? – cdrrr Oct 03 '19 at 10:34
  • I don't need to - I was trying to narrow down the performance issue to a particular column, then found I had it even when selecting the same column multiple times. By reporting it this way it has removed a lot of variables. – Benjo Oct 03 '19 at 11:22
  • I think something else is going on. There is no chance that selecting the same column three times will exponentially decrease performance like this. I suspect there is some blocking or other resource contention happening. – Sean Lange Oct 03 '19 at 12:55
  • How did you measure the time? If it is the client side time, it is most likely due to the network for transferring more packets. – PeterHe Oct 03 '19 at 13:24
  • @PeterHe I print a timestamp both before and after the select statement in the sp. – Benjo Oct 04 '19 at 09:57
  • @SeanLange It is happening both on my local system and the production server, if that supports or changes your thoughts – Benjo Oct 04 '19 at 09:58
  • 2
    Can you post execution plans of both the fast and slow executions? – Sean Lange Oct 04 '19 at 13:00
  • 0 ms and 200 ms are suspiciously round times. Are these the real benchmark numbers? If so, maybe the granularity of your benchmark timer is no good. Are you running each query multiple times to ensure a hot cache? – Schwern Oct 05 '19 at 15:52
  • I was rounding. Maybe 1-4ms, then 201-214ms – Benjo Oct 05 '19 at 17:01
  • @Schwern Yes I've run it a thousand times as I'm completely baffled by this! – Benjo Oct 06 '19 at 18:21
  • @SeanLange I have updated with some more info. It is still doing it with a different column. – Benjo Oct 14 '19 at 07:37
  • @Schwern I have added an example of how I am doing my timing – Benjo Oct 14 '19 at 07:43
  • Show us how to reproduce this. No matter what I try the behavior you describe is not something I can reproduce. – Sean Lange Oct 14 '19 at 13:47
  • @Benjo Thanks. This is really weird. Normally I'd say it's because you're timing both the execution and retrieval, and double the data might mean double the time if there's a lot of data, but you only have one row, and the time is far more than doubled. I suspect this is just an issue with the benchmark timings, especially since you're getting 0. How about https://support.microsoft.com/en-us/help/931279/sql-server-timing-values-may-be-incorrect-when-you-use-utilities-or-te and https://stackoverflow.com/questions/715432/why-is-sql-server-losing-a-millisecond? – Schwern Oct 14 '19 at 21:35
  • @Schwern thanks for the reply. The Microsoft article speaks of SQL Server 2005 (I'm using the most recent version) so it isn't that, as it was rectified in 2005 sp3. The second one speaks of milliseconds going up in 7ms increments, which still doesn't seem related as the disparity between the 2 values is over 200. – Benjo Oct 15 '19 at 14:06
  • I too was thinking it was a problem with the timing, but I have used this method to good effect in lots of other areas to tune performance, and it has always given me accurate readings. Baffling. – Benjo Oct 15 '19 at 14:07

0 Answers0