March 2, 2012 at 4:08 pm
Hi,
I have a very simple one-table report that uses the following-like statement for a datasource, do not think it matters, but just in case...:
SELECT
t1.X1, (all t1 fields here), t2.Y1, (all t2 fields here), ..., tN.Z1, (all tN fields here)
FROM
(SELECT ... FROM table1) AS t1
FULL OUTER JOIN
(SELECT ... FROM table2) AS t2 ON t2.key = t1.key
FULL OUTER JOIN
(SELECT ... FROM table3) AS t3 ON t3.key = COALESCE(t2.key, t1.key)
...
FULL OUTER JOIN
(SELECT ... FROM tableN) AS tN ON tN.key = COALESCE(tN-1.key,..., t2.key, t1.key)
ORDER BY
COALESCE(t1.key, t2.key, ..., tN.key)
where N is around 20. The SQL itself works fine and returns what I need.
There are about 150 columns in the table, no extensive calculations, no grouping, mostly straight forward fields from the SELECT.
My problem/question is that every time I was adding another JOIN to that statement in BIDS, (or in fact when even simply removing the previously commented line w/o adding/deleting any fields) as soon as I try to either switch from Data to Layout or Preview or save - it takes unbelievably long time to do so... The last change took almost 5 HOURS, imagine that
Again, the report runs fine aftewards, in BIDS at least
Any suggestions?
Thank you
March 9, 2012 at 2:34 pm
Make your query a stored procedure.
March 9, 2012 at 3:40 pm
Thanks, but why would it matter? Again, it's when I am introducing changes to my dataset sql and then simply switching to, say, layout tab or preview or just save... and when it actually previews, it runs considerably fast, no problems here. Are you saying that time it takes to populate columns somehow depends on if data comes from a query or SP? Thanks again
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply