I'm creating my first report.
I do "new project" > "report server project".
Thereafter I create my shared datasource.
Thereafter I try to add a new report.
It ask to enter my querry.
I've built up my querry previously with the querry analyser in SSMS, and there it runs quite fast.
But when i copy-paste the querry in the reporting designer, and i choose to preview it, it takes more then 20 minutes.
however, my querry it quite big, but i've written querries ten times larger in my life.
(it's about 50lines: 10 querries, union-ed together and in each one i have 4 innerjoins.... all tables are even almost empty, so it's not that indexes are missing or whatever)...
is it known that in the report designer querries are much more slower?
one of the ten querry's that are union-ed together, takes 20seconds, (while in SSMS it takes nearly a second).
if i add a union to it, to add another set of data, it seems to hang already....
is it maybe bug?
can anyone try it also to create a report with a querry with 'union' in it?
the querry looks like this:
select c1,c2,c3,c4,c5
from t1
inner join t2 on t1.cX = t2.cX
inner join t3 on t2.cX = t3.cX
inner join t4 on t3.cX = t4.cX
inner join t5 on t4.cX = t5.cX
where t1.cY = 'conditionA'
union
select c1,c2,c3,c4,c5
from t1
inner join t2 on t1.cX = t2.cX
inner join t3 on t2.cX = t3.cX
inner join t4 on t3.cX = t4.cX
inner join t5 on t4.cX = t5.cX
where t1.cY = 'conditionB'|||it is solved, but i don't understand it....
in fact the querry above was extended with some other stuff:
select c1,c2,c3,c4,c5, 1 ext1, '' ext2, '' ext3
from t1
inner join t2 on t1.cX = t2.cX
inner join t3 on t2.cX = t3.cX
inner join t4 on t3.cX = t4.cX
inner join t5 on t4.cX = t5.cX
where t1.cY = 'conditionA'
union
select c1,c2,c3,c4,c5, '' ext1, 1 ext2, '' ext3
from t1
inner join t2 on t1.cX = t2.cX
inner join t3 on t2.cX = t3.cX
inner join t4 on t3.cX = t4.cX
inner join t5 on t4.cX = t5.cX
where t1.cY = 'conditionB'
now, when i replace the empty strings ('') in the select-parts with NULL then it goes much faster !!!!
can someone explain this performance issue?
(it is also very strange that i have this problem in BIDS and not in SSMS)
|||can someone try this out also?
and eventually give an explanation, if any?
