none
ODBC call SQLFetch slower when MARS is enabled

    Question

  • We are connecting to a SQL Server 2008 R2 instance using ODBC and the performance of a SELECT query is significantly slower when MARS is enabled on the connection. I tried different driver versions (SQL Server Native Client 10.0, SQL Server Native Client 11.0), but the MARS connection is always significantly slower. I expect the time taken by the query in the MARS connection to be on par with the time taken with MARS off, as I am only running one query at a time without any interleaving.

    About the SELECT query:
    I am yet to look into other queries, but this one query which has multiple joins is slower with MARS on. The query returns 600K+ rows. I have pasted the query further below.

    Simplistic work flow:
    a) Connect to a SQL Server 2008 R2 instance using ODBC
    b) SQLPrepare the query
    c) SQLExecute
    d) SQLFetch multiple times until it returns SQL_NO_DATA

    When MARS is OFF:
    SQLExecute takes around 7 seconds
    When SQL_ATTR_ROW_ARRAY_SIZE is set to 100, SQLFetch is called 6000+ times, and total fetch time (across 6000+ SQLFetch calls) is under 300 milliseconds
    My application typically gets the complete result set (600K+ rows) in 7.5 seconds (i.e. from issuing the query to receiving the last row).

    When MARS is ON:
    SQLExecute takes around 7 seconds
    When SQL_ATTR_ROW_ARRAY_SIZE is set to 100, SQLFetch is called 6000+ times, and total fetch time is 30+ seconds.
    I experimented with different row sizes, but the total fetch time is typically 30+ seconds, as the time spent in each SQLFetch call increases when the row size is set to a higher number.
    My application typically gets the complete result set (600K+ rows) in about 40 seconds (i.e. from issuing the query to receiving the last row).

    I experimented with different cursor types (including SQL Server specific FAST FORWARD-ONLY cursor with and without autofetch), SQL_ATTR_CONCURRENCY, SQL_ATTR_CURSOR_SCROLLABLE and SQL_ATTR_CURSOR_SENSITIVITY. I experimented with different row fetch sizes too. But I have not been able to improve the performance of the query with MARS enabled. 

    I feel that MARS is using a cursor, while default result sets (“firehose cursors”) are being used when MARS is off; although it is claimed in the Microsoft documentation that default results are the default when MARS is enabled.

    The query I am using is below. I haven’t specified the data types of each column used in the query, but I am going to skip that for now. I can definitely provide that if that information is necessary to solving this problem.

    Maybe my query has some characteristic that is causing MARS to not use default result sets? Anything I can try to speed up my query under MARS? We need MARS to be enabled on our application.

    SELECT [G].[iteration] AS [iter],
      [A].[area] AS [area],
      [B].[status] AS [status],
      DATEADD(minute, DATEDIFF(minute, 0, [B].[dt]), 0) AS [mydate],
      MAX([Z].[title]) AS [title1],
      MIN([Z].[title]) AS [title2],
      MAX((CASE
    	WHEN 0 = ISNUMERIC(CAST([G].[estimate] AS VARCHAR)) THEN NULL
    	ELSE CAST(CAST([G].[estimate] AS VARCHAR) as float) END)) AS [calculation]
    FROM [dbo].[Z] [Z]
      INNER JOIN [dbo].[A] [A] ON ([Z].[idA] = [A].[idA])
      INNER JOIN [dbo].[B] [B] ON ([Z].[idB] = [B].[idB])
      INNER JOIN [dbo].[C] [C] ON ([Z].[idC] = [C].[idC])
      INNER JOIN [dbo].[D] [D] ON ([Z].[idFF] = [D].[idFF])
      INNER JOIN [dbo].[E] [E] ON ([Z].[idP] = [E].[idP])
      LEFT JOIN [dbo].[F] [F] ON ([Z].[idB] = [F].[idB])
      LEFT JOIN [dbo].[G] [G] ON ([Z].[idB] = [G].[idB])
      LEFT JOIN [dbo].[H] [H] ON ([B].[idQ] = [H].[idQ])
      INNER JOIN [dbo].[J] [J] ON ([Z].[idB] = [J].[idJ])
    WHERE (([A].[area] >= 'AA') AND ([A].[area] <= 'ZZ'))
    GROUP BY [G].[iteration],
      [A].[area],
      [B].[status],
      DATEADD(minute, DATEDIFF(minute, 0, [B].[dt]), 0)
    


    Monday, November 11, 2013 8:10 AM

All replies