Performance Tuning with Nvarchar(max)

• krishnavamshi24

  Mr or Mrs. 500

  Points: 553

  More actions

  September 15, 2017 at 1:11 pm

  #349125

  Hi Folks, 

  I have a performance issue on view that returns 7 columns which are of nvarchar(max) data type.  The view basically has joins among 3 base tables out of these tables, two tables return the 7 nvarchar(max) data. Total columns returned is 20. Rest of columns are of data type nvarchar of varying lengths not exceeding 300 characters.

  Please help me with resolutions. My understanding is having this many nvarchar(max) columns is hindering the performance.

  Thanks Guys.

• TheSQLGuru

  SSC Guru

  Points: 134017

  More actions

  September 15, 2017 at 3:41 pm

  #1959668

  krishnavamshi24 - Friday, September 15, 2017 1:11 PM

  Hi Folks, 

  I have a performance issue on view that returns 7 columns which are of nvarchar(max) data type.  The view basically has joins among 3 base tables out of these tables, two tables return the 7 nvarchar(max) data. Total columns returned is 20. Rest of columns are of data type nvarchar of varying lengths not exceeding 300 characters.

  Please help me with resolutions. My understanding is having this many nvarchar(max) columns is hindering the performance.

  Thanks Guys.

  1) What is the data size of all columns and rows being returned?

  2) Did you use sp_whoisactive to see what work is going on while the query is running?

  3) How long is the query running and how many rows are returned?

  4) Try creating variables of the proper data type for each column and switching your SELECT to put the data into them. That is the way to find out if the query is hurting due to transfer time to the calling machine/software.

  Best,
  Kevin G. Boles
  SQL Server Consultant
  SQL MVP 2007-2012
  TheSQLGuru on googles mail service

• Jeff Moden

  SSC Guru

  Points: 1003865

  More actions

  September 18, 2017 at 9:35 am

  #1959828

  krishnavamshi24 - Friday, September 15, 2017 1:11 PM

  Hi Folks, 

  I have a performance issue on view that returns 7 columns which are of nvarchar(max) data type.  The view basically has joins among 3 base tables out of these tables, two tables return the 7 nvarchar(max) data. Total columns returned is 20. Rest of columns are of data type nvarchar of varying lengths not exceeding 300 characters.

  Please help me with resolutions. My understanding is having this many nvarchar(max) columns is hindering the performance.

  Thanks Guys.

  Maybe, maybe not.  "It Depends" on a whole lot more than the columns just being present.  Kevin asked some questions and I'll also state that not knowing what the DDL, DML, keying, and indexing is, that's about the best answer you're going to get.  Post some more info and we can probably help.  See the 2nd link under "Helpful Links" in my signature line below for how to ask performance problem questions and what we need from you to help.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)