Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

From SpotFire:
declare @p1 int
set @p1=0
declare @p2 int
set @p2=0
declare @p7 int
set @p7=0
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.

Here is an article from MSDN regarding all connection parameters for JDBC.

Here is another article from someone who had the same exact problem I have, also with no resolution.

share|improve this question
add comment

2 Answers 2

up vote 1 down vote accepted

There are 2 standard ways to affect this

  1. MAXDOP hint per query
  2. max degree of parallelism Option for the whole server

The SQL Server 2008 resource governor and some trace flags will affect it. I doubt Tibco is using the resource governor so it could be a trace flag.

One more option I've seen is SET IMPLICIT TRANSACTIONS ON.

If no trace flags, no implicit transactions, and no MAXDOP hints, then the queries are not the same.

Edit, after question update

You should be able to change some prepare and/or cursor setting. Example: http://www.streamreader.org/serverfault/questions/177391/impact-on-sql-2000-performance-of-jdbc-selectmethodcursor

share|improve this answer
add comment

You could try creating a plan guide for the query specifying the maxdop hint; the linked article shows its use when you have no control over the query being executed, and an example to control a sp_cursorprepexec query.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.