I have a query which selects from multiple views and is fairly I/O heavy. If I execute this query using Management Studio it uses parallelism across most of the 16 CPUs and completes in under 10 seconds. However, when executing it from SpotFire (a product from Tibco) it uses only 1 CPU and can take hours to complete.
I am able to replicate the issue in Management Studio if I using the query hint OPTION (MAXDOP 1).
I was thinking that it may be a SpotFire problem, but it uses the Microsoft JDBC driver for connection and I see no connection properties regarding parallelism. I have confirmed using Profiler that the queries from SpotFire and from Management Studio look exactly. the. same. The only difference appears to be parallel execution.
Can anyone offer insight as to why this might occur?
!SOLVED! [kind of]
I reran the SQL Profiler trace and found that the queries are indeed the same, but the way that they are executed is different.
declare @p1 int
declare @p2 int
declare @p7 int
exec sp_cursorprepexec @p1 output,@p2 output,NULL,N'SELECT a, b, c, d FROM MyView',16,8193,@p7 output
select @p1, @p2, @p7
From Management Studio:
SELECT a, b, c, d FROM MyView
I then added two new events to SQL Profiler and reran the queries:
- Performance / Degree of Parallelism
- Performance / Showplan All
This showed the the query from SpotFire was using DOP 0 (BinaryData = 0x00000000) and from Management Studio was DOP 12 (BinaryData = 0x1200000). Next, the SHOWPLAN showed me that the SpotFire query was inserting the results from the view into a temporary table before returning the data to the client.
So why does it do this? Possibly because of the sp_cursorprepexec statement. But why should that cause the DOP to drop to 0? I don't know.
I think that the "solution" is going to be working with SpotFire and possibly tuning the JDBC connection string.