August 31, 2009 at 12:16 pm
Hi,
I have one stored procedure which is based on some Logic to process millions of records.
When I was using datatype VARCHAR it was taking More than 15 hours. When i changed data type to NVARCHAR I am getting very good performance. My SP runs in less than 40 minutes ;-).
Can anybody tell me why we have this perfromance difference? Is it better to use NVARCHAR if I am storing only english text in it?
Thanks,
Wish
August 31, 2009 at 12:56 pm
The change in datatype shouldn't have made that much of a difference.
It is possible that your prior poor performance was due to "parameter sniffing". Basically, your originally query may have been running an inefficient query plan. When you made changes to it and ran it again, it generated a better execution plan.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 31, 2009 at 1:04 pm
That's almost 180 degrees away from what people usually complain about, how NVARCHAR slows things down because it's bigger than VARCHAR. Bob probably hit the answer with parameter sniffing. What's the data type in your database? I don't think an implicit conversion from NVARCHAR to VARCHAR would act like a function, but it's possible.
"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
August 31, 2009 at 1:05 pm
I have checked execution plan. It is same for NVARCHAR and VARCHAR.
I tried on three different servers. and on all three servers I am getting same perfromance.
I am also surprised by this performance difference.
August 31, 2009 at 1:11 pm
Did you have a copy of the execution plan from back before you ever started making changes?
Parameter sniffing refers to SQL generating an execution plan for a stored procedure based on the first set of parameters fed to the stored procedured. Making any change to the code would trigger a recompilation of the execution plan.
Are you saying that you can at this moment change from nvarchar back to varchar and see the performance degrade, even if you run the stored procedure with the same parameters? If so, I'm really curious. Would you mind posting up some code, some schema, and maybe the execution plans?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 31, 2009 at 1:45 pm
BOB: Are you saying that you can at this moment change from nvarchar back to varchar and see the performance degrade, even if you run the stored procedure with the same parameters?
Yes Bob,
I can see the performance degrade. Sorry but I can not put code, execution plan over here.
August 31, 2009 at 1:57 pm
Why not?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 31, 2009 at 6:54 pm
Without, at least, the execution plan, there's no way to understand what's happening on your end. What you're describing is not normal behavior. I'd love to understand what's causing it.
"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 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply