3

I am really surprisde to see that in my MS SQL table, I am defining a field varchar and doing group by with another table's field.

When I do the same thing with the column as nvarchar it is 3 second faster than varchar when, theoretically, varchar should be faster because of 1 byte char.

Can anyone explain me why nvarchar is faster in this instance?

Thanks in advance.

James Wiseman
  • 29,946
  • 17
  • 95
  • 158
Shuvra
  • 213
  • 2
  • 15
  • 8
    Could you supply the query plan and statistics? Chances are that changing the type resulted in new statistics being generated and these result in a better plan. – Keith Jun 06 '11 at 10:33
  • 1
    nvarchar is certainly not faster. @Keith's explanation sounds likely. – Mitch Wheat Jun 06 '11 at 10:34
  • 1
    @Shuvra: what if you change the type *back* to 'varchar'? If it's the statistics, you should now have better performance than first. – Hans Kesting Jun 06 '11 at 10:49
  • how can i see query plan and statistics? – Shuvra Jun 06 '11 at 10:59
  • 5
    Is there a join/comparison with another nvarchar column? The conversion is expensive, so converting from varchar to nvarchar may actually improve things. – James Wiseman Jun 06 '11 at 11:00
  • very strange, if i use tinyint instead of nvarchar. tinyint is slower more.. – Shuvra Jun 06 '11 at 11:15
  • hi keith, please see the query statistics: – Shuvra Jun 06 '11 at 11:52
  • Query Profile Statistics Number of INSERT, DELETE and UPDATE statements 12 6 9.0000 Rows affected by INSERT, DELETE, or UPDATE statements 949872 316624 633248.0000 Number of SELECT statements 39 14 26.5000 Rows returned by SELECT statements 119 14 66.5000 Number of transactions 12 6 9.0000 Network Statistics Number of server roundtrips 6 3 4.5000 TDS packets sent from client 15 6 10.5000 TDS packets received from server 73 23 48.0000 Bytes sent from client 43260 14458 28859.0000 Bytes received from server 275650 85058 180354.0000 – Shuvra Jun 06 '11 at 11:58
  • Time Statistics Client processing time 31 0 15.5000 Total execution time 20151 15 10083.0000 Wait time on server replies 20120 15 10067.5000 – Shuvra Jun 06 '11 at 11:58
  • Hi keith, Mitch,james, Can you pleas give me your email. I can email the query plan and statistics.. shuvra – Shuvra Jun 06 '11 at 12:00
  • @Shuvra - Please edit your question to include the `CREATE TABLE` definitions of both tables including any indexes they may have so we can see if @James's explanation works. – Martin Smith Sep 11 '11 at 12:17

1 Answers1

1

Operating systems use Unicode internally. I think that makes nvarchar faster since it does not need any converting.

Eystein Bye
  • 5,016
  • 2
  • 20
  • 18