Asked by:
ODBC call SQLFetch slower when MARS is enabled

-
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)
Question
All replies
-
Hi Ed,
Thank you for your question.
I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated.
Thank you for your understanding and support.Best Regards,
Allen LiAllen Li
TechNet Community Support -
-
-
Hello Ed ,
I would suggest you to have a look at :
http://www.sqlteam.com/article/multiple-active-result-sets-mars
http://blogs.msdn.com/b/sqlprogrammability/archive/2006/05/01/marsintroduction1.aspx?Redirected=true
http://www.c-sharpcorner.com/uploadfile/sudhi.pro/multiple-active-result-sets-mars/
I hope that at least the 1st link could help you.
Mark Post as helpful if it provides any help.Otherwise,leave it as it is.