Explain the difference in performance between ODBC in VS versus ODBC in SSMS RRS feed

  • Question

  • I've been tasked with creating an extract routine, pulling data from a Progress database and importing it into SQL Server. I've built the SQL statement, and although not the most complex statement I've ever written, it's not a simple statement, either. However, the performance of the query varies wildly, depending upon where it's run from, and this is where I could use some help.

    I developed the query from within Visual Studio 2010, creating a Data Connection in the Server Explorer. This uses the .NET Framework Data Provider For ODBC. The query usually takes about 30 seconds to complete, and based on what it's doing (connecting to Progress via ODBC), I can live with that.

    My next step was to move to SQL Server Management Studio, where I set up a Linked Server (requiring a 64 bit Progress OpenEdge ODBC Driver). From here, I am not even able to run my entire query at all, it simply times out. So, I've take to breaking apart the query in order to find the bottleneck. I see some slight improvement in performance when I pay attention to the Indexes on the Progress tables. But, It can still take 3-4 minutes to run a sub-section of the original query, which ran in Visual Studio, in it's entirety, in 30 seconds.

    So, thanks for reading thus far. My questions;

    What is the difference between the .NET Framework Data Provider for ODBC (from within VS) and the 64-bit Progress OpenEdge Driver (from within SSMS) that would cause such a disparity in performance?

    Do you see any way for me to leverage the .NET Framework Data Provider for ODBC from within SSMS? I'll add that my preference is to stay within SSMS, because I will then be able to create a package and schedule things within SQL Server. This is opposed to writing something from VS, and creating a Windows Service, or some other infrastructure to execute the extract.

    Thanks for any/all input.

    • Edited by Chuck Miller Tuesday, March 5, 2013 2:53 PM typo
    Monday, March 4, 2013 4:55 PM


All replies

  • Hi Chuck,

    Welcome to the MSDN forum.

    I am trying to involve a senior expert into your thread. Please wait for the response. Sorry for any inconvenience.

    Best Regards,

    Alexander Sun [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, March 6, 2013 2:04 AM
  • SQL Server linked servers currently use OLE DB rather than native ODBC.  Linked servers access ODBC data sources via the OLE DB provider for ODBC drivers. 

    If you goal is to use an SSIS package, you don't need to use a linked server.  Instead, consider creating an ODBC connection in the package for use as an ADO.NET data source.  This will use the same underlying API as Visual Studio.  Alternatively, you can use a Script data source to read from the Postgress database just like VS.

    Dan Guzman, SQL Server MVP,

    Wednesday, March 6, 2013 3:13 AM
  • Thanks for the help.
    Thursday, March 7, 2013 8:34 PM
  • Thanks for the reply.
    • Marked as answer by Chuck Miller Thursday, March 7, 2013 8:34 PM
    Thursday, March 7, 2013 8:34 PM