Coalesce breaking SQL statement in SSRS?

I've run into what may an odd bug. I'm using SQL Server 2005, and
Visual Studio 2005. I was provided with a SQL statement (that is, I
did not originally write the SQL) for use in creating a simple
manifest report. Names are changed for security, but it is like so:
SELECT DISTINCT
A.NAME [name],
B.NAME [item_type],
CASE B.NAME,
WHEN N'option1' THEN COALESCE( C.name, N'')
WHEN N'option2' THEN COALESCE( D.name, N'')
WHEN N'option3' THEN COALESCE( E.name, N'')
WHEN N'option4' THEN COALESCE( F.name, N'')
WHEN N'option5' THEN COALESCE( G.name, N'')
...
FROM TABLE_A A
INNER JOIN TABLE_B B with (nolock) ON B.id = A.id
INNER JOIN TABLE_C C with (nolock) ON B.id = C.id
LEFT OUTER JOIN TABLE_D with (nolock) ON B.guid = D.id
LEFT OUTER JOIN TABLE_E with (nolock) ON B.guid = E.id
LEFT OUTER JOIN TABLE_F with (nolock) ON B.guid = F.id
LEFT OUTER JOIN TABLE_G with (nolock) ON B.guid = G.id
...
and onward, for 27 tables. There is no WHERE clause.
The quest runs perfectly fine in the SQL Server Management studio,
returning around 4000 rows in under one second. However, when I try to
run the same statement in an .RDL file, the query takes as long as
10-15 minutes, and locks up the Design Studio while it waits. If I
remove the COALESCE statements (i.e. I replace COALESCE( C.name, N'')
with ( C.name) ), the problem disappears entirely.
I have tried using ISNULL as well, and the problem remains. I have
tried creating an entirely new solution, new .RDL, new .RDS, with no
luck. sp_who2 indicates the the process is sleeping, no CPU usage or
DiskIO.
Has anyone seen this before, or have an idea?On Oct 1, 10:05 am, avat...@.gmail.com wrote:
> I've run into what may an odd bug. I'm using SQL Server 2005, and
> Visual Studio 2005. I was provided with a SQL statement (that is, I
> did not originally write the SQL) for use in creating a simple
> manifest report. Names are changed for security, but it is like so:
> SELECT DISTINCT
> A.NAME [name],
> B.NAME [item_type],
> CASE B.NAME,
> WHEN N'option1' THEN COALESCE( C.name, N'')
> WHEN N'option2' THEN COALESCE( D.name, N'')
> WHEN N'option3' THEN COALESCE( E.name, N'')
> WHEN N'option4' THEN COALESCE( F.name, N'')
> WHEN N'option5' THEN COALESCE( G.name, N'')
> ...
> FROM TABLE_A A
> INNER JOIN TABLE_B B with (nolock) ON B.id = A.id
> INNER JOIN TABLE_C C with (nolock) ON B.id = C.id
> LEFT OUTER JOIN TABLE_D with (nolock) ON B.guid = D.id
> LEFT OUTER JOIN TABLE_E with (nolock) ON B.guid = E.id
> LEFT OUTER JOIN TABLE_F with (nolock) ON B.guid = F.id
> LEFT OUTER JOIN TABLE_G with (nolock) ON B.guid = G.id
> ...
> and onward, for 27 tables. There is no WHERE clause.
> The quest runs perfectly fine in the SQL Server Management studio,
> returning around 4000 rows in under one second. However, when I try to
> run the same statement in an .RDL file, the query takes as long as
> 10-15 minutes, and locks up the Design Studio while it waits. If I
> remove the COALESCE statements (i.e. I replace COALESCE( C.name, N'')
> with ( C.name) ), the problem disappears entirely.
> I have tried using ISNULL as well, and the problem remains. I have
> tried creating an entirely new solution, new .RDL, new .RDS, with no
> luck. sp_who2 indicates the the process is sleeping, no CPU usage or
> DiskIO.
> Has anyone seen this before, or have an idea?
A couple of suggestions. Firstly, if you aren't already, you will want
to create a stored procedure out of the query above and access it in
the report. This should improve your performance a little. Secondly,
you might want to run the SQL Profiler while you are running the
report to see what kind of processing is going on and if there is an
exception/error hidden somewhere, etc. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant