0

After changing a column from ntext to varchar(max) a query becomes slightly slower.

I'm doing a

select * from table_with_ntext

vs

select * from table_with_nvarchar_max

enter image description here

I thought that since the lob logical reads decrease so much (213860 vs 6) vs the gain on logical reads (2572 vs 3384). The query will be faster, even when the CPU Time is faster (812 vs 547), the elapse time is slower (2032 vs 3384).

Note: I run the same comparison several time and always the table_with_ntext was faster by a similar margin.

Can someone explain this behavior?

Thanks

enter image description here

execution plan, pasted as requested

Community
  • 1
  • 1
roncansan
  • 2,310
  • 6
  • 27
  • 34
  • Can you paste execution plan for both/share the link for execution plans – TheGameiswar Mar 21 '17 at 15:17
  • 1
    actually second query used much less resources in terms of CPU(elapsed time is sum of cputime + any waits it might have encountered).check this as well for more info:http://dba.stackexchange.com/questions/166421/confusion-about-cpu-time-and-elapsed-time-calculation – TheGameiswar Mar 21 '17 at 15:25
  • You might have some locking in place, high memory, low I/O speed (or a sudden drop in I/O throughput because of higher load on the server at that specific time) or may other reasons. But as @TheGameiswar mentioned, the 2nd query is less resource intensive CPU-wise. – Radu Gheorghiu Mar 21 '17 at 16:02

0 Answers0