Gen EDGE

 View Only

  • 1.  Has anyone used SQL Server Native Client

    Posted Oct 05, 2018 08:26 AM

    Hello, I am trying to Generate/Build application and use "SQL Server Native Client".

    In generation properties I select "MS/SQL" but code generated uses ODBC.

    I need to use SQL Server Native Client coz ODBC has some performance issues (cursors).

     

    In Toolset there is an option MS/SQL, also in Build Tool the is an option in Build Profile DBMS\MSSQL.

     

    :execunit
            member=BLB001S
            language='C'
            os=WINDOWS
            execenv=WINDOWS
            dbms=ODBC
            isolation=CS
            profile=SQL
            format=BYPASS
            dbname=BLOB_TEST
            cascade=CASCADE
            dialectdir=LEFTDIR
            applguid={2F19F09B-FE6C-4727-AC21-A774141708C7}
            helpstyle=HTMLHELP.



  • 2.  Re: Has anyone used SQL Server Native Client

    Broadcom Employee
    Posted Oct 07, 2018 06:15 PM

    Hello Gintaras,

    There is no longer any SQL Server native client support in the Gen generated code. This was removed many releases back because Microsoft dropped SQL Server pre-compiler support and OCBC is the only option for MS/SQL.

    When you set the DBMS(TD) to be MS/SQL in the Generation Defaults the Technical Design for MS/SQL will be used but the generated code will still use OCBC.

     

    Can any other community users help with experiences/advice for OCBC cursor related performance problems for SQL Server?

     

    Thanks,

     

    Lynn



  • 3.  Re: Has anyone used SQL Server Native Client

    Posted Oct 08, 2018 09:52 PM

    I've had zero success using the SQL Server Native Client driver for an ODBC data source - it barfs the first time you encounter the need to open multiple cursors (eg a READ inside a READ EACH).  Have never had time to track down if this is avoidable.

     

    I've successfully used "ODBC Driver 11 for SQL Server", "ODBC Driver 13 for SQL Server" and "SQL Server" driver types, however.  No outrageous differences in performance from what I can tell.

     

    Without knowing the specific problem, it's hard to offer any advice - inevitably, whenever I've seen performance problems it's been around the way the query has been structured or ordered, and it's interactions (or lack thereof) with indexes.  My best suggestion is to use your monitoring tools, and also potentially to extract the SQL from the source and run an EXPLAIN on it.

     

    If push comes to shove, you can also modify StubODBN and use SQLSetConnectOption to change the SQL_CURSOR_TYPE.  Depending on your needs/requirements, 
    SQL_CURSOR_KEYSET_DRIVEN, SQL_CURSOR_FORWARD_ONLY, SQL_CURSOR_DYNAMIC, and SQL_CURSOR_STATIC are viable options.