SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 SSIS and Import/Export (2012)
 Unicode processing is faster then Non Unicode?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic   

MarkieMark73
Starting Member

Netherlands
1 Posts

Posted - 01/08/2013 :  02:57:32  Show Profile  Reply with Quote
Hi all,

My first forum experience at 'Forum SQL Server Integration Services'. Here we go!

I will attempt to describe my problem. In the past I used Non Unicode datatype columns as source and as destination (=a kind of staging database). Due to circumstances and support of exotic characters, I have both source and destination columns changed to Unicode (from varchar to nvarchar).
•In the old way (varchar to varchar) running approx. 30 packages after eachother --> results: 38 minutes runtime
•In the new way (nvarchar to nvarchar) running approx. 30 packages after eachother --> results: 32 minutes runtime

Can someone explain why runtime is becoming faster while using datatypes which consume more bytes of data (sizing). I was expecting that performance would decrease slightly. The opposite is proven in my test results.

Has anyone got any ideas? I was thinking that maybe SSIS handles each text source as Unicode anyway during execution and that's why there is improvement because SSIS does not have to convert twice.

Additional info, most common used components in the packages:
•I'm using an OLEDB source component (SQL Server Native Client 10.0)
•Lookup components on a cache connection file (also changed from varchar to nvarchar)
•And an OLEDB Destination (Fast Load)

Thanks! Any help is welcome!
Kind regards,
Mark

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 01/08/2013 :  04:08:02  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
For unicode you may not have any code page translation. Non-unicode often needs to convert from the source to destination code pages - possibly twice, once for the read and oncefor the write.

It's a common problem with utf-8 which often has dire performance meaning that sql server can't be used for large datasets.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page
  Previous Topic Topic   
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000