Informix ODBC nchar --> cast as varchar?

  • 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?

    Henk

Viewing 0 posts

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