July 22, 2008 at 5:43 am
I have a very simple table which has a nvarchar(4000) field that stores english and foreign language text. I am encountering very slow performance when retrieving records that need to display the nvarchar. (Records are retrieved using PK and just showing the PK is extremely fast, but showing the 'Phrase' field is very slow)
What has me even more puzzled is if I cast the field as a varchar in the select statement, say even a varchar (8000), I get much better performance. Any ideas?
Table Definition:
CREATE TABLE [dbo].[PMD](
[PhraseDetailsID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[Phrase] [nvarchar](4000) NULL,
CONSTRAINT [PMD_PhraseDetailsID] PRIMARY KEY CLUSTERED
(
[PhraseDetailsID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
SQL Server 2005 Enterprise SP2 x64
July 22, 2008 at 6:23 am
I'd be more interesting in seeing the execution plan from the query.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 22, 2008 at 6:50 am
Execution Plans attached:
EP_Nvarchar.sqlplan
select top 10000 PhraseDetailsId, Phrase from PMD-- results takes 14 seconds
EP_Cast.sqlplan
select top 10000 PhraseDetailsId, cast(Phrase as varchar(8000)) from PMD-- result takes 7 seconds
(needed to put 'top' in for demonstration purposes only, in real life there are 650K records, the results come back in 15 & 7 minutes)
July 22, 2008 at 8:37 am
OK. Well, that was useless.
Hmmm... If we were comparing reads of 10000 rows of VARCHAR to NVARCHAR, I wouldn't be surprised by a two-fold increase in speed. I'm a little shocked to see that much of a change just on the networking part of the query. I'd have to see system monitor counters on your network, but I'm guessing that the difference is simply the amount of data being moved is 1/2 for the VARCHAR and that makes the difference for your network. I say that because you're storing an NVARCHAR and the CAST doesn't change that. It only changes what gets returned as part of the result set.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 22, 2008 at 11:41 am
Grant---question for you.
Why would the data be half when using Varchar? Both the queries will return same number of rows so how can data be half for Varchar?
I think....most probably i ll b wrong.......... the "Cast into Varchar" query uses the same execution plan that was generated for the NVarchar query......dats y it is taking less time.......
July 22, 2008 at 11:47 am
I have noticed something like this before also. In some of my functions it is actually faster (significantly so) to convert all of the arguments from Nvarchar to Varchar, do my work, and then convert back to NVarchar to return the results, than it is to do everything in NVarchar, even though that means no conversions are necessary and even though the "work" to "conversion" ratio was very low. Wierd.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 22, 2008 at 11:53 am
CHAR or VARCHAR is one half the size of NCHAR or NVARCHAR.
The N designates the data type as unicode which stores at twice as much data for each character.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 22, 2008 at 11:53 am
But that assumes you're not worrying about extended character sets. Wouldn't that muck with international data?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 22, 2008 at 2:48 pm
Grant, that would appear to be it (network). 😀 When I move this test down to my local box I get identical results - makes sense because there is no network traffic involved.
Do you know of any way to monitor the network traffic specific to sql so I can monitor this activity in a production environment.:
Thanks for the help!
July 23, 2008 at 5:24 am
Just the System Monitor counters is all I've ever had available. I use Operations Manager to collect those now, but you could just use Performance Monitor.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply