Grouping UNION Commands causes lockup

[oticonaus]

Hi there,

I am running Crystal XI R2 against a MS SQL Server.

I have 2 commands which are both UNION queries. If I add both commands to a report and link them with an Inner Join, it runs fine.

If I then group by one field (which is indexed) from one of the comands, then the report suddenly runs incredibly slowly, selecting 2-3 records every 5-10 secs. Without the grouping, the records are selected at a rate of about 500 per second.

Why?

 

[lbass]

I wonder in the first instance whether you are referencing fields from only one command. It sounds like the grouping is the first reference to the fields in the second command. Linking commands is usually a bad idea, since the linking occurs locally.

-LB

 

[oticonaus]

Yes, I have come to a similar conclusion. I made one big command which incorporated all of the necessary tables, and made it as efficient as I could, and that seems to have done the trick.

I still don't really understand why it happens that way, but I am out of the current pickle and on to the next...