Performance when Inserting into nvarchar vs varchar

Question

Performance when Inserting into nvarchar vs varchar

terry999

SSCertifiable

Points: 5036
More actions
January 26, 2018 at 1:41 pm

#402997

Hi
A stored proc was running v.slow.
The problem was
select fieldlist into #t from myview
Time taken to select from the view doesn't change that much.
I changed the SP to use a normal table so it became
delete wtblCustomer insert into wtblCustomer (fieldlist...) select fieldlist.... from myview order by CustomerID asc
There is a PK clustered (CustomerID as int) on wtblCustomer
This improved v.much.
The wtblCustomer was created with varchar fields. The view columns are nvarchar.
To be consistent with the output generated previously.
I created wtblCustomer with nvarchar fields:
The insert with nvarchar takes >10 times longer!!
It's a small table e.g. < 10000 rows and when fields are varchar is < 5MB
Collation is the same from view and work table.
I recompiled the SP after I switch varchar to nvarchar.
So it would seem it's not inserting into a temp table but using nvarchar.
Any ideas?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

Look up CONVERT_IMPLICIT and see if that is in play here.

I note that in my almost 25 years of consulting on SQL Server MISMATCHED DATA TYPES ARE THE NUMBER ONE PERFORMANCE PROBLEM I SEE IN TOTAL!!!

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

terry999 SSCertifiable Points: 5036 More actions · Answer 2

Thanks
A quick look indicates in the view no implicit type conversions on join criteria or where clause - however there are many in case statements e.g.
case when is numeric(fld1) then cast(fld1 as int) else 0 end as intFld1

I am confused, I can understand if the view was slow to return but it returns quickly in results pane.

What is going on to make creating a temp table so slow Or inserting into a table defined as nvarchar slow, looking at the information_schema.columns the views' char fields are nvarchar.
When it inserts into a table defined as varchar its quick.

Tempdb and the view's db are different collations

terry999 SSCertifiable Points: 5036 More actions · Answer 3

First thank you v.much, without your suggestion of type conversions I wouldn't have found it.

The main view joined to a customer orders view

Create view customerorders AS with freePeriod ( SELECT freePeriod = -1 * funcGetParam_asint('DaysFreePeriod') ) SELECT fldlist, CASE WHEN DATEADD(day, Days.freePeriod, CO.BillingStartDate) < GETDATE() then 1 ELSE 0 END as isActive FROM tblCustomerOrders as CO CROSS JOIN freePeriod as Days

When I hardcoded the value
SELECT fldlist, CASE WHEN DATEADD(day, -20, CO.BillingStartDate) < GETDATE() then 1 ELSE 0 END as isActive FROM tblCustomerOrders as CO

funcGetParam_asint looks in a table for a single row/column (nvarchar) and converts to an int. Its a way the free period can be altered without having to alter the view (non sql person can do it via an App). The same table holds other params which may be strings. I guess I'll have to split the table into an integer param table.

I don't understand why this didn't affect the select performance of parent view but only the insert?

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

You are welcome.

The answer to your question will be found in the query plan.

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