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?