Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Informix ODBC nchar --> cast as varchar? Expand / Collapse
Posted Thursday, November 8, 2012 8:24 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, November 20, 2016 12:51 AM
Points: 47, Visits: 629
I need to copy more than a 100 large tables on a daily basis from Informix to SQL Server.
It seems like that on the Informix side most fields are nchar. With a few exceptions these are implicitly casted as varchar on the SQL Server side (that's how the data is stored). We use linked server/openquery.

The copying takes forever even though we managed to improve the performance by a factor 20 or so.

Now what I was considering was to Cast the nchar columns to varchar in the openquery statement, so make Informix doing the explicit conversion and resulting in a higher effective throughput (based on the thought varchar consumes less space than nchar).

The linked in Server is making use of a ODBC connection.

I thought the syntax has to be something like:
...SELECT * FROM OPENQUERY(xxx, 'Select ...CAST([invoice_no] AS VARCHAR(50)) invoice_no... from xxx.invoice')

where invoice_no is nchar(50)

Problem is that I cannot access the system at the moment so I cannot test this out myself.

Does it make any sense what I'm doing? Would this actually result in a better performance?
Is the syntax correct?


Post #1382528
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse